- Oracle en general no recomienda remover componentes de una base de datos;
- este procedimiento es un poco diferente de la documentación de Oracle;
- tienes que tener un buen entendimiento del software de Oracle Database;
- tienes que hacer un respaldo en frío de tu base de datos antes de probar este procedimiento;
- y será mejor tener una buena razón para desinstalar componentes sólo en caso de que termines con una base de datos corrupta.
Habiendo dicho esto, el siguiente procedimiento toma una base de datos 11g con muchos componentes y esquemas de ejemplo y termina con sólo el motor de la base de datos. Esto no está pensado para correrse como un script y será mejor ejecutar los pasos en el mismo orden e intentarlo primero en una base de datos de prueba.
Empecemos obteniendo información sobre todos los componentes instalados:
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.
A continuación podemos eliminar los esquemas de ejemplo si no se necesitan:
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.
Para remover Oracle Application Express podemos hacer esto:
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
Como puedes ver, al final del procedimiento no hay objetos inválidos y el componente APEX no está más en el registro; en general tienes que buscar objetos inválidos cada vez que eliminas un componente y también revisar dba_registry para estar seguro que el componente fue removido apropiadamente.
A continuación, para remover el componente Oracle Workspace Manager puedes seguir los siguientes pasos:
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
Aquí nuevamente buscamos objetos inválidos y revisamos dba_registry; sería una buena práctica correr utlrp antes de desinstalar componentes para revisar si la base de datos tiene objetos inválidos y por lo tanto no culpar al procedimientos de desinstalación si terminas con algunos objetos inválidos.
A continuación podemos eliminar Enterprise Manager de esta forma:
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
Para algunas desinstalaciones es necesario reiniciar la base de datos, y en general es una buena idea remover componentes mientras la base de datos no está en uso, reiniciando la base de datos al inicio y al final del procedimiento.
Este es el caso para desinstalar 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
En algunos casos los scripts de desinstalación no remueven sinónimos públicos e ignoran otros objetos, por lo tanto tienes que removerlos por medio de hacer un script, eliminando objeto por objeto o ejecutando utlrp.
Si quieres eliminar Oracle Multimedia puedes seguir los siguientes pasos:
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
Estos procedimientos están publicados como transcripciones para forzar a la gente a leer cuidadosamente y entender qué está pasando; si eres suficientemente valiente puedes tomar sólo las sentencias SQL> y crear un script para acelelar el proceso de desinstalación.
A continuación removeremos Oracle Rules Manager de esta manera:
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.
Algunas veces hay dependencias entre componentes por lo que el orden de eliminación es importante; por ejemplo, si remueves primero Oracle Expression Filter terminarás con muchos objetos inválidos de Oracle Rules Manager. Por lo tanto, es importante primero entender las dependencias entre componentes para saber cuándo y que desinstalar para evitar terminar con una base de datos corrupta.
A continuación podemos eliminar Oracle Expression Filter siguiendo estos pasos:
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
Algunos componentes son fáciles de desinstalar y otros requieren más trabajo manual, y en general desinstalar componentes es más fácil y limpio en una base de datos 11g que en una base de datos 10g; por ejemplo, si quieres remover sólo Oracle XML Database puedes hacerlo de esta forma:
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
Sólo en caso de que tengas una base de datos con poca memoria: para remover algunos componentes necesitas tener el mínimo de memoria SGA recomendada por Oracle (160 megabytes) o el procedimiento fallará, por lo tanto sería mejor ajustar la memoria SGA para que sea al menos de 160 megabytes.
Aquí están los pasos para desinstalar el componente Oracle Text:
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
Nuevamente, sería mejor reiniciar la base de datos y eliminar componentes cuando no está en uso, pero puedes saltarte este paso e intentar remover componentes si tienes sificiente suerte; a continuación removeremos OLAP Analytic Workspace de esta forma:
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
Puedes deshacerte de Oracle OLAP API de esta forma:
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
Y si quieres eliminar todo lo de OLAP, puedes terminar removiendo el componente OLAP Catalog siguiendo estos pasos:
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
Eliminar el soporte de Java de la base de datos Oracle (no relacionado con Java a nivel de sistema operativo) es más complejo que los procedimientos previos y algunos componentes son dependientes del soporte de Java, por lo tanto este es el último componente que vamos a desinstalar. Se lleva a cabo de esta forma:
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.
En este punto habrás terminado con una base de datos (ojalá funcionando) con sólo los componentes Oracle Database Catalog Views, Oracle Database Packages and Types y OWB (no puedes desinstalar OWB), pero para arreglar algunos problemas con imp/exp y datapump relacionados con la desinstalación de Java tienes que correr los siguientes 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.
Puedes revisar los componentes que quedaron y objetos inválidos sólo para estar seguro, y sería una buena idea revisar también el alerto log:
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.
Más información:
Manually Install/De-Install Database Options in Oracle Database 10gR2
Transcripción de eliminación de componentes en una base de datos 10g
Transcripción de eliminación de componentes en una base de datos 11g
No hay comentarios.:
Publicar un comentario