Difference between revisions of "ORA-23622: Operation SYS.DBMS STREAMS ADM.MAINTAIN CHANGE TABLE is in progress."

From Robs_Wiki
Jump to: navigation, search
(Solution)
(Solution II)
Line 34: Line 34:
 
This view provides the results of every step as well as the UNDO statement to UNDO the operation.
 
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.
 
UNDO the operation from where the error started, fix the issue and retry.
 +
<pre>
 +
SQL> select undo_block from DBA_RECOVERABLE_SCRIPT_BLOCKS where block_num = 7;
 +
 +
UNDO_BLOCK
 +
--------------------------------------------------------------------------------
 +
 +
DECLARE
 +
  stmt      VARCHAR2(4000);
 +
  sqlcursor  NUMBER;
 +
  nrows      NUMBER;
 +
  sname      VARCHAR2(30);
 +
  oname      VARCHAR2(30);
 +
. . .
 +
</pre>
 +
Now run the UNDO BLOCK as a seperate SQL, fix the error and retry the operation.

Revision as of 10:15, 24 January 2020

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.

SQL> select undo_block from DBA_RECOVERABLE_SCRIPT_BLOCKS where block_num = 7;

UNDO_BLOCK
--------------------------------------------------------------------------------

DECLARE
  stmt       VARCHAR2(4000);
  sqlcursor  NUMBER;
  nrows      NUMBER;
  sname      VARCHAR2(30);
  oname      VARCHAR2(30);
. . .

Now run the UNDO BLOCK as a seperate SQL, fix the error and retry the operation.