How to remove unused components in database ?

First check the installed components in database :

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;

1) Remove OLAP Catalog (OLAP AMD) 

SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_name like '%OLAP%' order by 1;

    $ORACLE_HOME/olap/admin/catnoamd.sql

2) 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';

3) Remove OLAP Analytic Workspace: 

shutdown immediate;

startup

@?/olap/admin/catnoaps.sql

@?/olap/admin/cwm2drop.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 = ‘INVAILD’;

               

4) Remove Enterprise Manager:

Option – 1

Remove the EM repository.

       - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 12.2.0.1.0 ORACLE_HOME into the source 11.2.0.3.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

$ORACLE_HOME/rdbms/admin/utlrp.sql

       SET SERVEROUTPUT ON;

       EXECUTE DBMS_PREUP.INVALID_OBJECTS; 

Option – 2:

                           [ OR ]

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;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

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

5) Remove Oracle Text:

spool /tmp/oracletxt-remove.log

set lines 200 pages 1000

select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id ='CONTEXT';

select * from ctxsys.ctx_version;

select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type order by 1;

select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

select * from dba_registry;

@?/ctx/admin/catnoctx.sql

drop procedure sys.validate_context;

select * from dba_registry;

select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';

spool off;

6) 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

7)  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;

set feed on

set pagesize 9999

shutdown immediate/abort

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

8) Remove Oracle Application Express:

@?/apex/apxremov.sql

drop package HTMLDB_SYSTEM;

drop PUBLIC SYNONYM HTMLDB_SYSTEM;

select owner, object_type, status, count(*) from dba_objects where status <> 'VALID' group by owner, object_type, status;

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

9) Remove OWB

OWB 11.2 Component Not Upgraded While Upgrading The Database From 11.2.0.x To 11.2.0.y (Doc ID 2027569.1)

Remove the OWB Component from the Database Registry 

1.     Start SQLPlus and connect with an account having SYSDBA privileges 

2.     Drop the OWBSYS schema by executing the script:

SQL> @<OH>/owb/UnifiedRepos/clean_owbsys.sql