Demant TABLE_REORG Procedure Failed ORA- on SALES_DATA MDP_MATRIX PROMOTION_DATA How do I Restart? RUPD$_ MLOG$_
By JeffG-Oracle on May 05, 2015
When you submit TABLE_REORG, DBMS_REDEFINITION is used. This will create an MLOG$_ database object. The table you supplied for the TABLE_REORG procedure has a Primary Key (PK). Since there is a PK on the table, when DBMS_DEFINITION creates the MLOG$_ object, DBMS_REDEFINITION automatically creates a RUPD$_ object. These objects are meant to be used for Java RepAPI. If you execute a 'drop snapshot log on tablename' the snapshot log as well as the temporary snapshot log are dropped. However, dropping the MLOG$_ object is not best practice.
If these objects exist prior to executing TABLE_REORG, you will see the following: ‘Table cannot be redefinitioned.' in log_table_reorg table. This means that one or both objects/segments exist. For example, if I had a table_reorg for mdp_matrix fail, the following segments would most likely be left behind:
Use the following SQL to verify (10g and above):
where regexp_like( object_name, 'MLOG$|RUPD$')
and owner = '&Schema_owner'
While you can drop these temporary RDBMS segments, it is best practice to use the following:
uname => '&demantra_schema_name',
orig_table => '&original_table_name',
int_table => '&interim_table_name');
In the above, supply the arguments:
- DM or your schema name
- MDP_MATRIX is the original_table_name
- MLOG$_MDP_MATRIX is the interim_table_name
Verify that the objects are dropped. Submit the TABLE_REORG again AFTER repairing the cause of the last failure.