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.
