DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: materialized view log

  1. #1
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    use DBA_MVIEW_LOGS

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width