Monday, April 7, 2014

How to remove unwanted components from an Oracle database

This article explains how to uninstall all optional components such as OWB, APEX, EM, OLAP, OWM, and others from an Oracle database. Oracle usually does not recommend uninstalling Oracle components manually unless you have a good reason.  Data Pump Export/Import is probably a safer alternative. Please take a full cold backup of your database before trying this approach.


OS: Red Hat Linux 5
Database version:  11.2.0.3.0


List installed options:
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID         COMP_NAME                                       STATUS
--------------- ---------------------------------------------    
OWB             OWB                                                VALID            
APEX            Oracle Application Express                         VALID
EM              Oracle Enterprise Manager                          VALID
AMD             OLAP Catalog                                       VALID          
SDO             Spatial                                            VALID            
ORDIM           Oracle Multimedia                                  VALID            
XDB             Oracle XML Database                                VALID
CONTEXT         Oracle Text                                        VALID            
EXF             Oracle Expression Filter                           VALID
RUL             Oracle Rules Manager                               VALID
OWM             Oracle Workspace Manager                           VALID
CATALOG         Oracle Database Catalog Views                      VALID
CATPROC         Oracle Database Packages and Types                 VALID
JAVAVM          JServer JAVA Virtual Machine                       VALID
XML             Oracle XDK                                         VALID            
CATJAVA         Oracle Database Java Packages                      VALID
APS             OLAP Analytic Workspace                            VALID
XOQ             Oracle OLAP API                                    VALID

18 rows selected.

Drop sample schemas if they exist:
drop user HR cascade;

drop user SCOTT cascade;

drop user OE cascade;

drop user SH cascade;

drop user BI cascade;

1. Remove Oracle Application Express:
?/apex/apxremov.sql
drop PUBLIC SYNONYM HTMLDB_SYSTEM;
drop PUBLIC SYNONYM HTMLDB_SYSTEM;

select comp_id, comp_name, version, status from dba_registry where comp_id='APEX';

2.  Remove Oracle Workspace Manager Components:
@?/rdbms/admin/owmuinst.plb

select comp_id, comp_name, version, status from dba_registry where comp_id='OWM';

3. Remove Enterprise Manager:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
EXEC sysman.setEMUserContext('',5);

DECLARE  
CURSOR c1 IS    
   SELECT owner, synonym_name name      
   FROM dba_synonyms     
   WHERE table_owner = 'SYSMAN';
BEGIN    
   FOR r1 IN c1    
       LOOP        
           IF r1.owner = 'PUBLIC' THEN            
           EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;        
           ELSE            
           EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;        
           END IF;    
       END LOOP;
   END;
/

DROP USER mgmt_view CASCADE;
DROP USER sysman CASCADE;
DROP ROLE mgmt_user;

select comp_id, comp_name, version, status from dba_registry where comp_id='EM';

4. Uninstalling Oracle Spatial:
shutdown immediate
startup

--Script
set pagesize 0
set feed off
spool drop_spatial.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
@drop_spatial.sql
commit;

shutdown immediate
startup

drop user MDSYS cascade;

select comp_id, comp_name, version, status from dba_registry where comp_id='SDO';

SELECT object_name,object_type,owner FROM dba_objects WHERE status = 'INVALID';

@$ORACLE_HOME/rdbms/admin/utlrp.sql

5. Remove Oracle Multimedia:
drop user ORDSYS cascade;
drop user ORDPLUGINS cascade;
drop user SI_INFORMTN_SCHEMA cascade;


--Script:
set pagesize 0
set feed off
spool drop_ordim.sql
select 'drop public synonym "'||b.object_name||'";' from dba_synonyms a, dba_objects b where b.status='INVALID'
and b.object_type='SYNONYM'
and b.owner='PUBLIC'
and a.synonym_name=b.object_name
and a.table_owner not in (select username from dba_users);
spool off;
@drop_ordim;
commit;

select comp_id, comp_name, version, status from dba_registry where comp_id='ORDIM';

SELECT object_name FROM dba_objects WHERE status = 'INVALID';
@$ORACLE_HOME/rdbms/admin/utlrp.sql

