Rem
Rem Copyright (c) 2004, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      apse102.sql - migration script for downgrading olap aw component
Rem
Rem    DESCRIPTION
Rem      Downgrade OLAP AW to 10.2
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    zqiu        02/22/08 - call 11.1 script
Rem    cchiappa    09/21/06 - Deletes sequences from noexp$;
Rem    jcarey      08/17/06 - remove truncate trigger
Rem    jcarey      06/12/06 - drop truncate trigger 
Rem    ckearney    06/08/06 - drop dbms_aw_stats 
Rem    awesley     04/20/06 - Creation
Rem

execute sys.dbms_registry.downgrading('APS');

Rem
Rem Call 11.1 downgrade script
Rem
@@apse111.sql 

DROP PUBLIC SYNONYM dbms_aw_stats
/
DROP PACKAGE sys.dbms_aw_stats
/

-- For any existing version 10 or higher AWs, we want to delete the name
-- of their sequence from noexp$ since earlier versions don't expect it
-- to be there.
DECLARE
  CURSOR aw_cursor IS
    SELECT u.name, a.awname FROM aw$ a, user$ u
      WHERE a.version >= 1 AND u.user#=a.owner#;
  awowner VARCHAR2(50);
  awname  VARCHAR2(50);
BEGIN
  OPEN aw_cursor;
  LOOP
    FETCH aw_cursor INTO awowner, awname;
    EXIT WHEN NOT aw_cursor%FOUND;
    DELETE FROM noexp$
      WHERE obj_type=6 AND owner=awowner AND name=awname||'_S$';
  END LOOP;
  CLOSE aw_cursor;
END;
/

execute sys.dbms_registry.downgraded('APS', '10.2.0');