ORA-23622: Operation SYS.DBMS STREAMS ADM.MAINTAIN CHANGE TABLE is in progress.

From Robs_Wiki
Revision as of 10:32, 24 January 2020 by Qadmin wiki (talk | contribs) (Solution II)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Analysis

This error most likely happens when you use Oracle Streams and when an error occurred, for example when you set up a streams configuration and have made an error. After that, the transaction failed, but is still open.

Solution I

Find out the script id of the transaction:

SQL> select script_id from DBA_RECOVERABLE_SCRIPT ;

SCRIPT_ID
--------------------------------
9CDF721F1C221F81E05400144FFB4175

Rollback the transaction:

SQL> exec DBMS_STREAMS_ADM.RECOVER_OPERATION('9CDF721F1C221F81E05400144FFB4175','ROLLBACK');

Solution II

Sometimes the previous approach is not working and you receive another error:

SQL> exec DBMS_STREAMS_ADM.RECOVER_OPERATION('9CDF721F1C221F81E05400144FFB4175','ROLLBACK');
BEGIN DBMS_STREAMS_ADM.RECOVER_OPERATION('9CDF721F1C221F81E05400144FFB4175','ROLLBACK'); END;

*
ERROR at line 1:
ORA-23616: Failure in executing block 7 for script 9CDF721F1C221F81E05400144FFB4175 with
ORA-00942: table or view does not exist

In that case, run the script below on the failed block:

SQL> select script_id, block_num, status from dba_recoverable_script_blocks where script_id = '9CDF721F1C221F81E05400144FFB4175' and block_num = 7;

SCRIPT_ID                         BLOCK_NUM STATUS
-------------------------------- ---------- ------------
9CDF721F1C221F81E05400144FFB4175          7 ERROR

--remark the above script_id is not always the same as the previous one, hence the query
SQL> begin
  2  dbms_streams_adm.recover_operation( 
  3  script_id => '9CDF721F1C221F81E05400144FFB4175',
  4  operation_mode => 'PURGE');
  5  END;
  6  /

PL/SQL procedure successfully completed.

After that you can fix the error in the script and rerun the script. (It will pick up where it left off)