Friday, February 22, 2008

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.

5 comments:

ddf said...

All you've done is create a new materialized view based on the same source table. This isn't renaming anything.

jason said...

hey, here is the site i was talking about where i made the extra cash, I was making about $900 extra a month...
check it out ..

jason said...

hey, here is the site i was talking about where i made the extra cash, I was making about $900 extra a month...
check it out ..

Sachin said...

DDF,

Agreed - this just rebuilt but in a tactical way. But this workaround can help in several other issues.
For example:
There is a non partition table which has a non-partitioned mat. view based on it.
with growth of table, in case you want to convert this table to partition table and mview to partitiond mview (without changing the name) and ofcourse
with least down time, this trick can be very handly.

For non part table to part table the procedure is easy. YOu can create a new partitioned table with diff name and then drop the old table and rename new to old table name.

But for mview, this cant be done in the same way as for table because it will involve long down time.
Using this trick discussed, that becomes easy.
1. create a new partitioned table as select * from old_mview.
2. drop the old mview.
3. rename the new part table to old mview name.
4. create the mview with old name using prebuilt option.

Let me know if that helps. Or you have any other way to achieve the same.

Frederick Tang said...

Sachin,

Thanks for your post. Your idea has certainly help my case a lot.

rgds,
fred