-
materialized view log
I have a MV and the materialized view log for fast refresh on one of them. I am writing a installation script for customers. I do not want them to get error on
DROP MATERIALIZED VIEW LOG ON xyz;
ORA-12000: a materialized view log already exists on table
if it does not exists initially.. so I was thinking of adding a check to see if it exists before doing the drop, but I cannot find which system table keeps the refrence to it ?
DROP MATERIALIZED VIEW LOG ON xyz;
CREATE MATERIALIZED VIEW LOG ON xyz WITH ROWID, SEQUENCE
(a,b,c)
INCLUDING NEW VALUES;
I have wrote a small pl/sql block to do this for view itself
DROP MATERIALIZED VIEW mv_xyz;
CREATE MATERIALIZED VIEW mv_xyz
....
Declare n int;
begin
select COUNT(*) into n from all_objects where OBJECT_NAME=upper('xyz') AND OBJECT_TYPE = 'MATERIALIZED VIEW' AND OWNER= UPPER('xxx');
If n=0 then
execute immediate 'DROP MATERIALIZED VIEW LOG ON xyz';
End if;
End;
/
How do I do something similar for mv logs ?
Thanks
-
-
Here's another approach, that i think might be more robust because it just traps the exception that you want to avoid getting raised to the user.
Code:
declare
no_mv_log exception;
pragma exception_init(no_mv_log,-12002);
begin
execute immediate 'drop materialized view log on my_table';
exception
when no_mv_log then null;
end;
Alternatively, you could code as ...
Code:
declare
mv_log_exists exception;
pragma exception_init(mv_log_exists,-12000);
begin
execute immediate 'create materialized view log on my_table';
exception
when mv_log_exists then null;
end;
... or even ...
Code:
declare
mv_log_exists exception;
pragma exception_init(mv_log_exists,-12000);
begin
execute immediate 'create materialized view log on my_table';
exception
when mv_log_exists then
execute immediate 'drop materialized view log on my_table';
execute immediate 'create materialized view log on my_table';
end;
-
Well lookee here ... http://asktom.oracle.com/pls/ask/f?p...15151793634161
Tom Kyte likes the exception trapping method as well.
I like his "if ( sqlcode = 12000 ) then" syntax also, but I'd comment it, like ...
[code]
begin
execute immediate 'drop materialized view log on xyz';
exception
when others
if ( sqlcode = 12000 ) then null; -- suppress "Table has no MV log" error
else raise;
end if;
end;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|