понедельник, 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 to dba_jobs by example.

WITH vw AS
 (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM dba_views
  where owner LIKE '%'
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link
    FROM dba_db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, owner, mview_name, master_link
    FROM dba_mviews
    WHERE master_link IS NOT NULL and owner LIKE '%'
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link
    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 '%'
    UNION
    SELECT 'PROCOBJ' obj_type, j.owner, program_name AS obj_name, db_link
    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 '%'
    UNION
    SELECT 'PROCOBJ' obj_type, j.owner, job_name AS obj_name, db_link
    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 '%'
    ORDER BY 2, 1, 3)
SELECT *
FROM all_obj;
-- To get DDL for objects with links uncomment this and comment line above
--SELECT obj_type,
--       obj_name,
--       owner,
--       dbms_metadata.get_ddl(obj_type, obj_name, owner) txt
--FROM all_obj;

четверг, 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

среда, 14 сентября 2016 г.

ORA-12154: space is really matter

Got an stupid error ORA-12154: TNS:could not resolve the connect identifier specifed.
Double check tnsnames.ora, sqlnet.ora, TNS_ADMIN – all were correct
This one in tnsnames doesn’t work:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
)
)

and this one works:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
 )
)

Difference only in one space in penultimate line.

It’s interesting, that tnsping shows another error

TNS-12533: TNS:illegal ADDRESS parameters

but sqlplus

ORA-12154: TNS:could not resolve the connect identifier specifed

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

Sql*plus login.sql file

SQLPATH/login.sql

The User Profile is executed after the Site Profile and is intended to allow users to specifically customize their session. The User Profile script is generally named login.sql. SQL*Plus searches for the User Profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
Content of my file


set echo off
set termout off
set feedback off
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
define _editor="C:\Program Files\IDM Computer Solutions\UltraEdit-32\Uedit32.exe"
define sqlplprompt=idle
column sqlplprompt noprint new_value sqlplprompt
-- user@connect_identifier
--select LOWER(user) || '@' || '&_CONNECT_IDENTIFIER' sqlplprompt from dual;
-- user@sqlprompt
--select LOWER(user) || '@' || global_name sqlplprompt from global_name;
-- connect identifier only
define sqlplprompt=&_CONNECT_IDENTIFIER
set sqlprompt '&sqlplprompt.>'
ALTER SESSION SET statistics_level = ALL;
set termout on
set feedback on

--set timing ondefine

Also you can use glogin.sql file, but usually we don’t have access to it

The Site Profile script is generally named glogin.sql. SQL*Plus or the iSQL*Plus Server executes this script whenever a user starts a SQL*Plus or iSQL*Plus session and successfully establishes the Oracle Database connection. The Site Profile enables the DBA to set up SQL*Plus environment defaults for all users of a particular SQL*Plus or iSQL*Plus Application Server installation. Users cannot directly access the Site Profile.The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and %ORACLE_HOME%\sqlplus\admin\glogin.sql in Windows.

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

dba_hist_sqlstat statistics

Template for PL/SQL developer based on link

Counts execution statistics from dba_hist_sqlstat for given sql_id

