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 II)
(Solution II)
 
Line 28: Line 28:
 
ORA-00942: table or view does not exist
 
ORA-00942: table or view does not exist
 
</pre>
 
</pre>
In that case, run the query below to see every single step that was carried out:
+
In that case, run the script below on the failed block:
 
<pre>
 
<pre>
SQL> select * from DBA_RECOVERABLE_SCRIPT_BLOCKS order by block_num;
+
SQL> select script_id, block_num, status from dba_recoverable_script_blocks where script_id = '9CDF721F1C221F81E05400144FFB4175' and block_num = 7;
</pre>
+
 
This view provides the results of every step as well as the UNDO statement to UNDO the operation.
+
SCRIPT_ID                        BLOCK_NUM STATUS
UNDO the operation from where the error started, fix the issue and retry.
+
-------------------------------- ---------- ------------
<pre>
+
9CDF721F1C221F81E05400144FFB4175          7 ERROR
SQL> select undo_block from DBA_RECOVERABLE_SCRIPT_BLOCKS where block_num = 7;
 
  
UNDO_BLOCK
+
--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  /
  
DECLARE
+
PL/SQL procedure successfully completed.
  stmt      VARCHAR2(4000);
 
  sqlcursor  NUMBER;
 
  nrows      NUMBER;
 
  sname      VARCHAR2(30);
 
  oname      VARCHAR2(30);
 
. . .
 
 
</pre>
 
</pre>
Now run the UNDO BLOCK as a seperate SQL, fix the error and retry the operation.
+
After that you can fix the error in the script and rerun the script. (It will pick up where it left off)

Latest revision as of 10:32, 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 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)