Renaming table when MV log is associated (ORA-26563)

Recently, I faced problem in renaming a table. The table had a materialized view log associated with it and I wanted to move the older version of the table and place a new version of the same instead.

But I was getting following error while renaming the old table:

ORA-26563: renaming this table is not allowed

One of the reasons of getting this error is, the table is used in a materialized view and a materialized view log is associated with the table to track the data changes.

Following are the steps need to be taken while renaming such tables:

1. Begin table organization to rename old table to bkup table
EXEC DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION(USER,’CI_PRE_LINE_CONTACT_HISTORY’);

2. Rename old table to bkup table

RENAME CI_PRE_LINE_CONTACT_HISTORY TO CI_PRE_LINE_CONTACT_HIST_OLD;

3. Rename new table to original table name

RENAME CI_PRE_LINE_CONTACT_HIST TO CI_PRE_LINE_CONTACT_HISTORY;

4. End table organization after renaming new table to original table name

EXEC DBMS_MVIEW.END_TABLE_REORGANIZATION (USER,’CI_PRE_LINE_CONTACT_HISTORY’);

Advertisements

About this entry