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

From Robs_Wiki
Revision as of 10:06, 24 January 2020 by Qadmin wiki (talk | contribs) (Solution)
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 query below to see every single step that was carried out:

SQL> select * from DBA_RECOVERABLE_SCRIPT_BLOCKS order by block_num;

This view provides the results of every step as well as the UNDO statement to UNDO the operation. UNDO the operation from where the error started, fix the issue and retry.