четверг, 16 февраля 2017 г.

Long to clob

Use function sys.dbms_metadata_util.long2clob
For example

SELECT sys.dbms_metadata_util.long2clob(v.textlength,
                                        'SYS.VIEW$',
                                        'TEXT',
                                        v.rowid) 
FROM sys.view$ v;

LISTAGG - remove duplicates

My colleague Victor help me to find solution for task:
Aggregate string from query result without duplicates.

Almost all solutions, that I find in internet was like

SELECT LISTAGG(str, ',') WITHIN GROUP (ORDER BY 1)
FROM (SELECT DISTINCT str FROM tab);

But if you have scalar subquery with filter condition this solution doesn’t work because of 2-levels of nesting.
Below there are 2 solutions with regexps and xslt -transformations

SELECT (
   SELECT regexp_replace(LISTAGG(object_type, ',') WITHIN GROUP (ORDER BY object_type), '([^,]+)(,\1)+', '\1') 
   FROM user_objects
   ) solution1,
   (
   SELECT rtrim(xmltype('<r><n>' || LISTAGG(object_type, ',</n><n>') WITHIN GROUP (ORDER BY object_type) || ',</n></r>').extract('//n[not(preceding::n = .)]/text()').getstringval(), ',')
   FROM user_objects
   ) solution2
FROM dual;

четверг, 9 февраля 2017 г.

Sql profile content

Extract sql profiles info and hints:

