How to rename Materialized view? ORA-32318

Now - this is what you call a long break .. almost a year. Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl. We are still searching a name for her. That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.

I hope to be more punctual in writing my experiences here on the blog.
Today, I have something on materialized views. How to rename them?

Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to ORA-32318.

ORA-32318: cannot rename a materialized view
Cause: Renaming a materialized view or its base table is not supported.
Action: Do not rename the base table of a materialized view.

Now, this is what I dont expect Oracle to do. when rename of table is possible, then why not rename of mat view possible? If any one knows a logical reason, please share.

Anyways, I gave it a further thought on how to accomplish this and an idea struck me.

I'm giving the information of our system but with changed names.
DB1 - where base table exists - 10.2.0.2
DB2 - where mview exists - 10.2.0.2
T1 - name of the table on DB1
M1 - name of the mview - Fast/Incremental refresh on demand

Now as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).

Here is some test setup scripts:

On DB1:

SQL> create table t1 as select * from all_objects;

Table created.

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

On DB2:


SQL> create materialized view m1 refresh fast on demand as select * from t1@db1; - db1 is database link

Materialized view created.

SQL> select count(*) from m1;

COUNT(*)
----------
62551

Now steps to change the name:

Step1: On DB1
- Stop DML/DDL activities on the base table T1

Step2: On DB2
- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.
- Create another table with new name. This new name should be the name of mview intended.

SQL> create table m2 as select * from m1;

Table created.

>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview

Step3: On DB2

Create new materialized view using "prebuilt" option.

SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from t1@db1;

Materialized view created.

Step4: On DB2

Drop the old materialized view.

SQL> drop materialized view m1;

Materialized view dropped.

Thats it!

Now to check if thats working.

Delete few rows on t1@db1

SQL> delete from t1 where object_id in (2,3);

2 rows deleted.

SQL> commit;

Commit complete.

>> And now refresh the new mview.

SQL> exec dbms_mview.refresh('M2')

PL/SQL procedure successfully completed.

>> Check the no of rows:
SQL> select count(*) from m2;

COUNT(*)
----------
62549

Finally check the way oracle refreshed this mview.


SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';

LAST_REF
--------
FAST


Well that's just a confirmation :)

Note: I havent tested other functionalities of this test. So please do a thorough testing of this code before pushing into production database system.
BTW - we are doing the same.