6.  Remove Oracle Rules Manager:
@?/rdbms/admin/catnorul.sql
drop FUNCTION EXFSYS.RLM$WLNCHK;
@?/rdbms/admin/utlrp

select comp_id, comp_name, version, status from dba_registry where comp_id='RUL';
SELECT object_name FROM dba_objects WHERE status = 'INVALID';

7.  Remove Oracle Expression Filter:
@?/rdbms/admin/catnoexf.sql

select comp_id, comp_name, version, status from dba_registry where comp_id='EXF';
SELECT object_name FROM dba_objects WHERE status = 'INVALID';

8. Remove Oracle XML Database:
@?/rdbms/admin/catnoqm.sql
@?/rdbms/admin/catmeta.sql
@?/rdbms/admin/catpprvt.sql
@?/rdbms/admin/utlrp

drop FUNCTION AWM_CREATEXDSFOLDER;
drop PROCEDURE VALIDATE_ORDIM;
drop package body HTMLDB_SYSTEM;
drop view ALL_XML_SCHEMAS;
drop view ALL_XML_SCHEMAS2;

SELECT object_name FROM dba_objects WHERE status = 'INVALID';

9. Remove Oracle Text:
@?/ctx/admin/catnoctx.sql
drop procedure sys.validate_context;

select comp_id, comp_name, version, status from dba_registry where COMP_ID='CONTEXT';
SELECT object_name FROM dba_objects WHERE status = 'INVALID';

10. Remove OLAP Analytic Workspace:
shutdown immediate;
startup

@?/olap/admin/catnoaps.sql
@?/rdbms/admin/utlrp.sql

select comp_id, comp_name, version, status from dba_registry where COMP_ID='APS';
SELECT object_name FROM dba_objects WHERE status = 'INVALID';

11. Remove Oracle OLAP API:
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoxoq.sql
@?/rdbms/admin/utlrp.sql

select comp_id, comp_name, version, status from dba_registry where COMP_ID='XOQ';
SELECT object_name FROM dba_objects WHERE status = 'INVALID';

12. Remove OLAP Catalog:
shutdown immediate;
startup
@?/olap/admin/catnoamd.sql

SELECT object_name FROM dba_objects WHERE status = 'INVALID';
select comp_id, comp_name, version, status from dba_registry where COMP_ID='AMD';

13. Remove Java Support from an Oracle Database:
shutdown immediate;
startup
exit

alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;

@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql

drop table sys.JAVA$RMJVM$AUX3;
drop table sys.JAVA$RMJVM$AUX2;
drop table sys.JAVA$RMJVM$AUX;

shutdown immediate;
startup

Cleanup:
@?/rdbms/admin/catdph.sql
@?/rdbms/admin/prvtcxml.plb
@?/rdbms/admin/catdpb.sql
@?/rdbms/admin/dbmspump.sql
@?/rdbms/admin/utlrp

List installed options:
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID         COMP_NAME                                     STATUS
--------------- --------------------------------------------------------
OWB             OWB                                                VALID             
CATALOG         Oracle Database Catalog Views                      VALID
CATPROC         Oracle Database Packages and Types                 VALID
JAVAVM          JServer JAVA Virtual Machine                       REMOVED
XML             Oracle XDK                                         REMOVED           
CATJAVA         Oracle Database Java Packages                      REMOVED

6 rows selected.


DataPump Export fails with PLS-201 identifier 'SYS.DBMS_CUBE_EXP' must be declared (Doc ID 1328829.1)
--OLAP objects remain existing in data dictionary while OLAP is not installed or was de-installed. Verify with:

sqlplus / as sysdba
SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';

-- Backup the table SYS.EXPPKGACT$ before deleting the row
CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$;

DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
COMMIT;


DataPump Export (EXPDP) Failed On Identifier SYS.DBMS_JVM_EXP_PERMS Must Be Declared (Doc ID 1095533.1)
--Take a full backup of the DB.
--Remove the DBMS_JVM_EXP_PERMS package:

sqlplus / as sysdba
delete from exppkgobj$ where package like '%JVM%';
commit;
shutdown immediate;
startup

--Retry the DataPump export to ensure the error is resolved.



I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.

Posted by at
Labels:

