2017-01-28

ORA-01031 on CREATE/ALTER USER under sysdba account

If you have the following error with user operations (CREATE USER, ALTER USER etc)

SQL> connect / as sysdba
Connected.
SQL> create user c##common identified by c##common;
create user c##common identified by c##common
                                    *
ERROR at line 1:
ORA-01031: insufficient privileges

check if Database Vault is enabled.

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

After you enable Oracle Database Vault, you no longer can use the
administrative accounts (such as SYS and SYSTEM) to create or enable
user accounts.
Disabling database Vault is version specific. For example in 12c if you forgot password for accounts with DV_ACCTMGR role the only way is to recreate database.

2017-01-16

Powershell: Split file to parts by placeholders

I have file all.sql with following structure

--------------start of file1.sql--------------
...
...content of file1.sql...
...
--------------end of file1.sql--------------


--------------start of file2.sql--------------
...
...content of file2.sql...
...
--------------end of file2.sql--------------

and I need to split file all.sql to separate files file1.sql, file2.sql, etc
You can use following Powershell script to do it

$workingDir="c:\[path_to_dir_with_files]\"
$allFileName="all.sql"

$pattern=[regex]'(?sm)--------------start of (.*?)--------------(.*?)--------------end of (.*?)--------------'

$file = Get-content $workingDir$allFileName -Raw 
foreach($match in $pattern.Matches($file)) {
  $outputFileName = $workingDir+$match.Groups[1].value
  Write-Output $outputFileName
  Set-Content -Path $outputFileName -Value $match.Groups[2].value
}

change pattern expression to hit your placeholders.

Get objects with dblinks

Script to get objects with dblinks in source code. It checks views, materialized views and objects with source code (I don’t check through dba_dependencies because it returns objects that use dblinks via synonyms).
It can be easily extended for dba_jobs for example.

Version with all_ views

WITH db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM all_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM all_views
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM all_mviews
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM all_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;
Version with dba_ views

WITH owner_list(owner) AS (
  SELECT USER FROM dual
 ),
 db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM dba_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM dba_views
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM dba_mviews
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM dba_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND s.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;

2017-01-12

Notepad++ lines not starting with word regexp

To remove lines not starting with word you can use following regular expression (put your word instead of GRANT

^(?!GRANT).*$

ORA-24005 and ORA-24002 on table DROP

If table drop ends with message

ORA-24005: Inappropriate utilities used to perform DDL on AQ table [queue_table_name]

first try drop through package DBMS_AQADM with force=true

BEGIN
  SYS.DBMS_AQADM.drop_QUEUE_TABLE(QUEUE_TABLE =>'[queue_table_name]', FORCE=> TRUE);
END;
/ 

But what to do if it returns

ORA-24002: QUEUE_TABLE [queue_table_name] does not exist
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 2

Your data dictionary is already little bit corrupted. You can try following

alter session set events '10851 trace name context forever, level 2';
drop table [queue_table_name];