Rem
Rem $Header: ctx_src_2/src/dr/admin/s1102000.sql /main/2 2009/06/03 11:28:22 surman Exp $
Rem
Rem s1102000.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      s1102000.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    surman      06/01/09 - 8323978: Ignore ORA-4080 error
Rem    surman      05/29/09 - 8323978: Drop map triggers on upgrade
Rem    rpalakod    06/07/08 - 11.2
Rem    rpalakod    06/07/08 - Created
Rem

REM ==================================================================
REM 8323978: Drop map triggers
REM ==================================================================
set serveroutput on

declare
  cursor all_indexes is
    select username, idx_name, idx_id,
           ixp_name, ixp_id
      from dba_users u, ctxsys.dr$index i, ctxsys.dr$index_partition p
      where i.idx_id = p.ixp_idx_id (+)
        and u.user_id = i.idx_owner#;

  sql_string varchar2(400);
begin
  for rec in all_indexes loop
    if (ctxsys.drixmd.IndexHasPTable(rec.idx_id)) then
      if rec.ixp_name is null then
        dbms_output.put_line('dropping trigger for index ' || rec.username ||
        '.' || rec.idx_name);
      else
        dbms_output.put_line('dropping trigger for index ' || rec.username ||
        '.' || rec.idx_name || ', partition ' || rec.ixp_name);
      end if;

      sql_string := 'drop trigger ' ||
        ctxsys.driutl.make_pfx(rec.username, rec.idx_name, 'T', rec.ixp_id) ||
        'M"';
      begin
        execute immediate sql_string;
      exception
        when others then
          if sqlcode != -4080 then
            -- ignore "trigger does not exist" error
            raise;
          end if;
      end;
    end if;
  end loop;
end;
/