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.
Here,
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');
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',...........);
*
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;
LOG_TABLE
-------------------------
MLOG$_SKU
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 @ora-data.blogspot.com.
Some more useful links:
Oracle DBA Troubleshooting Tips
How to Create Custom Schema in r12.2
Oracle DataGuard Concept and Setup
RAC Concepts and Details in Oracle
How to Create Custom Schema in r12.2
Oracle DataGuard Concept and Setup
RAC Concepts and Details in Oracle
Regards,
ora-data Team.
Hey! I'm at work browsing your blog from my new apple iphone!
ReplyDeleteJust wanted to say I love reading through your blog and look forward to all your posts!
Keep up the outstanding work!
Dear User,
DeleteThank you for your valuable feedback.
Regards,
ora-data Team