SELECT CREATED, PROFILE_NAME, SQL_TEXT, 
  XMLtransform(XMLTYPE(h.comp_data), '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="*">
<xsl:for-each select="/outline_data/hint">
<xsl:value-of select="."/>
<xsl:text>&#xa;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>').getStringVal()
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p
WHERE p.name = h.profile_name;

This query puts all hints in one field. If multiply lines is good for you you can choose simplier implementation

SELECT CREATED, PROFILE_NAME, SQL_TEXT, extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name;

Also found solution on JL site, but this query valid for 10g only

select
       sp.sp_name, sa.attr#, sa.attr_val
from
       sqlprof$      sp,
       sqlprof$attr  sa
where
       sp.signature = sa.signature
and     sp.category  = sp.category
order by
       sp.sp_name,
       sa.attr#
;

The further investigations give me the link to Christian’s Antognini site where he recommends the following query for 11g:

SELECT so.name, extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name = 'opt_estimate'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

Test shows me, that this query is incorrect. Correct query is

SELECT so.name,extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name IN (SELECT name FROM DBA_SQL_PROFILES)
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

But it should be rewritten to show sql-query and creation time

среда, 8 февраля 2017 г.

Why put sys.aud$ to sysaux?

It’s not a secret, that if you leave sys.aud$ in system tablespace, you can catch high buffer busy waits because of freelist management of system tablespace.
But which tablespace choose for moving? SYSAUX or user tablespace.
One more point for SYSAUX from Data Pump:

When transporting a database over the network using full transportable
export, auditing cannot be enabled for tables stored in an
administrative tablespace (such as SYSTEM and SYSAUX) if the audit
trail information itself is stored in a user-defined tablespace

вторник, 7 февраля 2017 г.

среда, 1 февраля 2017 г.

Looking for object usage

Looking for object usage
WITH looking_for(l_owner, l_name) AS (
  SELECT UPPER('&owner'), UPPER('&object_name') FROM dual
  ),
  dep AS (
    SELECT 'DBA_DEPENDENCIES' info_from, d.owner, d.name, d.type, CAST(d.dependency_type AS VARCHAR2(4000)) note 
    FROM dba_dependencies d, looking_for 
    WHERE d.referenced_name = l_name AND d.owner = l_owner
    ),
  dba_source_with_owner AS (
    SELECT 'DBA_SOURCE with owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_owner || '.' || l_name , 'i')
      AND NOT (d.name = l_name AND d.owner <> l_owner)
    ),
  dba_source_wo_owner AS (
    SELECT 'DBA_SOURCE without owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
      AND d.owner = l_owner
      AND d.name <> l_name
    ),
  jobs AS (
    SELECT 'DBMS_JOB', d.priv_user, to_char(d.job), NULL, d.what
    FROM dba_jobs d, looking_for 
    WHERE regexp_like(what, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules AS (
    SELECT 'DBA_SCHEDULER_JOBS.JOB_ACTION', d.owner, d.job_name, job_type, d.job_action
    FROM dba_scheduler_jobs d, looking_for 
    WHERE regexp_like(job_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules_programs AS (
    SELECT 'DBA_SCHEDULER_PROGRAMS.PROGRAM_ACTION', d.owner, d.program_name, program_type, program_action
    FROM dba_scheduler_programs d, looking_for 
    WHERE regexp_like(program_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  privs AS (
    SELECT 'DBA_TAB_PRIVS' info_from, NULL, grantee, 'ROLE', PRIVILEGE note 
    FROM dba_tab_privs d, looking_for 
    WHERE d.table_name = l_name AND d.owner = l_owner
    ),
  policy AS (
    SELECT 'DBA_POLICIES' info_from, NULL, d.policy_name, 'POLICI FOR ' || d.object_owner || '.' || d.object_name, pf_owner || '.' || d.package || '.' || d.function note 
    FROM dba_policies d, looking_for 
    WHERE d.pf_owner = l_owner 
      AND (d.package = l_name OR d.function = l_name)
    ),
  sql_plan AS (
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM looking_for, dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX_HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX__SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
  )
SELECT /*+ PARALLEL(4)*/* FROM dep
UNION ALL
SELECT * FROM dba_source_with_owner
UNION ALL
SELECT * FROM dba_source_wo_owner t WHERE NOT EXISTS (SELECT NULL FROM dba_source_with_owner i WHERE i.owner = t.owner AND i.name = t.name)
UNION ALL
SELECT * FROM jobs
UNION ALL
SELECT * FROM schedules
UNION ALL
SELECT * FROM schedules_programs
UNION ALL
SELECT * FROM schedules_programs
UNION ALL
SELECT * FROM privs
UNION ALL
SELECT * FROM policy
UNION ALL
SELECT * FROM policy
UNION ALL
SELECT * FROM sql_plan
;

суббота, 28 января 2017 г.

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.

понедельник, 16 января 2017 г.

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.

      WITH db_links as (
          SELECT owner, 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 LIKE 'VERTEX%'
        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 LIKE 'VERTEX%'
        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
          FROM dba_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
          FROM dba_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
          FROM dba_source s, dba_db_links d 
          WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
            and s.owner LIKE 'VERTEX%'
          UNION
          SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner
          FROM dba_scheduler_programs j, dba_db_links d
          WHERE program_type = 'PLSQL_BLOCK'
            AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
            and j.owner LIKE 'VERTEX%'
          UNION
          SELECT'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner
          FROM dba_scheduler_jobs j, dba_db_links d
          WHERE job_type = 'PLSQL_BLOCK'
            AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
            and j.owner LIKE 'VERTEX%'
          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 db_link_owner = owner OR db_link_owner = 'PUBLIC'
        )
      WHERE rn = 1
      ORDER BY owner, db_link, obj_name

четверг, 12 января 2017 г.

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];

четверг, 29 декабря 2016 г.

Script generator for moving objects to another tablaspace

Limitation and aspects
1. Subpartitioning doesn’t support
2. Currently 3 source-target tablespaces pares
3. IOT and lobs, partitioned IOT and lobs are supported
4. Parallelism support
5. Output script can be divided to separate files per owner

DECLARE 
  -- Minimum size of objects for parallel processing
  C_MIN_PARALLEL_SIZE_MB CONSTANT NUMBER := 100;  
  -- Parallel degree for moving and rebuilding
  C_PARALLEL_DEGREE CONSTANT INT := 8;

  TYPE tpt_varchar2 IS TABLE OF VARCHAR2(30);
  -- currently 3 tablespaces supported
  -- 1st old -> 1st new, 2nd old -> 2nd new etc
  l_old_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA1','GRIM_INDX1', '');
  l_new_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA2','GRIM_INDX2', '');


  FUNCTION get_old_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_old_ts(1) ||''','''|| l_old_ts(2)||''','''||l_old_ts(3) || '''';
  END;

  FUNCTION get_new_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_new_ts(1) ||''','''|| l_new_ts(2)||''','''||l_new_ts(3) || '''';
  END;

  FUNCTION get_corresponding_ts(l_old_ts_name VARCHAR2) RETURN VARCHAR2 IS
    i PLS_INTEGER;
  BEGIN
    i := l_old_ts.FIRST;
    WHILE UPPER(l_old_ts(i)) <> UPPER(l_old_ts_name) LOOP
      i := l_old_ts.NEXT(i);
    END LOOP;
    RETURN l_new_ts(i);
  EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, 'Can''t find corresponding ts for ' || l_old_ts_name);
  END get_corresponding_ts;

  FUNCTION get_parallel_clause(l_size_mb NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF l_size_mb > C_MIN_PARALLEL_SIZE_MB THEN
      RETURN ' PARALLEL ' || C_PARALLEL_DEGREE || ' ';
    END IF;
    RETURN NULL;
  END get_parallel_clause;

  PROCEDURE echo_rebuild_indexes(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line;
    dbms_output.put_line('
PROMPT Rebuild unusable indexes
BEGIN
  FOR rec IN (SELECT * FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' REBUILD PARTITION '' || rec.partition_name || '' PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;

  FOR rec IN (SELECT * FROM dba_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' REBUILD PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;
END;
/');
  END echo_rebuild_indexes;

  PROCEDURE echo_header(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.put_line('SPOOL ' || l_owner || '.log'); 
    dbms_output.put_line('PROMPT Unusable indexes
SELECT owner, index_name, NULL AS part_name FROM all_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
UNION ALL
SELECT index_owner, index_name, partition_name AS part_name FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
;
PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;');     
  END echo_header;

  PROCEDURE echo_footer(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;

PROMPT Check moving
SELECT owner, table_name AS obj_name, NULL AS part_name
FROM all_tables
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name AS obj_name, NULL AS part_name
FROM all_indexes
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name || ''.'' || column_name AS obj_name, NULL AS part_name 
FROM all_lobs
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name, partition_name 
FROM all_tab_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT index_owner, index_name, partition_name
FROM all_ind_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, partition_name
FROM all_lob_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name, ''DEF ATTRIBUTE''
FROM all_part_tables
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name, ''DEF ATTRIBUTE''
FROM all_part_indexes
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, ''DEF ATTRIBUTE''
FROM all_part_lobs
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT USER, segment_name, partition_name
FROM user_segments 
WHERE tablespace_name IN (' || get_old_ts_list || ');

SPOOL OFF');
  dbms_output.put_line(LPAD('-', 40, '-'));  
  END echo_footer;
BEGIN
  FOR rec IN (
      -- not by dba_segments because of delayed segment creation
      SELECT owner 
      FROM dba_tables
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_indexes
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_lobs
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_tab_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT index_owner 
      FROM dba_ind_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_lob_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_tables
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_indexes
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_part_lobs
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
  ) LOOP
    echo_header(rec.owner);
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned tables');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_tables t
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned indexes');
    FOR obj_rec IN (
        SELECT t.* 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned IOT');
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.def_tablespace_name 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned LOB');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_lobs t
        WHERE t.table_owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME || '" '
                         ||'MODIFY DEFAULT ATTRIBUTES LOB (' || obj_rec.column_name || ') (TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||');');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned tables'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tables t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)
                           || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move table partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tab_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) 
        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move IOT table partitions'); 
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.partition_name, t.tablespace_name, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)|| ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move indexes'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
          AND t.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME ||'" REBUILD '
      || ' TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
      || get_parallel_clause(obj_rec.size_mb) || ';');
      -- return parallel degree back
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME||'"' ||' PARALLEL '||obj_rec.DEGREE||';'); 
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move index partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.INDEX_OWNER||'"."'||obj_rec.INDEX_NAME||'"'
                        ||' REBUILD PARTITION "'||obj_rec.PARTITION_NAME||'" TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
                          || get_parallel_clause(obj_rec.size_mb) || ';');
      -- noparallel not needed for index partitions
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned lobs'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lobs t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME||'"'
                          ||' MOVE LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move lob partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lob_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.lob_partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.TABLE_OWNER||'"."'||obj_rec.TABLE_NAME||'" MOVE PARTITION "'||obj_rec.PARTITION_NAME||'"'
                        ||' LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) ||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    echo_rebuild_indexes(rec.owner);
    echo_footer(rec.owner);
  END LOOP;
END;

пятница, 23 декабря 2016 г.

regexp for oracle schema and object name

If you want to find or replace Oracle schema/object name you can use regular expression:

(^|[^A-Za-z0-9#$_])schema_name([^A-Za-z0-9#$_]|$)
(^|[^A-Za-z0-9#$_])schema_name.table_name([^A-Za-z0-9#$_]|$)
(^|[^A-Za-z0-9#$_])schema_name.table_name([^A-Za-z0-9#$_]|$) -> \1new_name\2

вторник, 20 декабря 2016 г.

Copying java classes to another database

1 Export java classes using dbms_metadata.
It doesn’t matter are you set SQLTERMINATOR=TRUE or FALSE:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

results will be incorrect in any cases and you should to transform received files using your favorite text editor or other tool.

To export java classes you should execute dbms_metadata.get_ddl with parameters (please notice the underscore in JAVA_CLASS)

SELECT dbms_metadata.get_ddl('JAVA_CLASS',dbms_java.longname(object_name), USER) 
FROM user_objects o 
WHERE object_type = 'JAVA CLASS'

2 Transform sql files from previous step. I have the following problems:
- lack of slashes. If you use SQLTERMINATOR=true on previous step, you get only one / at the end of the script, but unfortunately totally unusable because of leading spaces. So, I use SQLTERMINATOR=false and add slashes by regexp in Notepad++

[ ]+END;(\n|\z) --> END;\n/\n
With Regular expression radio button
  • long lines: sql*plus I got an error SP2-0027: Input is too long (> 2499 characters) in lines with DBMS_JAVA.IMPORT_RAW_CHUNK(HEXTORAW(…
    I split these lines with regexp
('[^']{2400})(.*')  --> \1'\n|| '\2
With Regular expression radio button

Classes will be created in connected user schema. If this schema is different, that in source database, you should to change these strings

STATUS := DBMS_JAVA.IMPORT_TEXT_CHUNK('(("*" "__NEW_OWNER_HERE__")("*" "PUBLIC"))', 34);

and change the second parameter 34 to length in chars of the first parameter

length('(("*" "__NEW_OWNER_HERE__")("*" "PUBLIC"))')

else you’ll get a lot of errors like this

SQL> alter java class "MDSYS"."/4e9308f3_BMPWriter" compile;

Warning: Java altered with compilation errors.

SQL> show errors java class "MDSYS"."/4e9308f3_BMPWriter"
Errors for JAVA CLASS "MDSYS"."/4e9308f3_BMPWriter":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-29521: referenced name com/sun/media/jai/codec/BMPEncodeParam could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageEncodeParam could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageEncoder could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageCodec could not be found

It’s interesting, that show errors for java classes works in Sql*Plus only.

3 After running the scripts you should to recompile or re-resolve imported classes. I did it with script:

-- resolve classes to new schema
SET SERVEROUTPUT ON SIZE 1000000
DECLARE 
  l_invalid_cnt NUMBER := 0;
  l_prev_invalid_cnt NUMBER := 0;
  FUNCTION get_invalid_cnt RETURN NUMBER IS 
    RESULT NUMBER;
  BEGIN
    SELECT COUNT(*) INTO RESULT FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID';
    RETURN RESULT;
  END;
BEGIN
  l_invalid_cnt := get_invalid_cnt();
  WHILE l_invalid_cnt > 0 AND l_invalid_cnt <> l_prev_invalid_cnt LOOP
    FOR rec IN (SELECT dbms_java.longname(object_name) NAME FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID') LOOP
      BEGIN
        EXECUTE IMMEDIATE 'alter java class "' || rec.name || '" resolver ((* ' || USER || ')(* PUBLIC)) resolve';
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    END LOOP;
    l_prev_invalid_cnt := l_invalid_cnt;
    l_invalid_cnt := get_invalid_cnt();    
  END LOOP;
  dbms_output.put_line('Finish Invalid count = ' ||   l_invalid_cnt); 
END;  
/

PROMPT ================================
PROMPT Invalid classes
PROMPT ================================
SELECT dbms_java.longname(object_name) NAME FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID';

I never use WHEN others THEN NULL; but in this case I’ve got strange errors without error code in PL/SQL Developer. So I have to wrap executions.
I think you can use more simple

alter java class ... compile; -- or
alter java class ... resolve;

without specifying resolver ((* ' || USER || ')(* PUBLIC))
But code above shows, how you can solve ORA-29521 errors. You can resolve reference to any schema you want (by default it’ll be resolved to current schema + public).
To check resolving schema name you can use the following query

select * from user_java_resolvers;

SQLcl output formats

From here

SQLFORMAT options.

  • default
  • ansiconsole
  • csv
  • insert – lists resuts as an insert statement
  • loader – pipe-delimited
  • delimited – same as csv
  • xml
  • html
  • fixed – fixed width
  • text
  • json

пятница, 16 декабря 2016 г.

Mat view logs cleanout

Quote from documentation

Oracle automatically tracks which rows in a materialized view log have
been used during the refreshes of materialized views, and purges these
rows from the log so that the log does not grow endlessly. Because
multiple simple materialized views can use the same materialized view
log, rows already used to refresh one materialized view might still be
needed to refresh another materialized view. Oracle does not delete
rows from the log until all materialized views have used them.

But what if no mat views references to mat view log? Does oracle fills log table? The answer is YES. Check it

DROP TABLE test_mv PURGE;
CREATE TABLE test_mv(a NUMBER PRIMARY KEY, b NUMBER);

CREATE MATERIALIZED VIEW LOG ON test_mv;

INSERT INTO test_mv VALUES (1,1);
INSERT INTO test_mv VALUES (2,2);

SELECT COUNT(*) FROM mlog$_TEST_MV;

  COUNT(*)
----------
         2

Helpful query to check matview logs subscribers and time of last update was found here

SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime, mowner, MASTER 
FROM sys.slog$ s, dba_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);

Let’s check, that Oracle realy delete data from matview logs

DROP MATERIALIZED VIEW mv1;
DROP MATERIALIZED VIEW mv2;
DROP TABLE test_mv;

CREATE TABLE test_mv(a NUMBER PRIMARY KEY, b NUMBER);

CREATE MATERIALIZED VIEW LOG ON test_mv
WITH PRIMARY KEY
EXCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW mv1 
refresh
with primary key
FAST on DEMAND AS
SELECT *
FROM test_mv
/

CREATE MATERIALIZED VIEW mv2
refresh
with primary key
FAST on DEMAND AS
SELECT *
FROM test_mv
/

INSERT INTO test_mv VALUES (1,1);
INSERT INTO test_mv VALUES (2,2);
COMMIT;

PROMPT Before refresh
--SELECT * FROM SYS.SLOG$;
--SELECT * FROM user_registered_snapshots;
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

BEGIN 
  dbms_mview.refresh('MV1');
END;
/  

PROMPT After refresh of 1 of 2 MV
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

BEGIN 
  dbms_mview.refresh('MV2');
END;
/  

PROMPT After refresh of all MV
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

Before refresh
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
  COUNT(*)
----------
         2
PL/SQL procedure successfully completed
After refresh of 1 of 2 MV
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV

  COUNT(*)
----------
         2
PL/SQL procedure successfully completed
After refresh of all MV
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV

  COUNT(*)
----------
         0

So oracle detects refreshing of all matviews and delete data from log

If we create matview through dblink oracle also keeps track refreshing and clears matview log table.

четверг, 8 декабря 2016 г.

Notepad++: замена одинокотосящей ;

Задача: переместить ; одинокостоящую в последней строке файла в последнюю строчку с кодом.
Решение в notepad++
\n;\z --> ;
Галочку установить в Regular expression

суббота, 19 ноября 2016 г.

How to view something from alert.log

Sumup from Oracle-l thread
linux way

tail -f alert.log | grep pattern

Adrci

adrci exec="set home ${adrci_home} ; show alert -p \\\"(message_text like '%ORA-%' and originating_timestamp>=systimestamp-(1/48) \\\"" -term 

more explanation here
Sql way

set lin 1000
col data form a20
col process_id form a12
col module_id form a40
col message_text form a200

select to_char(originating_timestamp,'dd/mm/rr hh24:mi:ss') dt_hour
  , process_id
  , module_id
  , message_text
from
  sys.x$dbgalertext
where
  originating_timestamp >trunc(sysdate)
order by originating_timestamp ;

More info from Tanel Poder

вторник, 18 октября 2016 г.

Опасные GLOBAL CONTEXT

Постигла такая же ошибка, что описана тут
Значение глобального контекста доступны/недоступны в зависимости от установок client identifier при коннекте.
Случаи выходят такие
DBMS_SESSION.SET_IDENTIFIER НЕ УСТАНОВЛЕН
- SET_CONTEXT.CLIENT_ID не установлен – работает
- SET_CONTEXT.CLIENT_ID установлен – работает
DBMS_SESSION.SET_IDENTIFIER УСТАНОВЛЕН

- SET_CONTEXT.CLIENT_ID не установлен – NULL НЕ РАБОТАЕТ
- SET_CONTEXT.CLIENT_ID установлен, но другой != DBMS_SESSION.SET_IDENTIFIER– NULL НЕ РАБОТАЕТ
- SET_CONTEXT.CLIENT_ID установлен = DBMS_SESSION.SET_IDENTIFIER - РАБОТАЕТ
Как видно работать будет в 2 случаях:
1. DBMS_SESSION.SET_IDENTIFIER не установлен, т.е. попробовать очищать его в logon триггере.
2. копировать глобальный контекст для каждого DBMS_SESSION.SET_IDENTIFIER, т.е. после подключения выполнять SET_CONTEXT с CLIENT_ID = DBMS_SESSION.SET_IDENTIFIER – получается не глобальный контекст, а локальный.
Т.е. в любом случае для глобальных контекстов потребуются дополнительные дейстивия: или ОЧИСТКА DBMS_SESSION.CLEAR_IDENTIFIER или КОПИРОВАНИЕ КОНТЕКСТА
Тесты, взятые из вышеуказанной статьи
CLEAR SCREEN 

DROP CONTEXT TEST_CONTEXT;

CREATE OR REPLACE PACKAGE my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_client_id VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_client_id VARCHAR2 DEFAULT NULL)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT(p_namespace, p_attribute, p_value, client_id => p_client_id);
END set_context;
END;
/
CREATE CONTEXT TEST_CONTEXT USING my_ctx_pkg ACCESSED GLOBALLY;

PROMPT НЕ устанавливаем client identifier при коннекте
exec DBMS_SESSION.CLEAR_IDENTIFIER();

PROMPT В контексте НЕ устанавливаем -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => NULL);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте УСТАНАВЛИВАЕМ -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'set_in_context');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;


PROMPT УСТАНАВЛИВАЕМ client identifier при коннекте
BEGIN
  dbms_session.set_identifier(client_id => 'session connect_id');
END;
/

PROMPT В контексте НЕ устанавливаем -- значение НЕ ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => NULL);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте устанавливаем ДРУГОЕ -- значение НЕ ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'set_in_context');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте устанавливаем ТОЖЕ -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'session connect_id');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

пятница, 30 сентября 2016 г.

Create database link in another schema

Here you can find couple of ways to create database link in another schema: using dbms_ijob and dbms_sys_sql

DECLARE
  l_link_owner VARCHAR2(30) := 'TEST_USER';
  l_query VARCHAR2(32767) := q'[
begin
    execute immediate 'CREATE DATABASE LINK test_link4
                          connect to    "test_user"
                          identified by "test_user"
                          using         ''localhost:1521/orcl''';
END;  
  ]';
BEGIN
  sys.dbms_isched.create_job(job_name            => 'CREATE_DBLINK',
                             job_style           => 'REGULAR',
                             program_type        => 'PLSQL_BLOCK',
                             program_action      => l_query,
                             number_of_arguments => 0,
                             schedule_type       => 'NOW', -- ONCE, NOW, EVENT
                             schedule_expr       => NULL,
                             queue_spec          => NULL,
                             start_date          => NULL,
                             end_date            => NULL,
                             job_class           => 'DEFAULT_JOB_CLASS', -- SELECT * FROM dba_scheduler_job_classes
                             comments            => NULL,
                             enabled             => TRUE,
                             auto_drop           => TRUE,
                             invoker             => l_link_owner,
                             sys_privs           => 1, -- sys.dbms_scheduler.check_sys_privs()
                             aq_job              => FALSE);
  COMMIT;
  dbms_isched.run_job('CREATE_DBLINK',
                      asynchronous   => TRUE,
                      invoker        => l_link_owner,
                      sys_privs      => 1/*sys.dbms_scheduler.check_sys_privs()*/);
END;
/

Foreign keys and nulls

Be careful with null enabled foreign key, especially multicolumn.
Test case below:

CREATE TABLE par(id1 NUMBER NOT NULL, id2 NUMBER NOT NULL, CONSTRAINT par_pk PRIMARY KEY(id1, id2));
Table created
INSERT INTO par VALUES(1,1);
1 row inserted
CREATE TABLE chi(id1 NUMBER, id2 NUMBER, CONSTRAINT par_fk FOREIGN KEY (id1, id2) REFERENCES par(id1, id2));
Table created
INSERT INTO chi VALUES(NULL, NULL);-- double nulls are allowed
1 row inserted
INSERT INTO chi VALUES(1, NULL);
1 row inserted
INSERT INTO chi VALUES(2, NULL);--!!!we have no 2 in par table!!!
1 row inserted
INSERT INTO chi VALUES(2, 1);
INSERT INTO chi VALUES(2, 1)
ORA-02291: integrity constraint (DOCKER.PAR_FK) violated - parent key not found

If one of the fields is null, the second field doesn’t need to present in parent table.

четверг, 29 сентября 2016 г.

dblink, которого нет

SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
--------------------------
ORCL
SELECT COUNT(*) cnt FROM dba_db_links l WHERE l.db_link = 'ORCL';
       CNT
----------
         0
SELECT * FROM dual@ORCL;
DUMMY
-----
X

понедельник, 19 сентября 2016 г.

Liquibase test: sorting in includeAll

Let we have the following folder structure

folder_01
  subfolder_03
    file_1.sql
    file_2.sql
  subfolder_04
    file_1.sql
    file_2.sql
folder_02
  subfolder_01
    file_1.sql
    file_2.sql
  subfolder_02
    file_1.sql
    file_2.sql
file_01.sql
file_02.sql
zfile.sql

and I have changelog with includeAll:

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
     <includeAll path="/multifolder/"/>
</databaseChangeLog>

the order of inclusion will be following

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: multifolder.xml
-- Ran at: 19.09.16 15:40
-- Against: null@offline:oracle
-- Liquibase version: 3.5.1
-- *********************************************************************

SET DEFINE OFF;

-- Changeset multifolder/file_01.sql::1::includeall
select 'file01' f from dual;

-- Changeset multifolder/file_02.sql::1::includeall
select 'file02' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/1.sql::1::includeall
select 'folder_01subfolder_03_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/2.sql::1::includeall
select 'folder_01subfolder_03_file2' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/1.sql::1::includeall
select 'folder_01subfolder_04_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/2.sql::1::includeall
select 'folder_01subfolder_04_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/1.sql::1::includeall
select 'folder_02subfolder_01_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/2.sql::1::includeall
select 'folder_02subfolder_01_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/1.sql::1::includeall
select 'folder_02subfolder_02_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/2.sql::1::includeall
select 'folder_02subfolder_02_file2' f from dual;

-- Changeset multifolder/zfile.sql::1::includeall
select 'zfile.sql' f from dual;

So files and folders are sorted together level by level.

One moment you should to keep in mind. If I write

     <includeAll path="multifolder/"/>

instead of

     <includeAll path="/multifolder/"/>

I’ve got Unexpected error running Liquibase: Unknown Reason