Monday, September 12, 2011

Uninstalling components of an Oracle database

Here is a detailed guide about removing components of an Oracle database, but first I would like to state that I'm not a big fan of this (it would be better to create a database with just the components you need), and:

- Oracle in general discourages removing components of a database;
- this procedure is a bit different from Oracle documentation;
- you have to have a good understanding of Oracle Database software;
- you have to make a cold backup of your databases before trying this procedure;
- and it might be better to have a good reason for uninstalling components just in case you end with a corrupted database.

Having said that, the following procedure takes a 11g database with a lot of components and example schemas and ends with just the database engine. This is not intended to run as a script and it would be better to execute steps in the same order and to try it first in a test database.

Lets start with getting information about all the installed components:

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> select comp_id, comp_name, version, status from dba_registry;

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

18 rows selected.

Next, we can drop example schemas if not needed:

SQL> drop user OE cascade;

User dropped.

SQL> drop user SH cascade;

User dropped.

SQL> drop user BI cascade;

User dropped.

SQL> drop user HR cascade;

User dropped.

SQL> drop user SCOTT cascade;

User dropped.

To remove Oracle Application Express we can do this:

SQL> @?/apex/apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_030200

Session altered.


PL/SQL procedure successfully completed.


no rows selected

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.

old 1: drop user &APPUN cascade
new 1: drop user APEX_030200 cascade

User dropped.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.

old 5: if '&UPGRADE' = '1' then
new 5: if '1' = '1' then

PL/SQL procedure successfully completed.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then

PL/SQL procedure successfully completed.

...Application Express Removed
SQL> drop package HTMLDB_SYSTEM;

Package dropped.

SQL> drop PUBLIC SYNONYM HTMLDB_SYSTEM;

Synonym dropped.

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

no rows selected

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

no rows selected

As you can see, at the end of the procedure there are no invalid objects and the APEX component is not in the registry anymore; in general you have to look for invalid objects every time you remove a component and also check dba_registry to be sure the component was removed properly.

Next, to remove Oracle Workspace Manager component you can do the following steps:

SQL> @?/rdbms/admin/owmuinst.plb

Procedure created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Procedure dropped.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

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

no rows selected

Here again we look for invalid objects and check dba_registry; it would be a good practice to run utlrp before uninstalling components to check if the database has invalid objects and therefore not to blame the uninstall procedure if you end with some invalid objects.

Next we can remove Enterprise Manager this way:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> EXEC sysman.setEMUserContext('',5);

PL/SQL procedure successfully completed.

SQL> 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;
/

PL/SQL procedure successfully completed.

SQL> DROP USER mgmt_view CASCADE;

User dropped.

SQL> DROP USER sysman CASCADE;

User dropped.

SQL> DROP ROLE mgmt_user;

Role dropped.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

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

no rows selected

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

no rows selected

For some uninstalls it is necessary to restart the database, and in general it is a good idea to remove components while the database is not in use, restarting the database both at the beginning and at the end of the procedure.

This is the case for uninstalling Spatial:

SQL> set pagesize 0
SQL> set feed off
SQL> spool drop_spatial.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
drop public synonym "/106e9897_GeorGrayscaleEntry";
drop public synonym "/10862847_Intersection";
drop public synonym "/1138e59d_DBFReaderJGeomLogica";

...

drop public synonym "oracle/spatial/util/WKB";
drop public synonym "oracle/spatial/util/WKBasis";
drop public synonym "oracle/spatial/util/WKT";
SQL> spool off;
SQL> @drop_spatial.sql
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> commit;
SQL> set feed on
SQL> set pagesize 9999
SQL> drop user MDSYS cascade;
drop user MDSYS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 13:05:52 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 117444460 bytes
Database Buffers 71303168 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> drop user MDSYS cascade;

User dropped.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

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

no rows selected

In some cases the uninstall scripts does not remove public synonyms and misses other objects, therefore you have to remove them by means of making a script, removing object by object or running utlrp.

If you want to remove Oracle Multimedia you can follow these steps:

SQL> drop user ORDSYS cascade;

User dropped.

SQL> drop user ORDPLUGINS cascade;

User dropped.

SQL> drop user SI_INFORMTN_SCHEMA cascade;

User dropped.

