List all privileges for user in oracle

-- You can filter results in last lines of query
WITH username(username) AS (
-- fill user name here
    SELECT UPPER('&USERNAME') FROM dual
  ),
  all_user_roles AS (
     SELECT (SELECT username FROM username) || sys_connect_by_path(granted_role, '->') PATH, granted_role, admin_option
     FROM dba_role_privs p
     START WITH grantee IN (SELECT username FROM username)
     CONNECT BY PRIOR granted_role = grantee
  ),
  grantee AS (
    SELECT granted_role NAME, PATH FROM all_user_roles
    UNION
    SELECT username, NULL AS PATH FROM username
  ),
  priv_list AS (
    SELECT 'ROLE' priv_type, granted_role priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option grantable, PATH
    FROM all_user_roles
    UNION
    SELECT 'SYSTEM' priv_type, privilege priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option, PATH
    FROM dba_sys_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'TABLE' priv_type, PRIVILEGE, owner, table_name, NULL AS column_name, grantable, PATH
    FROM dba_tab_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'COLUMN' priv_type, PRIVILEGE, owner, table_name, column_name, grantable, PATH
    FROM dba_col_privs, grantee
    WHERE grantee = grantee.name)
SELECT * 
FROM priv_list
-- optional filter
--WHERE table_name = 'MY_TABLE_NAME'
--AND priv = 'DELETE';  

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;

UPD: Starting from Oracle 19c this scripts are not relevant: we can use native syntax:

SELECT listagg(distinct object_type, ', ') txt FROM all_objects;

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

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

OPT_ESTIMATE hint

https://www.pythian.com/blog/oracles-opt_estimate-hint-usage-guide/
Guide how to use hint OPT_ESTIMATE hint, which is used in sql profiles

Sql profiles and hints

How do SQL profiles co-exist with user hints?
SQL profiles, when enabled, remove any user hints from the SQL statement. They can also
inadvertently change optimizer configuration; for example, they will
bring OPTIMIZER_FEATURES_ENABLE to the newest possible value, unless
an older value is shown to be better through test-execution of the SQL
statement.
http://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf

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, 'POLICY 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(+)
  ),
  tab_modifications AS (
    SELECT 'DBA_TAB_MODIFICATIONS' info_from, NULL AS owner, NULL AS NAME, 'Was modified on: ' || TO_CHAR(TIMESTAMP, 'DD.MM.YYYY HH24:MI:SS'), 
        'Inserts: ' || inserts || '; Updates: ' || updates || '; Deletes: ' || deletes || '; Truncated ' || truncated   note 
    FROM dba_tab_modifications d, looking_for 
    WHERE d.table_owner = l_owner 
      AND d.table_name = l_name
    )
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 privs
UNION ALL
SELECT * FROM policy
UNION ALL
SELECT * FROM sql_plan
UNION ALL
SELECT * FROM tab_modifications
;

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.

Powershell: Split file to parts by placeholders

I have file all.sql with following structure

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


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

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

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

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

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

change pattern expression to hit your placeholders.

Get objects with dblinks

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

Version with all_ views

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

Version with dba_ views

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

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

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;

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

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

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.

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

Задача: переместить ; одинокостоящую в последней строке файла в последнюю строчку с кодом.
Решение в notepad++

\n;\z --> ;

Галочку установить в Regular expression

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