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