SQL> set pagesize 0
SQL> set feed off
SQL> spool drop_ordim.sql
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);
drop public synonym "/6d99aea6_MediaLibLoadExceptio";
drop public synonym "ORD_DICOM_ADMIN";
drop public synonym "ORD_DICOM";

...

drop public synonym "SI_AVERAGECOLOR";
drop public synonym "SI_STILLIMAGE";
drop public synonym "SI_COLOR";
SQL> spool off;
SQL> @drop_ordim;
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> commit;
SQL> set feed on
SQL> set pagesize 9999
SQL> select owner, object_type, status, count(*) from dba_objects
where status <> 'VALID' group by owner, object_type, status;

no rows selected

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

no rows selected

These procedures are posted as transcripts in order to force people to read carefully and understand what's going on; if you are brave enough you can take just the SQL> sentences and assembly a script to speed up the uninstall process.

Next we will remove Oracle Rules Manager this way:

SQL> @?/rdbms/admin/catnorul.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

...

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.

SQL> drop FUNCTION EXFSYS.RLM$WLNCHK;

Function dropped.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:05:41

1 row selected.

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

...

DOC>#

OBJECTS WITH ERRORS
-------------------
0

1 row selected.

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

1 row selected.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

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

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='RUL';

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ---------------------------------
RUL Oracle Rules Manager 11.2.0.1.0 REMOVED

1 row selected.

Sometimes there are dependencies between components so the removal order is important; for example, if you remove first Oracle Expression Filter you will end with a lot of invalid objects of Oracle Rules Manager. Therefore, it is important to understand component dependencies first in order to know when and what to uninstall in order to avoid ending with a corrupted database.

Next we can remove Oracle Expression Filter following these steps:

SQL> @?/rdbms/admin/catnoexf.sql

PL/SQL procedure successfully completed.


User dropped.


Package dropped.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

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

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='EXF';

no rows selected

Some components are easier to uninstall and others require more manual work, and in general uninstalling components is easier and cleaner in an 11g database than in a 10g database; for example, if you want to remove just Oracle XML Database you can do it this way:

SQL> @?/rdbms/admin/catnoqm.sql

PL/SQL procedure successfully completed.


Index dropped.


Index dropped.

...


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Table dropped.

SQL> @?/rdbms/admin/catmeta.sql

...

Grant succeeded.


PL/SQL procedure successfully completed.





1 row selected.

SQL> @?/rdbms/admin/catpprvt.sql

...

37 /

Package body created.

SQL> show errors;
No errors.
SQL>
SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:28:42

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

...

DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> drop FUNCTION AWM_CREATEXDSFOLDER;

Function dropped.

SQL> drop PROCEDURE VALIDATE_ORDIM;

Procedure dropped.

SQL> drop package body HTMLDB_SYSTEM;

Package body dropped.

SQL> drop view ALL_XML_SCHEMAS;

View dropped.

SQL> drop view ALL_XML_SCHEMAS2;

View dropped.

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

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where comp_id='XDB';

no rows selected

Just in case you have a database with very little memory: in order to remove some components you need to have the minimum SGA memory recommended by Oracle (160 megabytes) or the procedure will fail, therefore it might be better to set SGA memory to be at least 160 megabytes.

Here are the steps to uninstall Oracle Text component:

SQL> @?/ctx/admin/catnoctx.sql

Session altered.


PL/SQL procedure successfully completed.

dropping all ctxsys objects...

...


dropping user ctxsys...

Role dropped.


User dropped.

SQL> drop procedure sys.validate_context;

Procedure dropped.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='CONTEXT';

no rows selected

Again, it would be better to restart the database and remove components where it's not in use, but you can skip this step and try to remove components if you're lucky enough; next we will remove OLAP Analytic Workspace this way:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 15:44:26 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 138415980 bytes
Database Buffers 50331648 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> @?/olap/admin/catnoaps.sql

Procedure created.


PL/SQL procedure successfully completed.


Procedure dropped.


1 row deleted.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:44:52

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

...

DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='APS';

no rows selected

You can get rid Oracle OLAP API off this way:

SQL> @?/olap/admin/olapidrp.plb

Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


Package dropped.


SQL> @?/olap/admin/catnoxoq.sql

Package dropped.


