Rem
Rem Copyright (c) 2004, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      apsu102.sql - migration script for upgrading olap aw component
Rem
Rem    DESCRIPTION
Rem      Upgrade OLAP AW from 10.2.0
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    zqiu        05/02/08 - bug 6910052, revoke DML rights
Rem    zqiu        02/22/08 - call 11.1 script
Rem    cchiappa    09/21/06 - Insert sequence into noexp$
Rem    awesley     04/20/06 - Creation
Rem

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

-- We want to insert the name of the sequence for the AW into noexp$
-- but only if that AW would have a sequence (ie, is version 10 or higher)
-- and is not already mentioned in the list
INSERT INTO sys.noexp$(owner, name, obj_type)
  (SELECT u.name, a.awname||'_S$', 6
     FROM user$ u, aw$ a
     WHERE a.version >= 1 AND u.user#=a.owner# AND a.awname NOT IN
       (SELECT SUBSTR(n.name, 1, LENGTH(n.name)-3) FROM noexp$ n
          WHERE n.owner=u.name AND n.obj_type=6
            AND n.name LIKE '%\_S$' ESCAPE '\'));

Rem In 9.2 we gave too many privileges to select_catalog_role.
BEGIN
 execute immediate 
   'REVOKE DELETE, INSERT, UPDATE ON DBA_AWS from select_catalog_role';
 execute immediate 
   'REVOKE DELETE, INSERT, UPDATE ON DBA_AW_PS from select_catalog_role';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

Rem
Rem Call 11.1 upgrade script
Rem
@@apsu111.sql

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