Rem
Rem $Header: ctx_src_2/src/dr/admin/d1110000.sql /main/9 2009/06/03 11:28:22 surman Exp $
Rem
Rem d1110000.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      d1110000.sql - downgrade from 11.2 to 11.1.0.7
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-4081 error
Rem    surman      05/29/09 - 8323978: Recreate map triggers on downgrade
Rem    shorwitz    03/25/09 - Bug 4860137
Rem    rpalakod    02/18/09 - change name of near_realtime
Rem    rpalakod    02/05/09 - nrtidx api
Rem    shorwitz    01/09/09 - Bug 4860137: roll back maxterm limits
Rem    nenarkhe    01/22/09 - reverse MVDATA changes
Rem    rpalakod    01/07/09 - separate_offsets
Rem    rpalakod    01/05/09 - Reverse BIG IO changes
Rem    rpalakod    12/08/08 - lrg 3693400: 11.1.0.7 downgrade
Rem    rpalakod    12/08/08 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

REM ===================================================================
REM WILDCARD_MAXTERMS
REM ===================================================================

update dr$object_attribute
  set oat_val_min = 1
  where oat_id = 70106;
commit; 

REM ==================================================================
REM Reverse Change from bug 7353283 to disallow bigram attribute
REM   in Japanese_Lexer.
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=60210 and OAT_CLA_ID=6 and OAT_OBJ_ID=2 and OAT_ATT_ID=10
    and OAT_NAME='BIGRAM';
commit;

REM ==================================================================
REM Reverse Change from BIG_IO txn to disallow BIG_IO storage attribute
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=90110 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=10
    and OAT_NAME='BIG_IO';
commit;

REM ==================================================================
REM Reverse Change from SEP_OFF txn to disallow SEPARATE_OFFSETS 
REM    storage attribute
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=90111 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=11
    and OAT_NAME='SEPARATE_OFFSETS';

REM ===================================================================
REM WILDCARD_MAXTERMS
REM ===================================================================

update dr$object_attribute
  set oat_val_min = 1
  where oat_id = 70106;
commit;

REM ==================================================================
REM Reverse Change from MVDATA txn to disallow MV_TABLE_CLAUSE
REM    storage attribute
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=90112 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=12
    and OAT_NAME='MV_TABLE_CLAUSE';
commit;

delete from dr$object_attribute
  where OAT_ID=50212 and OAT_CLA_ID=5 and OAT_OBJ_ID=2 and OAT_ATT_ID=12
    and OAT_NAME='MVDATA';
commit;

delete from dr$object_attribute
  where OAT_ID=50312 and OAT_CLA_ID=5 and OAT_OBJ_ID=3 and OAT_ATT_ID=12
    and OAT_NAME='MVDATA';
commit;

delete from dr$object_attribute
  where OAT_ID=50512 and OAT_CLA_ID=5 and OAT_OBJ_ID=5 and OAT_ATT_ID=12
    and OAT_NAME='MVDATA';
commit;

REM ==================================================================
REM Reverse Change from NRTIDX txn to disallow NEAR_REALTIME storage attribute
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=90113 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=13
    and OAT_NAME='STAGE_ITAB';

delete from dr$object_attribute
  where OAT_ID=90114 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=14
    and OAT_NAME='G_TABLE_CLAUSE';

delete from dr$object_attribute
  where OAT_ID=90115 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=15
    and OAT_NAME='G_INDEX_CLAUSE';

commit;

REM ==================================================================
REM 8323978: Recreate 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(4000);
  pfx   varchar2(80);
  tpfx  varchar2(80);
begin
  for rec in all_indexes loop
    if (ctxsys.drixmd.IndexHasPTable(rec.idx_id)) then
      pfx := ctxsys.driutl.make_pfx(rec.username, rec.idx_name,
                                    '$', rec.ixp_id);
      tpfx := ctxsys.driutl.make_pfx(rec.username, rec.idx_name, 'T',
                                     rec.ixp_id);
      sql_string :=
        ctxsys.drvxtab.map_trigger_text(pfx, tpfx,
          gtab=>ctxsys.drixmd.IndexHasGTable(rec.idx_id));

        if rec.ixp_name is null then
          dbms_output.put_line('creating trigger for index ' || rec.username ||
          '.' || rec.idx_name);
        else
          dbms_output.put_line('creating trigger for index ' || rec.username ||
          '.' || rec.idx_name || ', partition ' || rec.ixp_name);
        end if;

      begin
        execute immediate sql_string;
      exception
        when others then
          if sqlcode != -4081 then
            -- ignore "trigger already exists" error
            raise;
          end if;
      end;
    end if;
  end loop;
end;
/