Package dropped.


Type dropped.

...

Package dropped.


PL/SQL procedure successfully completed.


1 row deleted.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-09-09 15:49:19

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

...

DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='XOQ';

no rows selected

And if you want to remove all the OLAP stuff, you can finish removing the OLAP Catalog component following these steps:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 15:52:00 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 138415980 bytes
Database Buffers 50331648 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.
SQL> @?/olap/admin/catnoamd.sql

Synonym dropped.


Synonym dropped.


Synonym dropped.

...

Synonym dropped.


User dropped.


Role dropped.

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column owner format a30
SQL> column object_type format a20
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry where COMP_ID='AMD';

no rows selected

Removing Java support from an Oracle database (not related to Java at operating system level) is more complex that previous procedures and some components are dependent from Java support, therefore this is the last component we're going to uninstall. It is accomplished this way:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 9 16:13:42 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 150998892 bytes
Database Buffers 37748736 bytes
Redo Buffers 6598656 bytes
Database mounted.
SQL> alter system set "_system_trig_enabled" = false scope=memory;

System altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> @?/rdbms/admin/catnojav.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Trigger dropped.


Trigger dropped.


Trigger dropped.


Trigger dropped.


Call completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Java dropped.


Java dropped.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> @?/xdk/admin/rmxml.sql

PL/SQL procedure successfully completed.


Procedure created.


PL/SQL procedure successfully completed.

...

PL/SQL procedure successfully completed.


Procedure dropped.


PL/SQL procedure successfully completed.

SQL> @?/javavm/install/rmjvm.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

...

PL/SQL procedure successfully completed.

SQL>
SQL> REM
SQL> REM end java object removal
SQL> REM
SQL>
SQL> drop table sys.JAVA$RMJVM$AUX3;

Table dropped.

SQL> drop table sys.JAVA$RMJVM$AUX2;

Table dropped.

SQL> drop table sys.JAVA$RMJVM$AUX;

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 24 13:38:49 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 155193196 bytes
Database Buffers 33554432 bytes
Redo Buffers 6598656 bytes
Database mounted.
Database opened.

At this point you have ended with a (hopefully working) database with just Oracle Database Catalog Views, Oracle Database Packages and Types and OWB components (you cannot uninstall OWB), but in order to fix some problems with imp/exp and datapump related to uninstalling Java you have to run the following scripts:

SQL> @?/rdbms/admin/catdph.sql

Grant succeeded.

BEGIN
*
ERROR at line 1:
ORA-20002: Unable to create table IMPDP_STATS: already exists
ORA-06512: at "SYS.DBMS_STATS", line 10202
ORA-06512: at line 2



Grant succeeded.

...

Package created.


Package body created.


PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/prvtcxml.plb

Library created.


Package body created.

SQL> @?/rdbms/admin/catdpb.sql
CREATE ROLE datapump_exp_full_database
*
ERROR at line 1:
ORA-01921: role name 'DATAPUMP_EXP_FULL_DATABASE' conflicts with another user
or role name


CREATE ROLE datapump_imp_full_database
*
ERROR at line 1:
ORA-01921: role name 'DATAPUMP_IMP_FULL_DATABASE' conflicts with another user
or role name

...

Grant succeeded.


Grant succeeded.


Grant succeeded.

SQL> @?/rdbms/admin/dbmspump.sql

Type created.


Type created.


Library created.


Type body created.


Type body created.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-08-24 13:47:21

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.

...

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

You can check the components left and invalid objects just to be sure, and would be a good idea to check the alert log too:

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column OBJECT_NAME format a40
SQL> select owner, object_type, object_name from dba_objects
where status <> 'VALID' order by owner, object_type, object_name;

no rows selected

SQL> select comp_id, comp_name, version, status from dba_registry;

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

6 rows selected.

More information:

Manually Install/De-Install Database Options in Oracle Database 10gR2
Transcript of removing components in a 10g database
Transcript of removing components in an 11g database

4 comments:

  1. interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, Synonyms

    ReplyDelete
    Replies
    1. Thanks, I try to share whatever it seems useful to me, I'm glad to know someone else appreciates it. Your blog looks interesting too.

      Delete
  2. Replies
    1. Good to know you found this useful, you're welcome.

      Delete