13 comments:

  1. AnonymousMarch 3, 2015 at 10:25 PM

    Hi,
    Is ORDDATA not related to Oracle Multimedia?

    ReplyDelete
  2. BorisMarch 4, 2015 at 5:07 AM

    Hello, I believe that ORDIM Oracle account is related to "Oracle Multimedia". Please follow this article in order if you are trying to remove unwanted components from an Oracle database.

    ReplyDelete
  3. AnonymousJuly 14, 2015 at 5:29 PM

    This is very good presentation. Please also publish for all the above components (manual) installation steps.

    ReplyDelete
  4. AnonymousNovember 6, 2015 at 6:34 PM

    Thanks Boris !!! I was able to use your process to clean-up Spatial before my Oracle 12c upgrade. Thanks !!!

    ReplyDelete
  5. AnonymousOctober 26, 2016 at 8:36 PM

    We have had an audit that recommended that we remove unused components like spatial, XDB etc. Is there an official stance preferably in writing from Oracle on removing components? Is it supported or not?

    ReplyDelete
    Replies
    1. Andrew LacyApril 3, 2017 at 5:12 AM

      Mike Dietrich from Oracle explains how to uninstall unwanted components. Note well that he does NOT go so far as to recommend this. However, you MUST remove any components that you have installed, but not licensed. I would recommend uninstalling all unnecessary components. I would NOT recommend uninstalling XDB.

      Delete
  6. UnknownMay 4, 2017 at 4:39 AM

    What about gsm* default user (Global Data Service)? If I remove this users in 12.2.0.1 data pump import fails with:

    ORA-39006: internal error
    ORA-39065: unexpected master process exception in DISPATCH
    ORA-00942: table or view does not exist
    ORA-06512: in "SYS.KUPU$UTILITIES_INT", line 1579
    ORA-06512: in "SYS.KUPM$MCP", line 2710

    ReplyDelete
  7. UnknownSeptember 12, 2017 at 9:57 AM

    Thank you for this useful post!

    You mentioned about uninstallation of OWB, but no provided instructions. Here it is:

    To uninstall OWB component, one should exec:

    @?/owb/UnifiedRepos/clean_owbmeta.sql
    @?/owb/UnifiedRepos/clean_owbsys.sql

    ReplyDelete
  8. UnknownFebruary 27, 2018 at 10:26 PM

    Do you have uninstall procedure for 9i (9.2.0.4) for the following products:

    > OLAP
    > SPATIAL
    > DATA MINING

    ReplyDelete
  9. AnonymousJuly 5, 2018 at 7:13 AM

    I removed components as mentioned above, but I get error in export of full database as below:

    ORA-31693: Table data object "ORDDATA"."ORDDCM_DOCS" failed to load/unload and is being skipped due to error:
    ORA-00942: table or view does not exist

    ORA-31693: Table data object "ORDDATA"."ORDDCM_CT_PRED_OPRD" failed to load/unload and is being skipped due to error:
    ORA-00942: table or view does not exist

    ORA-31693: Table data object "ORDDATA"."ORDDCM_MAPPING_DOCS" failed to load/unload and is being skipped due to error:
    ORA-00942: table or view does not exist

    Could you please help to resolve this?
    I performed on 11.2.0.4 oracle database.

    Thanks

    ReplyDelete
  10. Suresh DoolyNovember 14, 2018 at 10:56 AM

    how to disappear the below from dba_registry?

    COMP_ID COMP_NAME STATUS
    --------------- --------------------------------------------------------
    JAVAVM JServer JAVA Virtual Machine REMOVED
    XML Oracle XDK REMOVED

    ReplyDelete
    Replies
    1. vandudeSeptember 29, 2021 at 2:08 PM

      SQL> delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA');
      SQL> commit;

      Delete
  11. Bronco AustinFebruary 12, 2021 at 3:16 AM


    Iam so thrilled because of finding your alluring website here.Actually i was searching for Oracle DBA.Your blog is so astounding and informative too..Iam very happy to find such a creative blog. Iam also find another one by mistake while am searching the same topicOracle APEX.Thank you soo much..

    ReplyDelete

Subscribe to: Post Comments (Atom)