버전 비교

이전 버전 1

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.

업그레이드 사전 준비 작업

업그레이드 사전 작업 체크 스크립트 수행

코드 블럭
languagebash
title11g ORACLE_HOME을 기준으로 사전 작업 체크 스크립트 수행
linenumberstrue
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
<19c ORACLE_HOME 경로>/jdk/bin/java -jar <19c ORACLE_HOME 경로>/rdbms/admin/preupgrade.jar TERMINAL TEXT


펼치기


서식 미적용
[racdb1:/home/oracle]> export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

[racdb1:/home/oracle]> $ORACLE_BASE/product/19.0.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/db_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-04-22T15:19:11

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  RACDB
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
     DB Patch Level:  PSU 11.2.0.4.200114
         Compatible:  11.2.0.0.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Expression Filter                      [to be upgraded]  VALID
  Rule Manager                           [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  1.  (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.

      The database contains 5 objects in the recycle bin.

      The recycle bin must be completely empty before database upgrade.

  RECOMMENDED ACTIONS
  ===================
  2.  Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
      This action may be done now or when starting the database in upgrade mode
      using the 19 ORACLE HOME.

       Parameter                                 Currently  19 minimum
       ---------                                 ---------  ------------------
       processes                                       150                 300

      The database upgrade process requires certain initialization parameters
      to meet minimum values.  The Oracle upgrade process itself has minimum
      values which may be higher and are marked with an asterisk.  After
      upgrading, those asterisked parameter values may be reset if needed.

  3.  Remove the EM repository.

      - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
      19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

      Step 1: If database control is configured, stop EM Database Control,
      using the following command

        $> emctl stop dbconsole

      Step 2: Connect to the database using the SYS account AS SYSDBA

        SET ECHO ON;
        SET SERVEROUTPUT ON;
        @emremove.sql

      Without the set echo and serveroutput commands, you will not be able to
      follow the progress of the script.

      The database has an Enterprise Manager Database Control repository.

      Starting with Oracle Database 12c, the local Enterprise Manager Database
      Control does not exist anymore. The repository will be removed from your
      database during the upgrade.  This step can be manually performed before
      the upgrade to reduce downtime.

  4.  Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
      $ORACLE_HOME/olap/admin/catnoamd.sql script.

      The OLAP Catalog component, AMD, exists in the database.

      Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
      desupported and will be automatically marked as OPTION OFF during the
      database upgrade if present. Oracle recommends removing OLAP Catalog
      (OLAP AMD) before database upgrade.  This step can be manually performed
      before the upgrade to reduce downtime.

  5.  Upgrade Oracle Application Express (APEX) manually before the database
      upgrade.

      The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
      version 18.2.0.00.12.

      Starting with Oracle Database Release 18, APEX is not upgraded
      automatically as part of the database upgrade. Refer to My Oracle Support
      Note 1088970.1 for information about APEX installation and upgrades.

  6.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  7.  (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
      owner of the trigger or drop and re-create the trigger with a user that
      was granted directly with such. You can list those triggers using: SELECT
      OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
      TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
      DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').

      There is one or more database triggers whose owner does not have the
      right privilege on the database.

      The creation of database triggers must be done by users granted with
      ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
      directly.

  8.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  INFORMATION ONLY
  ================
  9.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSTEM                            1000 MB      1190 MB
      TEMP                               100 MB       150 MB

      Minimum tablespace sizes for upgrade are estimates.

  10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
      Database Oracle home to remove both EXF and RUL.

      Expression Filter (EXF) or Rules Manager (RUL) exist in the database.

      Starting with Oracle Database release 12.1, the Expression Filter (EXF)
      and Database Rules Manager (RUL) features are desupported, and are
      removed during the upgrade process.  This step can be manually performed
      before the upgrade to reduce downtime.

  11. Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database RACDB
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  12. Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 14 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  13. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  14. Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  INFORMATION ONLY
  ================
  15. Check the Oracle documentation for the identified components for their
      specific upgrade procedure.

      The database upgrade script will not upgrade the following Oracle
      components:  OLAP Catalog,OWB

      The Oracle database upgrade script upgrades most, but not all Oracle
      Database components that may be installed.  Some components that are not
      upgraded may have their own upgrade scripts, or they may be deprecated or
      obsolete.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database RACDB
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-04-22T15:19:14




파라미터 변경

코드 블럭
languagebash
title프로세스 300 이상으로 변경
linenumberstrue
alter system set processes=300 sid='*' scope=spfile;


펼치기


서식 미적용
SQL> col NAME form a10
SQL> col VALUE form a10
SQL> select INST_ID, NAME, VALUE from GV$PARAMETER where NAME = 'processes';

   INST_ID NAME       VALUE
---------- ---------- ----------
         2 processes  150
         1 processes  150

SQL> alter system set processes=300 sid='*' scope=spfile;

System altered.

Upgrade를 위해 추후에 데이터베이스를 다시 재기동할 계획이므로 업그레이드할 노드만 재기동하거나, 이 부분에서의 데이터베이스 재기동은 생략해도 됩니다.

펼치기

1번 인스턴스 재기동

서식 미적용
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size             721423760 bytes
Database Buffers         2466250752 bytes
Redo Buffers               16904192 bytes
Database mounted.
Database opened.

SQL> col NAME form a10
SQL> col VALUE form a10
SQL> select INST_ID, NAME, VALUE from GV$PARAMETER where NAME = 'processes';

   INST_ID NAME       VALUE
---------- ---------- ----------
         2 processes  150
         1 processes  300

2번 인스턴스 재기동

서식 미적용
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size             721423760 bytes
Database Buffers         2466250752 bytes
Redo Buffers               16904192 bytes
Database mounted.
Database opened.

SQL> col NAME form a10
SQL> col VALUE form a10
SQL> select INST_ID, NAME, VALUE from GV$PARAMETER where NAME = 'processes';

   INST_ID NAME       VALUE
---------- ---------- ----------
         2 processes  300
         1 processes  300





EM Repository 제거

코드 블럭
languagebash
title삭제 스크립트 복제 및 EMDC 중지
linenumberstrue
cp <19c ORACLE_HOME 경로>/rdbms/admin/emremove.sql <11g ORACLE_HOME 경로>/rdbms/admin/
emctl stop dbconsole


펼치기

업그레이드 작업을 수행할 노드에서만 파일을 복사합니다.

서식 미적용
[racdb1:/home/oracle]> cp /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin

[racdb1:/home/oracle]> emctl stop dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_rac1_racdb not found.


서식 미적용
[racdb2:/home/oracle]> emctl stop dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_rac2_racdb not found.





코드 블럭
languagebash
title삭제 스크립트 수행
linenumberstrue
SET ECHO ON;
SET SERVEROUTPUT ON;
@?/rdbms/admin/emremove.sql


펼치기


서식 미적용
SQL> col COMP_NAME form a30
SQL> col STATUS form a10
SQL> col VERSION form a15
SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='EM';

COMP_NAME                      STATUS     VERSION
------------------------------ ---------- ---------------
Oracle Enterprise Manager      VALID      11.2.0.4.0

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/rdbms/admin/emremove.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/emremove.sql /main/5 2017/05/28 22:46:05 stanaya Exp $
SQL> Rem
SQL> Rem emremove.sql
SQL> Rem
SQL> Rem Copyright (c) 2012, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem emremove.sql - This script removes EM Schema from RDBMS
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem This script will drop the Oracle Enterprise Manager related schemas and objects.
SQL> Rem This script might take few minutes to complete; it has 6 phases to complete the process.
SQL> Rem The script may take longer if you have SYSMAN and related sessions are active
SQL> Rem from Oracle Enterprise Manager(OEM) application.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem Please do following two steps  before running this script
SQL> Rem set serveroutput on
SQL> Rem set echo on
SQL> Rem
SQL> Rem
SQL> Rem
SQL> Rem    RECOMMENDATIONS
SQL> Rem
SQL> Rem You are recommended to shutdown DB Control application immediately before running this
SQL> Rem OEM repository removal script.
SQL> Rem To shutdown DB Control application, you need to run emctl stop dbconsole
SQL> Rem
SQL> Rem
SQL> Rem Steps to be performed manually (after this script is run)
SQL> Rem
SQL> Rem
SQL> Rem Please note that you need to remove the DB Control Configuration Files
SQL> Rem manually to remove DB Control completly; remove the following
SQL> Rem directories from your filesystem
SQL> Rem <ORACLE_HOME>/<hostname_sid>
SQL> Rem <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
SQL> Rem
SQL> Rem If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,
SQL> Rem then the following directory also needs to be removed from the file system.
SQL> Rem <ORACLE_HOME>/<hostname_sid>.upgrade
SQL> Rem <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
SQL> Rem
SQL> Rem On Microsoft platforms, also delete the DB Console service, generally with name
SQL> Rem OracleDBConsole<sid>
SQL> Rem
SQL> Rem #############################################################################################
SQL> Rem
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/emremove.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/emremove.sql
SQL> Rem    SQL_PHASE: UTILITY
SQL> Rem    SQL_STARTUP_MODE: NORMAL
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    spramani    01/17/17 - fix for 24518751
SQL> Rem    spramani    08/03/16 - fix # 24330891
SQL> Rem    spramani    07/20/12 - more fix
SQL> Rem    spramani    12/21/11 - Created
SQL> Rem
SQL>
SQL>
SQL> DEFINE EM_REPOS_USER ="SYSMAN"
SQL> DEFINE LOGGING = "VERBOSE"
SQL>
SQL> declare
  2
  3    l_username dba_role_privs.grantee%type;
  4    l_user_name dba_role_privs.grantee%type;
  5    l_sql varchar2(1024);
  6    l_sysman_user number;
  7    l_mgmt_users_src number;
  8    l_sid number;
  9    l_serial number;
 10    err number;
 11    err_msg varchar2(128);
 12    c number;
 13    l_removejobs varchar2(1024);
 14    l_set_context varchar2(1024);
 15    l_client varchar2(16) := ' ';
 16    l_context integer := 5;
 17    l_verbose boolean := FALSE;
 18    l_msg varchar2(1024);
 19    l_open_acc number := 0;
 20
 21    TYPE SESSION_REC IS RECORD
 22     (sid     v$session.sid%type,
 23      serial_no v$session.serial#%type);
 24    TYPE     SESSION_TYPE IS TABLE OF SESSION_REC;
 25    l_sessions SESSION_TYPE;
 26
 27
 28
 29    l_job_process_count NUMBER ;
 30    TYPE TBSP_ARRAY IS TABLE OF varchar2(64) INDEX BY BINARY_INTEGER ;
 31    l_tablespaces TBSP_ARRAY;
 32
 33      PROCEDURE set_job_process_count(p_count IN NUMBER)
 34      IS
 35      BEGIN
 36        --scope=memory so it will be reset on instance startup
 37        -- SID=* to take care of RAC
 38        IF p_count >=0
 39        THEN
 40          EXECUTE IMMEDIATE 'ALTER SYSTEM SET job_queue_processes='
 41                      ||p_count||' SID=''*'' scope=memory' ;
 42        END IF ;
 43      EXCEPTION WHEN OTHERS THEN NULL ;
 44      END set_job_process_count ;
 45
 46      PROCEDURE LOG_MESSAGE (verbose boolean, message varchar2)
 47      IS
 48      BEGIN
 49          IF (verbose = TRUE)
 50          THEN
 51              DBMS_OUTPUT.PUT_LINE(message);
 52          END IF;
 53      END LOG_MESSAGE;
 54
 55      FUNCTION get_job_process_count
 56      RETURN NUMBER
 57      IS
 58      l_value NUMBER ;
 59      BEGIN
 60        SELECT value
 61          INTO l_value
 62          FROM v$parameter
 63         WHERE name = 'job_queue_processes' ;
 64         RETURN(l_value) ;
 65      EXCEPTION
 66      WHEN OTHERS THEN
 67         RETURN(10) ;
 68      END get_job_process_count ;
 69  begin
 70      IF (upper('&LOGGING') = 'VERBOSE')
 71      THEN
 72        l_verbose := TRUE;
 73      END IF;
 74
 75      LOG_MESSAGE(l_verbose,' This script will drop the Oracle Enterprise Manager related schemas and objects.');
 76      LOG_MESSAGE(l_verbose, ' This script might take few minutes to complete; it has 6 phases to complete the process.');
 77      LOG_MESSAGE(l_verbose,' The script may take longer if you have SYSMAN and related sessions are active');
 78      LOG_MESSAGE(l_verbose,' from Oracle Enterprise Manager(OEM) application.');
 79      LOG_MESSAGE(l_verbose,' ');
 80      LOG_MESSAGE(l_verbose,' ');
 81      LOG_MESSAGE(l_verbose,' Recommendations:');
 82      LOG_MESSAGE(l_verbose,' ');
 83      LOG_MESSAGE(l_verbose,' ');
 84      LOG_MESSAGE(l_verbose,' You are recommended to shutdown DB Control application immediately before running this');
 85      LOG_MESSAGE(l_verbose,' OEM repository removal script.');
 86      LOG_MESSAGE(l_verbose,' To shutdown DB Control application, you need to run: emctl stop dbconsole');
 87      LOG_MESSAGE(l_verbose,' ');
 88      LOG_MESSAGE(l_verbose,' ');
 89      LOG_MESSAGE(l_verbose,' Steps to be performed manually (after this script is run):');
 90      LOG_MESSAGE(l_verbose,' ');
 91      LOG_MESSAGE(l_verbose,' ');
 92      LOG_MESSAGE(l_verbose,' Please note that you need to remove the DB Control Configuration Files');
 93      LOG_MESSAGE(l_verbose,' manually to remove DB Control completly; remove the following');
 94      LOG_MESSAGE(l_verbose,' directories from your filesystem:');
 95      LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/<hostname_sid>');
 96      LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>');
 97      LOG_MESSAGE(l_verbose,' ');
 98      LOG_MESSAGE(l_verbose,' If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,');
 99      LOG_MESSAGE(l_verbose,' then the following directory also needs to be removed from the file system.');
100      LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/<hostname_sid>.upgrade');
101      LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade');
102      LOG_MESSAGE(l_verbose,' ');
103      LOG_MESSAGE(l_verbose,' On Microsoft platforms, also delete the DB Console service, generally with name');
104      LOG_MESSAGE(l_verbose,' OracleDBConsole<sid>');
105
106
107      LOG_MESSAGE(l_verbose,'Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle Enterprise Manager related schemas; except SYSMAN ...');
108
109      c := 0;
110      BEGIN
111          select count(1) into l_sysman_user from all_users where username='SYSMAN';
112      IF (l_sysman_user > 0 ) THEN
113      BEGIN
114
115          BEGIN
116              LOG_MESSAGE(l_verbose,'dropping AQ related objests from SYSMAN ...');
117
118              DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);
119          EXCEPTION
120              WHEN OTHERS THEN
121               err := SQLCODE;
122               LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: AQ related objects are dropped already or not found');
123          END;
124
125          BEGIN
126              -- reduce job_queue_processes to zero
127             l_job_process_count := get_job_process_count ;
128             set_job_process_count(0) ;
129             LOG_MESSAGE(l_verbose,'saved job_queue_process=' || l_job_process_count || ', set to 0, now removing Oracle EM jobs ...');
130             l_removejobs := 'BEGIN ' ||  'SYSMAN' || '.emd_maintenance.remove_em_dbms_jobs; END;';
131             execute immediate l_removejobs;
132          EXCEPTION
133             WHEN OTHERS THEN
134                 err := SQLCODE;
135                 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: EM jobs are dropped already or not found');
136          END;
137
138      END;
139      END IF;
140      END;
141
142      -- First, drop all users, except SYSMAN who have MGMT_USER role and
143      -- are created by EM. All users created by EM will have a record
144      -- in MGMT_CREATED_USERS table
145      --
146
147      BEGIN
148          select count(1) into l_sysman_user from all_users where username='SYSMAN';
149          IF (l_sysman_user > 0 ) THEN
150          BEGIN
151
152          LOOP  --  part 1 main loop
153                -- handle SYSMAN is partially dropped
154               select count(1) into l_mgmt_users_src from all_objects where object_name='MGMT_CREATED_USERS' and owner='SYSMAN';
155               IF(l_mgmt_users_src = 0 ) THEN
156                   EXIT;
157               END IF;
158          BEGIN
159            LOG_MESSAGE(l_verbose,'finding users who needs to be dropped ...');
160            l_username := '';
161            BEGIN
162                execute immediate 'select grantee
163                   from sys.dba_role_privs
164                   where granted_role ='||DBMS_ASSERT.ENQUOTE_LITERAL('MGMT_USER')||
165                    ' AND grantee IN (SELECT user_name
166                                 FROM SYSMAN.MGMT_CREATED_USERS
167                                  WHERE SYSTEM_USER=0)
168                    AND ROWNUM=1'
169                    into l_user_name;
170                 LOG_MESSAGE(l_verbose,'found user name: ' || l_user_name);
171                    l_username := DBMS_ASSERT.ENQUOTE_NAME(l_user_name, FALSE);
172
173                 EXECUTE IMMEDIATE 'ALTER USER '||l_username||' ACCOUNT LOCK' ;
174            EXCEPTION
175                  WHEN NO_DATA_FOUND THEN
176                      LOG_MESSAGE(l_verbose,l_username || ' IS ALREADY DROPPED');
177                  EXIT; -- THEN RETURN ;
178            END ;
179
180
181            FOR cnt in 1 .. 150 LOOP -- session kill loop
182
183              BEGIN
184                -- FOR crec in (SELECT sid, serial#
185                --               FROM v$session
186                --              WHERE username=l_username
187                --                AND status NOT IN('KILLED'))
188
189                l_sql := 'SELECT sid, serial#
190                               FROM v$session
191                              WHERE username='|| DBMS_ASSERT.ENQUOTE_LITERAL(l_user_name)||'
192                              AND status NOT IN(''KILLED'')';
193                execute immediate l_sql BULK COLLECT INTO l_sessions;
194
195                FOR i in 1..l_sessions.COUNT
196                LOOP   --  cursor loop
197                BEGIN
198                     LOG_MESSAGE(l_verbose,'killing related sessions : sid= ' || l_sessions(i).sid || ' serial#= ' || l_sessions(i).serial_no || ' ...');
199                       EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' || DBMS_ASSERT.ENQUOTE_LITERAL(l_sessions(i).sid || ',' || l_sessions(i).serial_no)||' IMMEDIATE';
200                EXCEPTION
201                        WHEN OTHERS THEN
202                           err := SQLCODE;
203                           IF err != -30 THEN
204                              LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no session found; or already killed.');
205                              EXIT;
206                           END IF;
207                END;
208                COMMIT;
209                END LOOP; -- end cursor loop
210
211              EXCEPTION
212                  WHEN OTHERS THEN
213                    err := SQLCODE;
214                    IF err != -30 THEN
215                      LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no session found; or already killed.');
216                      EXIT;
217                    END IF;
218              END;
219
220              IF SQL%NOTFOUND THEN
221                 LOG_MESSAGE(l_verbose,'found [sql%notfound]: no session found; or already killed.');
222                 EXIT;
223              END IF;
224
225            COMMIT;
226
227            END LOOP;  -- end session killing loop
228            LOG_MESSAGE(l_verbose,' Dropping user : ' || l_username || '...');
229
230            EXECUTE IMMEDIATE 'drop user ' || l_username || ' cascade';
231            exit;
232            EXCEPTION
233              WHEN NO_DATA_FOUND THEN
234                LOG_MESSAGE(l_verbose,'found [no_data_found]: no user/corresponding sessions found related to DB Control');
235                 EXIT;
236              WHEN OTHERS THEN
237                err := SQLCODE;
238                IF err = -1918 THEN
239                 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no DB Control user/corresponding sessions found related to DB Control');
240                  EXIT;
241                ELSE
242                  IF err = -1940 THEN
243                    NULL;
244                  ELSE
245                    -- keep count of try to drop EM related user and sessions
246                    -- give up after 50 try
247
248                    c := c+1;
249                    IF c > 50 THEN
250                       RAISE;
251                    END IF;
252                  END IF;
253                END IF;
254          END;
255          END LOOP; -- end part main loop
256         END;
257         ELSE
258                 LOG_MESSAGE(l_verbose,'SYSMAN IS ALREADY DROPPED');
259         END IF;
260     END;
261
262     BEGIN
263         -- Now, drop the SYSMAN user
264         LOG_MESSAGE(l_verbose,'Finished phase 1');
265         LOG_MESSAGE(l_verbose,'Starting phase 2 : Dropping SYSMAN schema ...');
266
267         c := 0;
268         -- validate user exists
269         select count(1) into l_sysman_user from all_users where username='SYSMAN';
270         IF (l_sysman_user > 0 ) THEN
271         BEGIN
272
273             BEGIN
274               --  SELECT username
275               --   INTO l_username
276               --   FROM dba_users
277               --   WHERE username = 'SYSMAN';
278                  -- l_user_name := 'SYSMAN';
279                  -- l_username = DBMS_ASSERT.ENQUOTE_NAME(l_user_name);
280                  EXECUTE IMMEDIATE 'ALTER USER SYSMAN ACCOUNT LOCK' ;
281             EXCEPTION
282                  WHEN NO_DATA_FOUND THEN
283                      LOG_MESSAGE(l_verbose,'SYSMAN IS ALREADY DROPPED');
284                  -- THEN RETURN ;
285             END ;
286
287
288             BEGIN
289                 LOOP  -- main loop
290                     BEGIN
291                         FOR cnt in 1 .. 150 LOOP -- session kill loop
292                             BEGIN
293                                 FOR crec in (SELECT sid, serial#
294                                     FROM gv$session
295                                       WHERE (username='SYSMAN' OR
296                                          schemaname='SYSMAN')
297                                       AND status != 'KILLED')
298                                 LOOP   --cursor loop
299                                     BEGIN
300                                         LOG_MESSAGE(l_verbose,'killing related sessions : sid= ' || crec.sid || ' serial#= ' || crec.serial#  || ' ...');
301                                         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' ||
302                                           DBMS_ASSERT.ENQUOTE_LITERAL(crec.sid || ',' || crec.serial#)|| ' IMMEDIATE';
303                                     EXCEPTION
304                                     WHEN OTHERS THEN
305                                         err := SQLCODE;
306                                         IF err != -30 THEN
307                                             LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: SYSMAN related sessions are already killed; no session found');
308                                             EXIT;
309                                         END IF;
310                                    END;
311                                    COMMIT;
312                                 END LOOP;  -- cursor loop ends
313                             EXCEPTION
314                                WHEN OTHERS THEN
315                                   err := SQLCODE;
316                                   IF err != -30 THEN
317                                       LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: SYSMAN related sessions are already killed; no session found');
318                                       EXIT;
319                                   END IF;
320                             END;
321                             IF SQL%NOTFOUND THEN
322                                  LOG_MESSAGE(l_verbose,'found [sql%notfound]: SYSMAN related sessions are already killed; no session found');
323                                  EXIT;
324                            END IF;
325                            COMMIT;
326                         END LOOP;  -- end of session kill loop
327
328                         -- END;
329                         LOG_MESSAGE(l_verbose,'dropping user :  ' || l_user_name || '...');
330                         execute immediate 'drop user SYSMAN cascade';
331                         set_job_process_count(l_job_process_count) ;
332                         exit;
333
334                         -- >> START - Dropping the Tablespaces
335                         LOG_MESSAGE(l_verbose,'Finished phase 2');
336                         LOG_MESSAGE(l_verbose,'Starting phase 3 : Dropping Oracle Enterprise Manager related tablespaces ...');
337
338                         LOG_MESSAGE(l_verbose,'No seperate TABLESPACES Exist for EM;  all in SYSAUX; no action taken');
339                         -- >> END - Dropping the Tablespaces
340
341                     EXCEPTION
342                        WHEN NO_DATA_FOUND THEN
343                            LOG_MESSAGE(l_verbose,'found [ no_data_found]: no sysman/corresponding sessions');
344                            EXIT;
345                        WHEN OTHERS THEN
346                        err := SQLCODE;
347                        IF err = -1918 THEN
348                            LOG_MESSAGE(l_verbose,'found [sqlcode:1918]: no sysman/corresponding sessions');
349                            EXIT;
350                        ELSIF err = -1940 THEN
351                                NULL;
352                        ELSE
353                            LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no sysman/corresponding sessions');
354                            c := c+1;
355                            IF c > 50 THEN
356                                RAISE;
357                            END IF;
358                        END IF;
359                     END;
360                 END LOOP;  -- end of main loop
361             END;
362             LOG_MESSAGE(l_verbose,'SYSMAN dropped');
363             commit;
364         END;
365      ELSE
366        LOG_MESSAGE(l_verbose,'SYSMAN is already dropped');
367      END IF;
368      EXCEPTION
369          WHEN OTHERS THEN
370          set_job_process_count(l_job_process_count) ;
371          RAISE ;
372      END;
373
374  BEGIN
375
376      -- Drop basic roles.
377      LOG_MESSAGE(l_verbose,'Finished phase 3');
378      LOG_MESSAGE(l_verbose,'Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...');
379
380      BEGIN
381        execute immediate 'drop role MGMT_USER';
382      EXCEPTION
383            WHEN OTHERS THEN
384            LOG_MESSAGE(l_verbose,'Role MGMT_USER already dropped');
385      END;
386      --
387      -- Drop the following synonyms related to REPOS Schema
388      --
389      LOG_MESSAGE(l_verbose,'Finished phase 4');
390      LOG_MESSAGE(l_verbose,'Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms ...');
391
392
393      BEGIN
394        FOR crec in (SELECT synonym_name,table_owner,table_name
395                     FROM dba_synonyms
396                     WHERE owner = 'PUBLIC'
397                     AND table_owner = 'SYSMAN')
398        LOOP
399            BEGIN
400                LOG_MESSAGE(l_verbose,'Dropping synonym : ' || crec.synonym_name || ' ... ');
401                EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.synonym_name);
402
403            EXCEPTION
404                when others then
405                LOG_MESSAGE(l_verbose,'Public synonym ' || crec.synonym_name ||
406                                     ' cannot be dropped');
407                -- continue dropping other synonyms.
408            END;
409        END LOOP;
410      END;
411
412      BEGIN
413        LOG_MESSAGE(l_verbose,'Finished phase 5');
414        LOG_MESSAGE(l_verbose,'Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...');
415        FOR crec in (select role from sys.dba_roles where role like 'MGMT_%')
416        LOOP
417          LOG_MESSAGE(l_verbose,'Dropping role: ' || crec.role ||' ...');
418          execute immediate 'drop role ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.role);
419          commit;
420        END LOOP;
421      EXCEPTION
422          when NO_DATA_FOUND THEN
423            LOG_MESSAGE(l_verbose,'Roles like MGMT% do not exist');
424          WHEN OTHERS THEN
425            err := SQLCODE;
426            LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: no MGMT% roles to drop');
427      END;
428
429      -- lock DBSNMP user
430      BEGIN
431          BEGIN
432             LOG_MESSAGE(l_verbose,'Process DBSNMP user');
433             select count(1) into l_open_acc  from DBA_USERS where USERNAME ='DBSNMP' and ACCOUNT_STATUS='OPEN';
434          EXCEPTION
435             when NO_DATA_FOUND THEN
436                LOG_MESSAGE(l_verbose,'User DBSNMP does not exist');
437             WHEN OTHERS THEN
438                  err := SQLCODE;
439                  LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while checking DBSNMP user status');
440
441          END;
442
443          IF (l_open_acc > 0 ) THEN
444              BEGIN
445                  execute immediate 'ALTER USER DBSNMP PASSWORD EXPIRE';
446                   LOG_MESSAGE(l_verbose,'DBSNMP user password is made expired');
447              EXCEPTION
448                  WHEN OTHERS THEN
449                      err := SQLCODE;
450                      LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while expiring DBSNMP user password');
451              END;
452
453              BEGIN
454                  execute immediate 'ALTER USER DBSNMP ACCOUNT LOCK';
455                   LOG_MESSAGE(l_verbose,'User DBSNMP is locked');
456              EXCEPTION
457                  WHEN OTHERS THEN
458                      err := SQLCODE;
459                      LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while locking DBSNMP user');
460              END;
461
462          END IF;
463          LOG_MESSAGE(l_verbose,'Done processing DBSNMP user');
464      END;
465
466      LOG_MESSAGE(l_verbose,'Finished phase 6');
467      LOG_MESSAGE(l_verbose,'The Oracle Enterprise Manager related schemas and objects are dropped.');
468      LOG_MESSAGE(l_verbose,'Do the manual steps to studown the DB Control if not done before running this script and then delete the DB Control configuration files');
469      commit;
470  END;
471  END;
472  /
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')
This script will drop the Oracle Enterprise Manager related schemas and objects.
This script might take few minutes to complete; it has 6 phases to complete the
process.
The script may take longer if you have SYSMAN and related sessions are active
from Oracle Enterprise Manager(OEM) application.
Recommendations:
You are recommended to shutdown DB Control application immediately before
running this
OEM repository removal script.
To shutdown DB Control application, you need to run: emctl stop dbconsole
Steps to be performed manually (after this script is run):
Please note that you need to remove the DB Control Configuration Files
manually to remove DB Control completly; remove the following
directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to
10.2.0.4,
then the following directory also needs to be removed from the file system.
<ORACLE_HOME>/<hostname_sid>.upgrade
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
On Microsoft platforms, also delete the DB Console service, generally with name
OracleDBConsole<sid>
Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle
Enterprise Manager related schemas; except SYSMAN ...
dropping AQ related objests from SYSMAN ...
saved job_queue_process=1000, set to 0, now removing Oracle EM jobs ...
finding users who needs to be dropped ...
found user name: MGMT_VIEW
found [sql%notfound]: no session found; or already killed.
Dropping user : "MGMT_VIEW"...
Finished phase 1
Starting phase 2 : Dropping SYSMAN schema ...
found [sql%notfound]: SYSMAN related sessions are already killed; no session
found
dropping user :  MGMT_VIEW...
SYSMAN dropped
Finished phase 3
Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...
Finished phase 4
Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms
...
Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ...
Dropping synonym : SMP_EMD_TARGET_OBJ ...
Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY ...
Dropping synonym : SMP_EMD_NVPAIR_ARRAY ...
Dropping synonym : SMP_EMD_NVPAIR ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY ...
Dropping synonym : SMP_EMD_DELETE_REC_ARRAY ...
Dropping synonym : SMP_EMD_AVAIL_OBJ ...
Dropping synonym : SETEMVIEWUSERCONTEXT ...
Dropping synonym : MGMT_VIEW_UTIL ...
Dropping synonym : MGMT_USER ...
Dropping synonym : MGMT_TYPE_PROPERTIES ...
Dropping synonym : MGMT_TARGET_PROPERTIES ...
Dropping synonym : MGMT_TARGET_MEMBERSHIPS ...
Dropping synonym : MGMT_TARGET_BLACKOUTS ...
Dropping synonym : MGMT_TARGETS ...
Dropping synonym : MGMT_TARGET ...
Dropping synonym : MGMT_STRING_METRIC_HISTORY ...
Dropping synonym : MGMT_SEVERITY_OBJ ...
Dropping synonym : MGMT_SEVERITY_ARRAY ...
Dropping synonym : MGMT_SEVERITY ...
Dropping synonym : MGMT_PREFERENCES ...
Dropping synonym : MGMT_PAF_PROCS_LATEST ...
Dropping synonym : MGMT_PAF_JOBS ...
Dropping synonym : MGMT_PAF$STATES ...
Dropping synonym : MGMT_PAF$PROCEDURES ...
Dropping synonym : MGMT_PAF$INSTANCES ...
Dropping synonym : MGMT_PAF$APPLICATIONS ...
Dropping synonym : MGMT_NAME_VALUES ...
Dropping synonym : MGMT_NAME_VALUE ...
Dropping synonym : MGMT_METRIC_THRESHOLDS ...
Dropping synonym : MGMT_METRIC_ERRORS ...
Dropping synonym : MGMT_METRIC_COLLECTIONS ...
Dropping synonym : MGMT_METRICS_RAW ...
Dropping synonym : MGMT_METRICS_COMPOSITE_KEYS ...
Dropping synonym : MGMT_METRICS_1HOUR ...
Dropping synonym : MGMT_METRICS_1DAY ...
Dropping synonym : MGMT_METRICS ...
Dropping synonym : MGMT_MESSAGES ...
Dropping synonym : MGMT_LONG_TEXT ...
Dropping synonym : MGMT_LOG ...
Dropping synonym : MGMT_JOB_TARGET ...
Dropping synonym : MGMT_JOB_SCHEDULE ...
Dropping synonym : MGMT_JOB_PARAMETER ...
Dropping synonym : MGMT_JOB_OUTPUT ...
Dropping synonym : MGMT_JOB_EXEC_SUMMARY ...
Dropping synonym : MGMT_JOB_EXECUTION ...
Dropping synonym : MGMT_JOB_EXECPLAN ...
Dropping synonym : MGMT_JOBS ...
Dropping synonym : MGMT_JOB ...
Dropping synonym : MGMT_IP_TGT_GUID_ARRAY ...
Dropping synonym : MGMT_GUID_OBJ ...
Dropping synonym : MGMT_GUID_ARRAY ...
Dropping synonym : MGMT_GLOBAL ...
Dropping synonym : MGMT_DELTA_VALUES ...
Dropping synonym : MGMT_DELTA_VALUE ...
Dropping synonym : MGMT_DELTA_ID_VALUES ...
Dropping synonym : MGMT_DELTA_IDS ...
Dropping synonym : MGMT_DELTA_ENTRY_VALUES ...
Dropping synonym : MGMT_DELTA_ENTRY ...
Dropping synonym : MGMT_DELTA ...
Dropping synonym : MGMT_CURRENT_SEVERITY ...
Dropping synonym : MGMT_CURRENT_METRIC_ERRORS ...
Dropping synonym : MGMT_CURRENT_METRICS ...
Dropping synonym : MGMT_CURRENT_AVAILABILITY ...
Dropping synonym : MGMT_CREDENTIAL ...
Dropping synonym : MGMT_COLLECTION_PROPERTIES ...
Dropping synonym : MGMT_AVAILABILITY ...
Dropping synonym : MGMT_ADMIN ...
Dropping synonym : MGMT$TXN_PERF_RAW ...
Dropping synonym : MGMT$TXN_PERF_HOUR ...
Dropping synonym : MGMT$TXN_PERF_DAY ...
Dropping synonym : MGMT$TEMPLATE_POLICY_SETTINGS ...
Dropping synonym : MGMT$TEMPLATE_METRIC_SETTINGS ...
Dropping synonym : MGMT$TEMPLATE_METRICCOLLECTION ...
Dropping synonym : MGMT$TEMPLATES ...
Dropping synonym : MGMT$TARGET_TYPE_PROPERTIES ...
Dropping synonym : MGMT$TARGET_TYPE_DEF ...
Dropping synonym : MGMT$TARGET_TYPE ...
Dropping synonym : MGMT$TARGET_PROPERTIES ...
Dropping synonym : MGMT$TARGET_POLICY_SETTINGS ...
Dropping synonym : MGMT$TARGET_POLICY_EVAL_SUMM ...
Dropping synonym : MGMT$TARGET_POLICIES ...
Dropping synonym : MGMT$TARGET_METRIC_SETTINGS ...
Dropping synonym : MGMT$TARGET_METRIC_COLLECTIONS ...
Dropping synonym : MGMT$TARGET_MEMBERS ...
Dropping synonym : MGMT$TARGET_FLAT_MEMBERS ...
Dropping synonym : MGMT$TARGET_COMPOSITE ...
Dropping synonym : MGMT$TARGET_COMPONENTS ...
Dropping synonym : MGMT$TARGET_ASSOCIATIONS ...
Dropping synonym : MGMT$TARGET ...
Dropping synonym : MGMT$STORAGE_REPORT_VOLUME ...
Dropping synonym : MGMT$STORAGE_REPORT_PATHS ...
Dropping synonym : MGMT$STORAGE_REPORT_NFS ...
Dropping synonym : MGMT$STORAGE_REPORT_LOCALFS ...
Dropping synonym : MGMT$STORAGE_REPORT_KEYS ...
Dropping synonym : MGMT$STORAGE_REPORT_ISSUES ...
Dropping synonym : MGMT$STORAGE_REPORT_DISK ...
Dropping synonym : MGMT$STORAGE_REPORT_DATA ...
Dropping synonym : MGMT$STEP_METRICS_RAW ...
Dropping synonym : MGMT$STEP_METRICS_HOURLY ...
Dropping synonym : MGMT$STEP_METRICS_DAILY ...
Dropping synonym : MGMT$STEP_GROUPS ...
Dropping synonym : MGMT$STEPS ...
Dropping synonym : MGMT$SOFTWARE_PATCHSETS ...
Dropping synonym : MGMT$SOFTWARE_PATCHES_IN_HOMES ...
Dropping synonym : MGMT$SOFTWARE_OTHERS ...
Dropping synonym : MGMT$SOFTWARE_ONEOFF_PATCHES ...
Dropping synonym : MGMT$SOFTWARE_HOME_PROPERTIES ...
Dropping synonym : MGMT$SOFTWARE_HOMES ...
Dropping synonym : MGMT$SOFTWARE_DEPENDENCIES ...
Dropping synonym : MGMT$SOFTWARE_COMP_PATCHSET ...
Dropping synonym : MGMT$SOFTWARE_COMPONENT_ONEOFF ...
Dropping synonym : MGMT$SOFTWARE_COMPONENTS ...
Dropping synonym : MGMT$RACDB_INTERCONNECTS ...
Dropping synonym : MGMT$POLICY_VIOL_NOTIF_LOG ...
Dropping synonym : MGMT$POLICY_VIOL_ANNOTATIONS ...
Dropping synonym : MGMT$POLICY_VIOLATION_HISTORY ...
Dropping synonym : MGMT$POLICY_VIOLATION_CURRENT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CTXT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CONTEXT ...
Dropping synonym : MGMT$POLICY_PARAMETERS ...
Dropping synonym : MGMT$POLICIES ...
Dropping synonym : MGMT$PATCH_ADVISORIES ...
Dropping synonym : MGMT$OS_SUMMARY ...
Dropping synonym : MGMT$OS_PROPERTIES ...
Dropping synonym : MGMT$OS_PATCHES ...
Dropping synonym : MGMT$OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$OS_HW_SUMMARY ...
Dropping synonym : MGMT$OS_FS_MOUNT ...
Dropping synonym : MGMT$OS_COMPONENTS ...
Dropping synonym : MGMT$ORACLE_SW_GRP_TARGETS ...
Dropping synonym : MGMT$ORACLE_SW_GRP_INSTALL ...
Dropping synonym : MGMT$ORACLE_SW_ENT_TARGETS ...
Dropping synonym : MGMT$ORACLE_SW_ENT_INSTALL ...
Dropping synonym : MGMT$MISSING_TARGETS_IN_GROUPS ...
Dropping synonym : MGMT$MISSING_TARGETS ...
Dropping synonym : MGMT$METRIC_HOURLY ...
Dropping synonym : MGMT$METRIC_ERROR_HISTORY ...
Dropping synonym : MGMT$METRIC_ERROR_CURRENT ...
Dropping synonym : MGMT$METRIC_DETAILS ...
Dropping synonym : MGMT$METRIC_DAILY ...
Dropping synonym : MGMT$METRIC_CURRENT ...
Dropping synonym : MGMT$METRIC_COLLECTION ...
Dropping synonym : MGMT$METRIC_CATEGORIES ...
Dropping synonym : MGMT$MESSAGES ...
Dropping synonym : MGMT$JOB_TARGETS ...
Dropping synonym : MGMT$JOB_STEP_HISTORY ...
Dropping synonym : MGMT$JOB_NOTIFICATION_LOG ...
Dropping synonym : MGMT$JOB_EXECUTION_HISTORY ...
Dropping synonym : MGMT$JOB_ANNOTATIONS ...
Dropping synonym : MGMT$JOBS ...
Dropping synonym : MGMT$INTERFACE_STATS ...
Dropping synonym : MGMT$HW_NIC ...
Dropping synonym : MGMT$HOSTPATCH_HOST_COMPL ...
Dropping synonym : MGMT$HOSTPATCH_HOSTS ...
Dropping synonym : MGMT$HOSTPATCH_GRP_COMPL_HIST ...
Dropping synonym : MGMT$HOSTPATCH_GROUPS ...
Dropping synonym : MGMT$HOMES_AFFECTED ...
Dropping synonym : MGMT$HA_RMAN_CONFIG_ALL ...
Dropping synonym : MGMT$HA_RMAN_CONFIG ...
Dropping synonym : MGMT$HA_MTTR ...
Dropping synonym : MGMT$HA_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$HA_INIT_PARAMS ...
Dropping synonym : MGMT$HA_INFO_ALL ...
Dropping synonym : MGMT$HA_INFO ...
Dropping synonym : MGMT$HA_FILES_ALL ...
Dropping synonym : MGMT$HA_FILES ...
Dropping synonym : MGMT$HA_BACKUP ...
Dropping synonym : MGMT$GRP_METRICS_RAW ...
Dropping synonym : MGMT$GRP_METRICS_HOURLY ...
Dropping synonym : MGMT$GRP_METRICS_DAILY ...
Dropping synonym : MGMT$GROUP_MEMBERS ...
Dropping synonym : MGMT$GROUP_FLAT_MEMBERSHIPS ...
Dropping synonym : MGMT$GROUP_DERIVED_MEMBERSHIPS ...
Dropping synonym : MGMT$ESA_WITH_GRANT_REPORT ...
Dropping synonym : MGMT$ESA_WITH_ADMIN_REPORT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REP_NT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REPORT ...
Dropping synonym : MGMT$ESA_TABSP_OWNERS_REPORT ...
Dropping synonym : MGMT$ESA_SYS_PUB_PKG_REPORT ...
Dropping synonym : MGMT$ESA_PUB_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_POWER_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_OH_PERMISSION_REPORT ...
Dropping synonym : MGMT$ESA_OH_OWNERSHIP_REPORT ...
Dropping synonym : MGMT$ESA_KEY_OBJECTS_REPORT ...
Dropping synonym : MGMT$ESA_EXMPT_ACCESS_REPORT ...
Dropping synonym : MGMT$ESA_DIRECT_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_DBA_ROLE_REPORT ...
Dropping synonym : MGMT$ESA_DBA_GROUP_REPORT ...
Dropping synonym : MGMT$ESA_CREATE_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_CONN_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_CATALOG_REPORT ...
Dropping synonym : MGMT$ESA_BECOME_USER_REPORT ...
Dropping synonym : MGMT$ESA_AUDIT_SYSTEM_REPORT ...
Dropping synonym : MGMT$ESA_ANY_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_ANY_DICT_REPORT ...
Dropping synonym : MGMT$ESA_ALL_PRIVS_REPORT ...
Dropping synonym : MGMT$EM_HOMES_PLATFORM ...
Dropping synonym : MGMT$ECM_VISIBLE_SNAPSHOTS ...
Dropping synonym : MGMT$ECM_CURRENT_SNAPSHOTS ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY6 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY5 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY4 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY3 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY2 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY1 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY ...
Dropping synonym : MGMT$E2E_RAW ...
Dropping synonym : MGMT$E2E_HOURLY ...
Dropping synonym : MGMT$E2E_1DAY ...
Dropping synonym : MGMT$DELTA_VIEW_DETAILS ...
Dropping synonym : MGMT$DELTA_VIEW ...
Dropping synonym : MGMT$DELTA_VENDOR_SW ...
Dropping synonym : MGMT$DELTA_PATCHSET_DETAILS ...
Dropping synonym : MGMT$DELTA_PATCHSETS ...
Dropping synonym : MGMT$DELTA_OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$DELTA_OS_COMP_DETAILS ...
Dropping synonym : MGMT$DELTA_OS_COMPONENTS ...
Dropping synonym : MGMT$DELTA_ORACLE_HOME ...
Dropping synonym : MGMT$DELTA_ONEOFF_PATCHES ...
Dropping synonym : MGMT$DELTA_INIT ...
Dropping synonym : MGMT$DELTA_HOST_CONFIG ...
Dropping synonym : MGMT$DELTA_HARDWARE ...
Dropping synonym : MGMT$DELTA_FS_MOUNT ...
Dropping synonym : MGMT$DELTA_COMPONENT_DETAILS ...
Dropping synonym : MGMT$DELTA_COMPONENTS ...
Dropping synonym : MGMT$DB_TABLESPACES_ALL ...
Dropping synonym : MGMT$DB_TABLESPACES ...
Dropping synonym : MGMT$DB_SGA_ALL ...
Dropping synonym : MGMT$DB_SGA ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS_ALL ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS ...
Dropping synonym : MGMT$DB_REDOLOGS_ALL ...
Dropping synonym : MGMT$DB_REDOLOGS ...
Dropping synonym : MGMT$DB_OPTIONS_ALL ...
Dropping synonym : MGMT$DB_OPTIONS ...
Dropping synonym : MGMT$DB_LICENSE_ALL ...
Dropping synonym : MGMT$DB_LICENSE ...
Dropping synonym : MGMT$DB_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$DB_INIT_PARAMS ...
Dropping synonym : MGMT$DB_FEATUREUSAGE ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO_ALL ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO ...
Dropping synonym : MGMT$DB_DATAFILES_ALL ...
Dropping synonym : MGMT$DB_DATAFILES ...
Dropping synonym : MGMT$DB_CONTROLFILES_ALL ...
Dropping synonym : MGMT$DB_CONTROLFILES ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_STANDARD ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_RULE ...
Dropping synonym : MGMT$CS_CONFIG_STANDARDS ...
Dropping synonym : MGMT$CSM_WATCHLIST ...
Dropping synonym : MGMT$CSM_URL_HOURLY ...
Dropping synonym : MGMT$CSM_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_URL_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_DAILY ...
Dropping synonym : MGMT$CSM_REGION_HOURLY ...
Dropping synonym : MGMT$CSM_REGION_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_REGION_DIST_DAILY ...
Dropping synonym : MGMT$CSM_REGION_DAILY ...
Dropping synonym : MGMT$CSM_REGION ...
Dropping synonym : MGMT$CSM_MT_URL_HOURLY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_URL_DAILY ...
Dropping synonym : MGMT$CSM_MT_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_MT_IP_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_IP_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_HOURLY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_DAILY ...
Dropping synonym : MGMT$CSM_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_IP_HOURLY ...
Dropping synonym : MGMT$CSM_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_IP_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_HOURLY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_DAILY ...
Dropping synonym : MGMT$CSA_HOST_SW ...
Dropping synonym : MGMT$CSA_HOST_RULES ...
Dropping synonym : MGMT$CSA_HOST_OS_PROPERTIES ...
Dropping synonym : MGMT$CSA_HOST_OS_FILESYSTEMS ...
Dropping synonym : MGMT$CSA_HOST_OS_COMPONENTS ...
Dropping synonym : MGMT$CSA_HOST_NICS ...
Dropping synonym : MGMT$CSA_HOST_IOCARDS ...
Dropping synonym : MGMT$CSA_HOST_CUSTOM ...
Dropping synonym : MGMT$CSA_HOST_CPUS ...
Dropping synonym : MGMT$CSA_HOST_COOKIES ...
Dropping synonym : MGMT$CSA_FAILED ...
Dropping synonym : MGMT$CSA_COLLECTIONS ...
Dropping synonym : MGMT$CSA_CLIENT_RULE_VIOLS ...
Dropping synonym : MGMT$CSA_CLIENTS ...
Dropping synonym : MGMT$CPF_PATCH_INFO ...
Dropping synonym : MGMT$CPF_PATCH_DATA ...
Dropping synonym : MGMT$CPF_HOMES_INFO ...
Dropping synonym : MGMT$CPF_ADVISORY_INFO ...
Dropping synonym : MGMT$CLUSTER_INTERCONNECTS ...
Dropping synonym : MGMT$BLACKOUT_HISTORY ...
Dropping synonym : MGMT$BLACKOUTS ...
Dropping synonym : MGMT$AVAILABILITY_HISTORY ...
Dropping synonym : MGMT$AVAILABILITY_CURRENT ...
Dropping synonym : MGMT$AUDIT_LOG ...
Dropping synonym : MGMT$APPL_PATCH_AND_PATCHSET ...
Dropping synonym : MGMT$APPLIED_PATCHSETS ...
Dropping synonym : MGMT$APPLIED_PATCHES ...
Dropping synonym : MGMT$ALERT_NOTIF_LOG ...
Dropping synonym : MGMT$ALERT_HISTORY ...
Dropping synonym : MGMT$ALERT_CURRENT ...
Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
Dropping synonym : EMD_MNTR ...
Dropping synonym : ECM_UTIL ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files

PL/SQL procedure successfully completed.

SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='EM';

no rows selected




OLAP Catalog 제거

코드 블럭
titleOLAP Catalog 삭제 스크립트 수행 및 dba_recyclebin 정리
linenumberstrue
@?/olap/admin/catnoamd.sql
purge dba_recyclebin;


펼치기


서식 미적용
SQL> col COMP_NAME form a30
SQL> col STATUS form a10
SQL> col VERSION form a15
SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='AMD';

COMP_NAME                      STATUS     VERSION
------------------------------ ---------- ---------------
OLAP Catalog                   VALID      11.2.0.4.0

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/olap/admin/catnoamd.sql
SQL> Rem
SQL> Rem $Header: cwmlite/admin/catnoamd.sql /st_rdbms_11.2.0/3 2013/04/11 13:07:21 awesley Exp $
SQL> Rem
SQL> Rem catnoamd.sql
SQL> Rem
SQL> Rem Copyright (c) 2000, 2013, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catnoamd.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Permenantly remove metadata by deleting 'OLAPSYS' schema.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run as 'SYS'. Does not remove OLAP_DBA_TABLES tablespace.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    awesley     04/09/13 - lrg 8899446 remove awm from dba_registry
SQL> Rem    awesley     03/07/13 - lrg 8899446 drop all olapsys objects excep xml_load... objects
SQL> Rem    awesley     02/20/13 - bug 16362503 do not drop olapsys user
SQL> Rem    awesley     03/10/09 - add missing drops
SQL> Rem                         - add second '@' to factvdrp.sql, dimvwdrp.sql, olapodrp.sql
SQL> Rem    cdalessi    10/21/02 - cdalessi_txn104879
SQL> Rem    mrangwal    02/15/02 - Add olapodrp call
SQL> Rem    cdalessi    01/18/02 -
> Rem    cdalessi    01/14/02 -
> Rem    awesley     11/06/01 - Add cwm2 api
SQL> Rem    dthompso    07/24/00 - add connection.
SQL> Rem    dthompso    06/01/00 - Add new public synonyms for osa.
SQL> Rem    dthompso    04/27/00 - Initial Version
SQL> Rem    dthompso    01/00/00 - Created
SQL> Rem
SQL>
SQL> rem
SQL> rem drop cwm2
SQL> rem
SQL> @@cwm2drop
SQL> Rem
SQL> Rem $Header: cwm2drop.sql 18-nov-2003.13:10:14 awesley Exp $
SQL> Rem
SQL> Rem onedrop.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         cwm2drop.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Permenantly remove metadata by deleting 'OLAPSYS' schema.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run as 'SYS'. Does not remove OLAP_DBA_TABLES tablespace.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem          awesley       11/18/03 - add verify_access ,export ,delete
SQL> Rem          dbardwel 09/23/03 -
> Rem    Aziz.Mboya  09/02/03 - amboya_txn108623. Code cleanup for package name change from cwm2_olap_aw_access to dbms_aw_utilities.
SQL> Rem    dbardwel    03/14/02 - Got rid of CONNECT / AS SYSDBA
SQL> Rem    Aziz.Mboya  01/29/02 - changed package name from cwm2_olap_awconvert to cwm2_olap_aw_access
SQL> Rem    dbardwel    01/24/02 - Add DBA$ and ALL$ 9i2 union view synonym drops
SQL> Rem    cdalessi    01/14/02 - Add missing entries.
SQL> Rem    awesley     11/08/01 - awesley_txn100648
SQL> Rem    awesley     11/06/01 - create
SQL> REM        mstasiew         01/31/02  mstasiew_txn101768
SQL> Rem    dbardwel    02/02/02 - Removed cwm_olap_map from this script
SQL> Rem
SQL>
SQL> rem
SQL> rem cwm2 API Package Synonyms
SQL> rem
SQL>
SQL> drop public synonym cwm2_olap_exceptions
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_manager
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_utility
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_catalog
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_cube
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_dimension
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_dimension_attribute
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_hierarchy
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_level
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_level_attribute
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_measure
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_validate
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_verify_access
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_export
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_delete
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_table_map
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_pc_transform
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_security
  2  /

Synonym dropped.

SQL> drop public synonym dbms_aw_utilities
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_dim_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_dim_levels
  2  /

Synonym dropped.

SQL> drop public synonym cwm2_olap_classify
  2  /

Synonym dropped.

SQL>
SQL> rem
SQL> rem DBA_OLAP view synonyms
SQL> rem
SQL>
SQL> drop public synonym dba_olap2_cubes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_cube_measures
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_cube_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_cube_meas_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_hierarchies
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_levels
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_hier_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_attributes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_attr_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_level_attributes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_cube_measure_maps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_fact_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_level_key_col_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_join_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_level_attr_maps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_catalogs
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_entity_desc_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_catalog_entity_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_fact_table_gid
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_hier_custom_sort
  2  /

Synonym dropped.

SQL> drop public synonym dba_olapmr_facttblkeymaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olapmr_facttblfctmaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_listdims
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_hiers
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_hierdims
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_FactTblKeyMaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_rufcttblkyMaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_FactTblFctMaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_hierdim_keycol_map
  2  /

Synonym dropped.

SQL> drop public synonym dba_olapmr_dim_levels_keymaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_dim_levels_keymaps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_mr_measdimview
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap9i1_hier_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap9i2_hier_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap1_cubes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_entity_parameters
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap2_entity_ext_parms
  2  /

Synonym dropped.

SQL>
SQL> rem
SQL> rem ALL_OLAP view synonyms
SQL> rem
SQL>
SQL> drop public synonym all_olap2_cubes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_cube_measures
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_cube_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_cube_meas_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_hierarchies
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_levels
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_hier_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_attr_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_level_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_cube_measure_maps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_fact_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_level_key_col_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_join_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_level_attr_maps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_catalogs
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_entity_desc_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_catalog_entity_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_fact_table_gid
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_hier_custom_sort
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_facttblkeymaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_facttblfctmaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_listdims
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_hiers
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_hierdims
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_FactTblKeyMaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_rufcttblkyMaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_FactTblFctMaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_hierdim_keycol_map
  2  /

Synonym dropped.

SQL> drop public synonym all_olapmr_dim_levels_keymaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_dim_levels_keymaps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_mr_measdimview
  2  /

Synonym dropped.

SQL> drop public synonym all_olap9i1_hier_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym all_olap9i2_hier_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym all_olap1_cubes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_entity_parameters
  2  /

Synonym dropped.

SQL> drop public synonym all_olap2_entity_ext_parms
  2  /

Synonym dropped.

SQL>
SQL>
SQL> rem
SQL> rem API Package Synonyms
SQL> rem
SQL> drop public synonym cwm_classify
  2  /

Synonym dropped.

SQL> drop public synonym cwm_exceptions
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_cube
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_measure
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_dim_attribute
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_dimension
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_hierarchy
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_level
  2  /

Synonym dropped.

SQL> drop public synonym cwm_olap_level_attribute
  2  /

Synonym dropped.

SQL> drop public synonym cwm_utility
  2  /

Synonym dropped.

SQL>
SQL> rem
SQL> rem DBA_OLAP view synonyms
SQL> rem
SQL> drop public synonym dba_olap_catalog_entity_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_catalogs
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_columns
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_cube_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_cube_measure_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_cube_measure_maps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_cube_measures
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_cubes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_descriptor_types
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_descriptors
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_attr_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_attributes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_hier_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_hierarchies
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_level_attr_maps
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_level_attributes
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dim_levels
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_entity_desc_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_fact_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_foreign_keys
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_function_arguments
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_function_parameters
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_function_usages
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_functions
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_join_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_keys
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_level_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym dba_olap_tables
  2  /

Synonym dropped.

SQL>
SQL> rem
SQL> rem ALL_OLAP view synonyms
SQL> rem
SQL> drop public synonym all_olap_catalog_entity_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_catalogs
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_columns
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_cube_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_cube_measure_dim_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_cube_measure_maps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_cube_measures
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_cubes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_descriptor_types
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_descriptors
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_attr_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_hier_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_hierarchies
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_level_attr_maps
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_level_attributes
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dim_levels
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_dimensions
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_entity_desc_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_fact_level_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_foreign_keys
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_function_arguments
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_function_parameters
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_function_usages
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_functions
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_join_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_keys
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_level_key_column_uses
  2  /

Synonym dropped.

SQL> drop public synonym all_olap_tables
  2  /

Synonym dropped.

SQL>
SQL>
SQL> drop package SYS.CWM2_OLAP_INSTALLER
  2  /

Package dropped.

SQL>
SQL> drop public synonym ALL_AW_CUBE_AGG_LEVELS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_CUBE_AGG_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_CUBE_AGG_PLANS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_CUBE_ENABLED_HIERCOMBO
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_CUBE_ENABLED_VIEWS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_DIM_ENABLED_VIEWS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_CUBES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_CUBE_DIMS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_CUBE_FILTERS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_CUBE_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_CUBE_PARMS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_DIMENSIONS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_DIM_FILTERS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_AW_LOAD_DIM_PARMS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_LOAD_CUBE_SEGWIDTH
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AGGREGATION_USES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AWVIEWCOLS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AWVIEWS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_ATTRIBUTES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CATALOGS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CATALOG_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_AGG_LVL
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_AGG_MEAS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_AGG_OP
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_AGG_SPECS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_DIM_USES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_CUBE_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_DIMENSIONS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_DIM_HIER_LVL_ORD
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_DIM_LEVELS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_PHYS_OBJ
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_AW_PHYS_OBJ_PROP
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_MV_CUBE_AGG_LEVELS
  2  /

Synonym dropped.

SQL> drop public synonym ALL_OLAP2_MV_CUBE_AGG_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_AW_AWUTIL
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_METADATA_REFRESH
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_MR_CHECK_PRIVS
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_MR_SECURITY_INIT
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_MR_SESSION_POP
  2  /

Synonym dropped.

SQL> drop public synonym CWM2_OLAP_OLAPAPI_ENABLE
  2  /

Synonym dropped.

SQL> drop public synonym DBA_OLAP2_AGGREGATION_USES
  2  /

Synonym dropped.

SQL> drop public synonym DBA_OLAP2_AWVIEWCOLS
  2  /

Synonym dropped.

SQL> drop public synonym DBA_OLAP2_AWVIEWS
  2  /

Synonym dropped.

SQL> drop public synonym DBMS_AWM
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP1_FACTTBLFCTMAPS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP1_FACTTBLKEYMAPS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP1_POP_CUBES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP1_POP_DIMENSIONS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AGGREGATION_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AWS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AWVIEWCOLS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AWVIEWS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_ATTRIBUTES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_AGG_LVL
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_AGG_MEAS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_AGG_OP
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_AGG_SPECS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_DIM_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_CUBE_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_DIMENSIONS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_DIM_HIER_LVL_ORD
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_DIM_LEVELS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_MAP_ATTR_USE
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_MAP_DIM_USE
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_MAP_MEAS_USE
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_PHYS_OBJ
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_AW_PHYS_OBJ_PROP
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_CATALOGS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_CATALOG_ENTITY_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_CUBE_MEASURES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_DESCRIPTORS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_DIM_ATTRIBUTES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_DIM_HIERS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_DIM_HIER_LEVEL_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_DIM_LEVEL_ATTR_MAPS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_ENTITY_DESC_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_ENTITY_EXT_PARMS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_ENTITY_PARAMETERS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_FACTTBLFCTMAPS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_FACTTBLKEYMAPS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_HIERDIMS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_HIERDIMS_CC
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_HIERDIM_KEYCOL_MAP
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_HIER_CUSTOM_SORT
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_JOIN_KEY_COL_USES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_LISTDIMS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_LISTDIMS_CC
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_POP_CUBES
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP2_POP_DIMENSIONS
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP_CWM1_AGGOP
  2  /

Synonym dropped.

SQL> drop public synonym MRV_OLAP_CWM1_AGGORD
  2  /

Synonym dropped.

SQL> drop public synonym OLAP_SYS_AW_ACCESS_CUBE_VIEW
  2  /

Synonym dropped.

SQL> drop public synonym OLAP_SYS_AW_ACCESS_DIM_VIEW
  2  /

Synonym dropped.

SQL> drop public synonym OLAP_SYS_AW_ENABLE_ACCESS_VIEW
  2  /

Synonym dropped.

SQL>
SQL>
SQL> @@factvdrp.sql
SQL> drop public synonym OlapFactView;

Synonym dropped.

SQL> @@dimvwdrp.sql
SQL> drop public synonym OlapDimView;

Synonym dropped.

SQL> @@olapodrp.sql
SQL> drop public synonym DBMS_ODM;

Synonym dropped.

SQL>
SQL> rem
SQL> rem drop olapsys user
SQL> rem
SQL> rem drop user olapsys cascade
SQL> rem /
SQL>
SQL> rem drop olapsys objects in the following order
SQL> rem drop nested aw objects defined in cwm2awao.sql
SQL> drop view olapsys.olap_sys_aw_access_dim_view
  2  /

View dropped.

SQL> drop view olapsys.olap_sys_aw_access_cube_view
  2  /

View dropped.

SQL> drop type olapsys.olap_sys_aw_access_tbl
  2  /

Type dropped.

SQL> drop type olapsys.olap_sys_aw_access_obj
  2  /

Type dropped.

SQL> drop view olapsys.olap_sys_aw_enable_access_view
  2  /

View dropped.

SQL> drop type olapsys.olap_sys_aw_enable_access_tbl
  2  /

Type dropped.

SQL> drop type olapsys.olap_sys_aw_enable_access_obj
  2  /

Type dropped.

SQL>
SQL> rem drop nested aw objects defined in cwm2awmd.sql
SQL> drop type olapsys.all_olap2_aw_metadata_t
  2  /

Type dropped.

SQL> drop type olapsys.all_olap2_aw_metadata_o
  2  /

Type dropped.

SQL>
SQL> declare
  2  begin
  3    -- drop tables and indexes user for referential integrity constraints
  4    for c_object in (select owner, object_name, object_type
  5                     from dba_objects
  6                     where owner = 'OLAPSYS'
  7                     and object_name not in ('XML_LOAD_RECORDS','XML_LOAD_LOG')
  8                     and object_type = 'TABLE'
  9                     order by object_type, owner, object_name
 10                     )
 11    loop
 12      -- dbms_output.put_line(
 13      --   'TABLE OLAPSYS'.' || c_object.object_name);
 14      execute immediate
 15        'drop TABLE OLAPSYS.' || c_object.object_name || ' cascade constraints';
 16    end loop;
 17
 18    -- drop remaining objects, TABLE and INDEX are dropped @dba_objects
 19    for c_object in (select owner, object_name, object_type
 20                     from dba_objects
 21                     where owner = 'OLAPSYS'
 22                     and object_name != 'XML_LOADID_SEQUENCE'
 23                     and object_Type not in ('TABLE', 'INDEX', 'PACKAGE BODY')
 24                     order by object_type, owner, object_name
 25                     )
 26    loop
 27      -- dbms_output.put_line(
 28      --   c_object.object_type || ' OLAPSYS'.' || c_object.object_name);
 29      execute immediate
 30        'drop ' || c_object.object_type || ' OLAPSYS.' || c_object.object_name;
 31    end loop;
 32  end;
 33  /

PL/SQL procedure successfully completed.

SQL>
SQL> rem
SQL> rem drop OLAP_DBA role
SQL> rem
SQL> drop role OLAP_DBA
  2  /

Role dropped.

SQL>
SQL> execute sys.dbms_registry.removed('AMD');

PL/SQL procedure successfully completed.

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

1 row deleted.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select COMP_NAME, STATUS from DBA_REGISTRY where COMP_ID = 'AMD';

no rows selected




Oracle Application Express 삭제

코드 블럭
languagebash
titleAPEX 삭제 스크립트 수행 및 invalid 객체 삭제
linenumberstrue
@?/apex/apxremov.sql
drop package htmldb_system;
drop public synonym htmldb_system;


펼치기


서식 미적용
SQL> col COMP_NAME form a30
SQL> col STATUS form a10
SQL> col VERSION form a15
SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='APEX';

COMP_NAME                      STATUS     VERSION
------------------------------ ---------- ---------------
Oracle Application Express     VALID      3.2.1.00.12

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/apex/apxremov.sql
SQL> Rem  Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         apxremov.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Removes Application Express
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Assumes the SYS user is connected.
SQL> Rem
SQL> Rem    REQUIRENTS
SQL> Rem         Application Express
SQL> Rem
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YYYY)
SQL> Rem         jstraub        08/14/2006 - Created
SQL> Rem         jkallman       09/29/2006 - Adjusted APPUN to FLOWS_030000, add FLOWS_020200 to upgrade query
SQL> Rem         jstraub        02/14/2007 - Added call to wwv_flow_upgrade.drop_public_synonyms, and dropping APEX_PUBLIC_USER if not upgraded
SQL> Rem         jkallman       08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
SQL> Rem         jstraub        08/29/2007 - Altered to completely remove Application Express per bug 6086891
SQL> Rem         jstraub        11/27/2007 - Added removing APEX_ADMINISTRATOR_ROLE if not an upgrade installation
SQL> Rem         jkallman       09/09/2008 - Change FLOWS_030100 references to APEX_030200
SQL> Rem         jstraub        12/19/2008 - Added removal of SYS owned objects specific to Application Express
SQL> Rem         jstraub        01/21/2009 - Moved XDB cleanup to block that only executes if not an upgrade from prior release
SQL> Rem         jstraub        01/30/2009 - Added dropping WWV_FLOW_KEY and WWV_FLOW_VAL_LIB
SQL> Rem
SQL>
SQL> prompt ...Removing Application Express
...Removing Application Express
SQL>
SQL> define UPGRADE = '1'
SQL> define APPUN = 'APEX_030200'
SQL> define IMGPR = '/i/'
SQL>
SQL> alter session set current_schema = &APPUN;
old   1: alter session set current_schema = &APPUN
new   1: alter session set current_schema = APEX_030200

Session altered.

SQL>
SQL> begin
  2      wwv_flow_upgrade.drop_public_synonyms;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> column foo2 new_val UPGRADE
SQL>
SQL> select '2' foo2 from dba_users where username in ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100');

no rows selected

SQL>
SQL> begin
  2      if '&UPGRADE' = '1' then
  3          wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
  4      end if;
  5  end;
  6  /
old   2:     if '&UPGRADE' = '1' then
new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set current_schema = SYS;

Session altered.

SQL>
SQL> set serveroutput on
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SQL> begin
  2      if dbms_registry.status('APEX') is null then
  3          dbms_output.put_line(chr(13)||chr(10));
  4          dbms_output.put_line('Error:');
  5          dbms_output.put_line('You can only use this script to remove Application Express');
  6          dbms_output.put_line(chr(13)||chr(10)||chr(13)||chr(10));
  7          execute immediate('invalid sql stmnt to force exit');
  8      end if;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> -- Remove FLOWS SCHEMA
SQL> drop user &APPUN cascade;
old   1: drop user &APPUN cascade
new   1: drop user APEX_030200 cascade

User dropped.

SQL>
SQL> -- Remove FLOWS_FILES and APEX_PUBLIC_USER SCHEMA if no other versions exist
SQL>
SQL> begin
  2      if '&UPGRADE' = '1' then
  3          execute immediate 'drop user FLOWS_FILES cascade';
  4          execute immediate 'drop user APEX_PUBLIC_USER cascade';
  5          execute immediate 'drop role APEX_ADMINISTRATOR_ROLE';
  6      end if;
  7  end;
  8  /
old   2:     if '&UPGRADE' = '1' then
new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

SQL>
SQL> -- XDB Cleanup
SQL> declare
  2      cfg XMLType;
  3  begin
  4
  5      if '&UPGRADE' = '1' then
  6
  7          if dbms_xdb.existsresource('/i/') then
  8            dbms_xdb.deleteresource('/i/',4);
  9          end if;
 10
 11          if dbms_xdb.existsresource('/images/') then
 12            dbms_xdb.deleteresource('/images/',4);
 13          end if;
 14
 15          dbms_epg.drop_dad('APEX');
 16
 17          cfg := dbms_xdb.cfg_get();
 18
 19          if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping/servlet-name[text()="PublishedContentServlet"]') = 1 then
 20              cfg := cfg.deleteXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping/servlet-name[text()="PublishedContentServlet"]/..');
 21          end if;
 22
 23          if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet/servlet-name[text()="PublishedContentServlet"]') = 1 then
 24              cfg := cfg.deleteXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet/servlet-name[text()="PublishedContentServlet"]/..');
 25          end if;
 26
 27          dbms_xdb.cfg_update(cfg);
 28          commit;
 29          dbms_xdb.cfg_refresh;
 30
 31      end if;
 32
 33  end;
 34  /
old   5:     if '&UPGRADE' = '1' then
new   5:     if '1' = '1' then

PL/SQL procedure successfully completed.

SQL>
SQL> -- Remove SYS owned objects
SQL>
SQL> begin
  2      if '&UPGRADE' = '1' then
  3          execute immediate 'drop procedure validate_apex';
  4          execute immediate 'drop package WWV_FLOW_VAL';
  5          execute immediate 'drop package WWV_DBMS_SQL';
  6          execute immediate 'drop package WWV_FLOW_KEY';
  7          execute immediate 'drop library WWV_FLOW_VAL_LIB';
  8      end if;
  9  end;
 10  /
old   2:     if '&UPGRADE' = '1' then
new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

SQL>
SQL> prompt ...Application Express Removed
...Application Express Removed

SQL> drop package htmldb_system;

Package dropped.

SQL> drop public synonym htmldb_system;

Synonym dropped.

SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='APEX';

no rows selected




데이터 파일 크기 조정

코드 블럭
titleSYSTEM, SYSAUX, TEMP 파일 크기 변경
linenumberstrue
alter database datafile '+DATA/racdb/datafile/<SYSTEM 데이터 파일명>' resize 2G;
alter database datafile '+DATA/racdb/datafile/<SYSTEM 데이터 파일명>' autoextend on maxsize 10G;
alter database datafile '+DATA/racdb/datafile/<SYSAUX 데이터 파일명>' resize 2G;
alter database datafile '+DATA/racdb/datafile/<SYSAUX 데이터 파일명>' autoextend on maxsize 10G;
alter database tempfile '+DATA/racdb/tempfile/<TEMP 파일명>' resize 500M;
alter database tempfile '+DATA/racdb/tempfile/<TEMP 파일명>' autoextend on maxsize 2G;


펼치기


서식 미적용
SQL> set lines 200
SQL> col FILE_NAME form a50
SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSTEM';

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/datafile/system.256.1037878345               1000 YES             2048

SQL> alter database datafile '+DATA/racdb/datafile/system.256.1037878345' resize 2G;

Database altered.

SQL> alter database datafile '+DATA/racdb/datafile/system.256.1037878345' autoextend on maxsize 10G;

Database altered.

SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSTEM';

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/datafile/system.256.1037878345               2048 YES            10240

SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSAUX';

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/datafile/sysaux.257.1037878355               1000 YES             2048

SQL> alter database datafile '+DATA/racdb/datafile/sysaux.257.1037878355' autoextend on maxsize 10G;

Database altered.

SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSAUX';

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/datafile/sysaux.257.1037878355               1000 YES            10240

SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_TEMP_FILES;

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/tempfile/temp.265.1037878839                  100 YES             1024

SQL> alter database tempfile '+DATA/racdb/tempfile/temp.265.1037878839' resize 500M;

Database altered.

SQL> alter database tempfile '+DATA/racdb/tempfile/temp.265.1037878839' autoextend on maxsize 2G;

Database altered.

SQL> select FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_TEMP_FILES;

FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
-------------------------------------------------- ---------- --------- ----------
+DATA/racdb/tempfile/temp.265.1037878839                  500 YES             2048

SQL> col TABLESPACE_NAME form a15

SQL> select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB from DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME                                                  MB AUTOEXTEN     MAX_MB
--------------- -------------------------------------------------- ---------- --------- ----------
SYSTEM          +DATA/racdb/datafile/system.256.1037878345               2048 YES            10240
SYSAUX          +DATA/racdb/datafile/sysaux.257.1037878355               1000 YES            10240
UNDOTBS1        +DATA/racdb/datafile/undotbs1.258.1037878363             1000 YES             2048
UNDOTBS2        +DATA/racdb/datafile/undotbs2.259.1037878371             1000 YES             2048
USERS           +DATA/racdb/datafile/users.260.1037878377                 500 NO                 0




Expression Filter & Rules Manager 삭제 (옵션)

코드 블럭
titleExpression Filter & Rules Manager 삭제 스크립트 수행
linenumberstrue
@?/rdbms/admin/catnoexf.sql


펼치기


서식 미적용
SQL> col COMP_NAME form a30
SQL> col STATUS form a10
SQL> col VERSION form a15
SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID in ('EXF','RUL');

COMP_NAME                      STATUS     VERSION
------------------------------ ---------- ---------------
Oracle Expression Filter       VALID      11.2.0.4.0
Oracle Rules Manager           VALID      11.2.0.4.0

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/rdbms/admin/catnoexf.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catnoexf.sql /st_rdbms_11.2.0/1 2013/02/12 13:38:21 sdas Exp $
SQL> Rem
SQL> Rem catnoexf.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2013, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catnoexf.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    jerrede     01/02/13 - Add the Removal of Rules Manager
SQL> Rem    ayalaman    02/25/08 - cleanup public synonyms
SQL> Rem    ayalaman    04/19/04 - cleanup export dependeny actions
SQL> Rem    ayalaman    11/19/02 - registry entries
SQL> Rem    ayalaman    09/26/02 - ayalaman_expression_filter_support
SQL> Rem    ayalaman    09/06/02 -
> Rem    ayalaman    09/06/02 - Created
SQL> Rem
SQL>
SQL> REM
SQL> REM Drop Rules Manager if present.  Rules Manager must be
SQL> REM done first before we drop Expression Filter.
SQL> REM
SQL> COLUMN     :rul_name NEW_VALUE rul_file NOPRINT;
SQL> VARIABLE rul_name VARCHAR2(30)
SQL> BEGIN
  2
  3     IF (dbms_registry.is_loaded('RUL') IS NOT NULL) THEN
  4         :rul_name := '@catnorul.sql';                -- RUL exists in DB
  5     ELSE
  6         :rul_name := dbms_registry.nothing_script;   -- No RUL
  7     END IF;
  8
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT :rul_name FROM DUAL;




SQL> @&rul_file
SQL> Rem
SQL> Rem $Header: rdbms/admin/catnorul.sql /st_rdbms_11.2.0/2 2013/02/12 13:38:21 sdas Exp $
SQL> Rem
SQL> Rem catnorul.sql
SQL> Rem
SQL> Rem Copyright (c) 2004, 2013, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catnorul.sql - Uninstall script for Rule Manager
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This script un-installs the Rule Manager component
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         See Documentation
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    sdas        01/21/13 - XbranchMerge sdas_bug-16038193 from
SQL> Rem                           st_rdbms_12.1.0.1
SQL> Rem    jerrede     01/14/13 - XbranchMerge jerrede_bug-16097914 from
SQL> Rem                           st_rdbms_12.1.0.1
SQL> Rem    sdas        01/11/13 - drop in catnoexf: type RLM$ROWIDTAB
SQL> Rem    dvoss       01/11/13 - disable database guard
SQL> Rem    jerrede     01/03/13 - Bug#16025279 Add Error Checking
SQL> Rem    ayalaman    05/14/07 - drop irrelev java classes
SQL> Rem    ayalaman    02/16/05 - drop the truncate system trigger with uninstall
SQL> Rem    ayalaman    07/16/04 - negation with delay dictionary table
SQL> Rem    ayalaman    06/29/04 - rules with negation and deadline
SQL> Rem    ayalaman    04/23/04 - ayalaman_rule_manager_support
SQL> Rem    ayalaman    04/02/04 - Created
SQL> Rem
SQL>
SQL>
SQL> REM
SQL> REM Drop the Rule Manager specific objects from the EXFSYS schema
SQL> REM
SQL> EXECUTE dbms_registry.removing('RUL');

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER SESSION DISABLE GUARD;

Session altered.

SQL>
SQL> begin
  2    dbms_scheduler.drop_job(job_name => 'EXFSYS.RLM$EVTCLEANUP', force=> true);
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -27475 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_scheduler.drop_job(job_name => 'EXFSYS.RLM$SCHDNEGACTION', force=> true);EXCEPTION
  3    WHEN OTHERS THEN IF SQLCODE = -27475 THEN NULL; ELSE RAISE; END IF;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_dr';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_ir';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_utl';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.rlm$timecentral';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_depasexp';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_exp';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlm4j_dictmaint';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.dbms_rlmgr_irpk';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.ADM_RLMGR_SYSTRIG';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop package exfsys.DBMS_RLM4J_DICTMAINT_DR';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop procedure exfsys.RLM$CREATE_SCHEDULER_JOBS';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop procedure exfsys.RLM$PROCESSCOLLPREDS';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop procedure exfsys.RLM$PROCCLLGRPBY';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.RLM$UNIQUETAG';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.RLM$PARSEOBYCLS';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.RLM$OPTIMEQCLS';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.RLM$EQLLSRNONEG';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> --exec dbms_java.dropjava('-schema exfsys oracle.expfil.rlmgr.HavingExpParser.java');
SQL> --exec dbms_java.dropjava('-schema exfsys oracle.expfil.rlmgr.RLMAggregateRules.java');
SQL> --exec dbms_java.dropjava('-schema exfsys oracle.expfil.rlmgr.RLMPropertiesParser.java');
SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.RLM$COLLGRPBYSPEC';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$dmlevttrigs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$orderclsals';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm4j$ruleset';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm4j$evtstructs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.RLM4J$ATTRALIASES';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$schactlist';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$schacterrs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$equalspec';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$eventstruct';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$rulesetprivs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$validprivs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$primevttypemap';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$rsprimevents';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$errcode';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$jobqueue';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$ruleset';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$rulesetstcode';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop table exfsys.rlm$parsedcond';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.RLM$INCRRSLTMAPS';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.USER_RLM4J_ATTRIBUTE_ALIASES';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlm4j_evtst';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlm4j_ruleclasses';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_event_structs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_event_structs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_event_structs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_rule_classes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_rule_classes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_rule_classes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_privileges';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_privileges';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_rule_class_status';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_rule_class_status';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_rule_class_status';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_rule_class_opcodes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_comprcls_properties';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_comprcls_properties';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_comprcls_properties';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.user_rlmgr_action_errors';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.all_rlmgr_action_errors';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop view exfsys.adm_rlmgr_action_errors';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- drop public synonyms --
SQL> BEGIN
  2    execute immediate 'drop public synonym dbms_rlmgr';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym user_rlmgr_rule_classes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym all_rlmgr_rule_classes';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym user_rlmgr_privileges';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym rlm$eventids';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> --drop public synonym all_rlmgr_privileges;
SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym user_rlmgr_event_structs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym all_rlmgr_event_structs';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym user_rlmgr_rule_class_status';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym all_rlmgr_rule_class_status';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym user_rlmgr_comprcls_properties';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop public synonym all_rlmgr_comprcls_properties';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -1432 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> ---drop public synonym rlm$equalattr;
SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$keyval';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$dateval';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    --execute immediate 'drop type exfsys.rlm$rowidtab';
  3    NULL;
  4  EXCEPTION
  5    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$numval';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$equalattr';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$collpreds';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$collevents';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.rlm$collevent';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.RLM$APNUMBLST';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.RLM$APMULTVCL';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop type exfsys.RLM$APVARCLST';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.rlm$eqlchk';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    execute immediate 'drop function exfsys.rlm$seqchk';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4043 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    -- since this is a fresh install, delete any actions left behind --
  3    -- from past installations --
  4    delete from sys.expdepact$ where schema = 'EXFSYS'
  5       and package = 'DBMS_RLMGR_DEPASEXP';
  6
  7    delete from sys.exppkgact$ where package = 'DBMS_RLMGR_DEPASEXP'
  8      and schema = 'EXFSYS';
  9
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER SESSION SET CURRENT_SCHEMA = EXFSYS;

Session altered.

SQL>
SQL>
SQL> BEGIN
  2    dbms_xmlschema.deleteschema('http://xmlns.oracle.com/rlmgr/rclsprop.xsd');
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -31000 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    dbms_xmlschema.deleteschema('http://xmlns.oracle.com/rlmgr/rulecond.xsd');
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -31000 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- create the system trigger without rule manager maintenance --
SQL> -- drop the truncate trigger for rule class tables --
SQL> BEGIN
  2    execute immediate 'drop trigger exfsys.rlmgr_truncate_maint';
  3  EXCEPTION
  4    WHEN OTHERS THEN IF SQLCODE = -4080 THEN NULL; ELSE RAISE; END IF;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace package adm_rlmgr_systrig as
  2
  3    procedure pre_dropobj_maint (objowner VARCHAR2,
  4                                 objname  VARCHAR2,
  5                                 objtype  VARCHAR2);
  6
  7    --- @todo: system trigger to avoid truncate of the rules table --
  8    --- @todo: consider all other operations such as renaming tables, --
  9    ---        dropping columns from the table and altering the tables --
 10  end adm_rlmgr_systrig;
 11  /

Package created.

SQL>
SQL> create or replace package body adm_rlmgr_systrig as
  2
  3    /*************************************************************************/
  4    /*** PRE_DROPOBJ_MAINT : Pre drop maintenance for rule manager objects ***/
  5    /*************************************************************************/
  6    procedure pre_dropobj_maint (objowner VARCHAR2,
  7                                 objname  VARCHAR2,
  8                                 objtype  VARCHAR2) as
  9    begin
 10      return;
 11    end;
 12  end;
 13  /

Package body created.

SQL>
SQL> exec exfsys.adm_expfil_systrig.create_systrig_dropobj;

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER SESSION SET CURRENT_SCHEMA = SYS;

Session altered.

SQL>
SQL> EXECUTE dbms_registry.removed('RUL');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> REM
SQL> REM Drop the Expression Filter user with cascade option
SQL> REM
SQL> EXECUTE dbms_registry.removing('EXF');

PL/SQL procedure successfully completed.

SQL> drop user exfsys cascade;

User dropped.

SQL> drop package sys.exf$dbms_expfil_syspack;

Package dropped.

SQL> begin
  2    -- since this is a fresh install, delete any actions left behind --
  3    -- from past installations --
  4    delete from sys.expdepact$ where schema = 'EXFSYS'
  5      and package = 'DBMS_EXPFIL_DEPASEXP';
  6    delete from sys.exppkgact$ where package = 'DBMS_EXPFIL_DEPASEXP'
  7      and schema = 'EXFSYS';
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- drop public synonyms --
SQL> declare
  2    cursor cur1 is select synonym_name from all_synonyms where owner = 'PUBLIC' and table_owner = 'EXFSYS';
  3  begin
  4    for c1 in cur1 loop
  5       EXECUTE IMMEDIATE 'drop public synonym '||dbms_assert.enquote_name(c1.synonym_name, false);
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> execute sys.dbms_java.dropjava('-s rdbms/jlib/ExprFilter.jar');

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_registry.removed('EXF');
  3  exception
  4    when others then null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID in ('EXF','RUL');

no rows selected




Oracle Warehouse Builder 삭제 (옵션)

코드 블럭
languagebash
titleOracle Warehouse Builder 삭제 스크립트 수행
linenumberstrue
@?/owb/UnifiedRepos/clean_owbsys.sql


펼치기


서식 미적용
SQL> col COMP_NAME form a30
SQL> col STATUS form a10
SQL> col VERSION form a15
SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='OWB';

COMP_NAME                      STATUS     VERSION
------------------------------ ---------- ---------------
OWB                            VALID      11.2.0.4.0

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/owb/UnifiedRepos/clean_owbsys.sql
SQL> drop user owbsys cascade;

User dropped.

SQL> drop user owbsys_audit cascade;

User dropped.

SQL> drop role OWB_user;

Role dropped.

SQL> drop role OWB_DESIGNCENTER_view;

Role dropped.

SQL> drop role OWB$CLIENT;

Role dropped.

SQL> -- Bug Fix 6233292:
SQL> -- exit

SQL> select COMP_NAME, STATUS, VERSION from DBA_REGISTRY where COMP_ID='OWB';

no rows selected




업그레이드 사전 작업 체크 스크립트 재수행

코드 블럭
languagebash
title수동 사전 작업 수행 후 체크 스크립트 재수행
linenumberstrue
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
<19c ORACLE_HOME 경로>/jdk/bin/java -jar <19c ORACLE_HOME 경로>/rdbms/admin/preupgrade.jar TERMINAL TEXT


펼치기


서식 미적용
[racdb1:/home/oracle]> export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

[racdb1:/home/oracle]> $ORACLE_BASE/product/19.0.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/db_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-04-22T15:40:47

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  RACDB
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
     DB Patch Level:  PSU 11.2.0.4.200114
         Compatible:  11.2.0.0.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  INFORMATION ONLY
  ================
  3.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database RACDB
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 14 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  6.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database RACDB
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-04-22T15:40:48