materialized view log

[Sonali]

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

[fraze]

use DBA_MVIEW_LOGS

[slimdave]

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.

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 ...

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 ...

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;

[slimdave]

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;
/