select to_char(min(s.end_interval_time),'DD.MM.YYYY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),3) sec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
&<name="start_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.end_interval_time >= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
&<name="end_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.begin_interval_time <= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value;

вторник, 30 августа 2016 г.

Fast access to dba_hist_active_sess_history

dba_hist_active_sess_history view code

create or replace view sys.dba_hist_active_sess_history
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
as
select /* ASH/AWR meta attributes */
       ash.snap_id, ash.dbid, ash.instance_number,
       ash.sample_id, ash.sample_time,
       /* Session/User attributes */
       ash.session_id, ash.session_serial#,
       decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
       ash.flags,
       ash.user_id,
       /* SQL attributes */
       ash.sql_id,
       decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
       ash.sql_child_number, ash.sql_opcode,
       (select command_name from DBA_HIST_SQLCOMMAND_NAME
        where command_type = ash.sql_opcode
        and dbid = ash.dbid) as sql_opname,
       ash.force_matching_signature,
       decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
       decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
              ash.top_level_sql_opcode),
       /* SQL Plan/Execution attributes */
       ash.sql_plan_hash_value,
       decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
       (select operation_name from DBA_HIST_PLAN_OPERATION_NAME
        where  operation_id = ash.sql_plan_operation#
          and  dbid = ash.dbid) as sql_plan_operation,
       (select option_name from DBA_HIST_PLAN_OPTION_NAME
        where  option_id = ash.sql_plan_options#
          and  dbid = ash.dbid) as sql_plan_options,
       decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
       ash.sql_exec_start,
       /* PL/SQL attributes */
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_object_id),
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_subprogram_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_object_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_subprogram_id),
       /* PQ attributes */
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
       decode(ash.px_flags,      0, to_number(NULL), ash.px_flags),
       /* Wait event attributes */
       decode(ash.wait_time, 0, evt.event_name, NULL),
       decode(ash.wait_time, 0, evt.event_id,   NULL),
       ash.seq#,
       evt.parameter1, ash.p1,
       evt.parameter2, ash.p2,
       evt.parameter3, ash.p3,
       decode(ash.wait_time, 0, evt.wait_class,    NULL),
       decode(ash.wait_time, 0, evt.wait_class_id, NULL),
       ash.wait_time,
       decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
       ash.time_waited,
       (case when ash.blocking_session = 4294967295
               then 'UNKNOWN'
             when ash.blocking_session = 4294967294
               then 'GLOBAL'
             when ash.blocking_session = 4294967293
               then 'UNKNOWN'
             when ash.blocking_session = 4294967292
               then 'NO HOLDER'
             when ash.blocking_session = 4294967291
               then 'NOT IN WAIT'
             else 'VALID'
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session_serial#
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_inst_id
          end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then NULL
             else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
                         0, 'N', 'Y')
          end),
       /* Session's working context */
       ash.current_obj#, ash.current_file#, ash.current_block#,
       ash.current_row#, ash.top_level_call#,
       (select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
        where top_level_call# = ash.top_level_call#
        and dbid = ash.dbid) as top_level_call_name,
       decode(ash.consumer_group_id, 0, to_number(NULL),
              ash.consumer_group_id),
       ash.xid,
       decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
       ash.time_model,
       decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
                                                         as in_connection_mgmt,
       decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
       decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
       decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
       decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
                                                         as in_plsql_execution,
       decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
       decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
                                                       as in_plsql_compilation,
       decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
                                                       as in_java_execution,
       decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
       decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
       decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
       decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
                                                       as capture_overhead,
       decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
                                                           as replay_overhead,
       decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
       decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
       /* Application attributes */
       ash.service_hash, ash.program,
       substrb(ash.module,1,(select ksumodlen from x$modact_length)) module,
       substrb(ash.action,1,(select ksuactlen from x$modact_length)) action,
       ash.client_id,
       ash.machine, ash.port, ash.ecid,
       /* DB Replay info */
       ash.dbreplay_file_id, ash.dbreplay_call_counter,
       /* stash columns */
       ash.tm_delta_time,
       ash.tm_delta_cpu_time,
       ash.tm_delta_db_time,
       ash.delta_time,
       ash.delta_read_io_requests,
       ash.delta_write_io_requests,
       ash.delta_read_io_bytes,
       ash.delta_write_io_bytes,
       ash.delta_interconnect_io_bytes,
       ash.pga_allocated,
       ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id(+)
      and  ash.dbid             = sn.dbid(+)
      and  ash.instance_number  = sn.instance_number(+)
      and  ash.dbid             = evt.dbid
      and  ash.event_id         = evt.event_id;
comment on table SYS.DBA_HIST_ACTIVE_SESS_HISTORY is 'Active Session Historical Statistics Information';

WRH$_ACTIVE_SESSION_HISTORY partitioned by DBID and SNAP_ID columns, so the fastest way to query this view – using snap_id ranges.
You can determine right snap_id from dba_hist_snapshot view.

This query uses partition pruning and run fast

select * from dba_hist_active_sess_history WHERE snap_id >= 12345

and this is not (it usees FTS on WRH$_ACTIVE_SESSION_HISTORY ash)

select * from dba_hist_active_sess_history WHERE sample_time >= SYSTIMESTAMP -1 

Be careful with subqueries. The following queries can also use FTS:

select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT :1 FROM dual);
select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT min(snap_id) FROM Dba_Hist_Snapshot WHERE begin_interval_time >= SYSTIMESTAMP - 1)

So, the safest way is determine snap_id in separate query.