Rem
Rem $Header: oraolap/admin/catnoaps.sql /main/4 2009/03/13 16:35:16 akociube Exp $
Rem
Rem catnoaps.sql
Rem
Rem Copyright (c) 2001, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      catnoaps.sql - Deinstall APS (OLAP) RDBMS component
Rem
Rem    DESCRIPTION
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    akociube    03/06/09 - Remove component info
Rem    cchiappa    04/02/04 - Fix misspelling
Rem    zqiu        01/13/04 - do something
Rem    cdalessi    10/11/02 - Creation


create or replace procedure aps_clean as
  cursor c1 IS select a.awname from aw$ a;
  awname varchar2(60);
  dropstmt varchar2(75);
begin
  
  /* Drop AW tables, the trigger will do the rest */
  for awname_rec in c1 loop 
    awname := 'SYS.AW$' || awname_rec.awname;
    dropstmt := 'drop table ' || awname ;
    dbms_output.put_line(dropstmt);
    execute immediate dropstmt;
    -- not all AWs will have a sequence on the AW table
    BEGIN
      awname := 'SYS.' || awname_rec.awname || '_S$';
      dropstmt := 'drop sequence ' || awname ;
      execute immediate dropstmt;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;    
  end loop;

  commit;
end;
/

-- anonymous block
DECLARE
  tb_exist number;
  olap_user number;
BEGIN 
  SELECT count(*) INTO tb_exist FROM aw$ WHERE awseq# >= 1000;
  SELECT count(*) INTO olap_user FROM gv$aw_olap;	
  IF tb_exist > 0 OR olap_user > 0 THEN
    RAISE_APPLICATION_ERROR(-20003,  
                  'User created AW objects exist, unable to deinstall OLAP');
  END IF;  
  sys.dbms_registry.removing('APS');
  
  BEGIN
  execute immediate 'DROP VIEW DBA_AW_PROP';
  execute immediate 'DROP VIEW USER_AW_PROP';
  execute immediate 'DROP VIEW ALL_AW_PROP';
  execute immediate 'DROP SYNONYM DBA_AW_PROP';
  execute immediate 'DROP SYNONYM USER_AW_PROP';
  execute immediate 'DROP SYNONYM ALL_AW_PROP';

  execute immediate 'DROP VIEW DBA_AW_OBJ';
  execute immediate 'DROP VIEW USER_AW_OBJ';
  execute immediate 'DROP VIEW ALL_AW_OBJ';

  execute immediate 'DROP SYNONYM DBA_AW_OBJ';
  execute immediate 'DROP SYNONYM USER_AW_OBJ';
  execute immediate 'DROP SYNONYM ALL_AW_OBJ';

  execute immediate 'DROP VIEW all_aw_numbers';
  EXCEPTION
    WHEN OTHERS THEN
	NULL;
  END;

  aps_clean;
  sys.dbms_registry.removed('APS');
END;
/
drop procedure aps_clean;

-- No longer show up in dba_registry
delete from registry$ where cid='APS' and status='99';