Friday, 11 August 2017

ORA-12091 Cannot online redefine table with materialized views error Solution

Hi DBA-mates,
We are back once more with some issue and solutions step by step with some explanations.
Recently when we attempt to re-organize a table ONLINE that table contains materialized views. We used the DBMS_REDEFINITION.START_REDEF_TABLE procedure to do this, and we got an ORA-12091 "cannot online redefine table "ora-data"."SKU" with materialized views error.
ora-data = user name
SKU = table name
‘Column mapping i.e. source table and reorg table’ 

For example:

SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');
   ERROR at line 1:
   ORA-12091: cannot online redefine table "ora-data"."SKU" with materialized views
   ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
   ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
   ORA-06512: at line 1

If you are also facing the same issue in your environments, you can check and do workaround which we have followed in our real time environments.

First we need to remove the snapshot log and materialized view which was created during failed execution of the command BMS_REDEFINITION.START_REDEF_TABLE procedure, which is mentioned above error.

Now, Check for log details as shown below commands:
SQL> select log_table from user_snapshot_logs;

Now, we will drop the Materialized view log from below querry.
SQL> drop snapshot log on SKU;  
   Materialized view log dropped.

Now, we are checking for Interim Table
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME                     STATUS
   ------------------------------ -------
   INT_SKU                        INVALID

SQL> select mview_name, compile_state from user_mviews;
   MVIEW_NAME                     COMPILE_STATE
   ------------------------------ -------------------
   INT_SKU                         ERROR

Here, we need to drop this also, because some data may stored from failed above command.

So, please drop the interim table also as shown below:

SQL> drop materialized view int_sku;
   Materialized view dropped.

Now, we are trying again:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');

PL/SQL procedure successfully completed.

Hope this may useful and helpful. Please let us know for any concerns or suggestions either in comment box or contact us
Some more useful links:

ora-data Team.

1 comment:

  1. Link exchange is nothing else however it is just placing the other person's webpage link on your page at proper place and other person will also do same in support of you.


Thank you for your comments and suggestions. It's good to talk you.