Oracle 12cR1 - Database Components Removal - Non CDB Database Components Removal on 12.1.0.2 version
Remove and Clean Up Components from Oracle Database 11.2 – 12.2 - 19c:
Reasons to Clean Up:
There may be many reasons to clean up the option set in an Oracle database. A precreated seed databases from DBCA has all options configured by default. Therefore I did recommend to use your own templates instead and create always a custom database tailored for your environment and needs:
-> Always create databases as CUSTOM databases
-> Use your own templates in DBCA to create databases
Amongst the most common reasons to remove an option or a component from an Oracle Database are license topics, faster upgrades and less potential for issues. I don’t justify any of these. But it’s a fact that an Oracle upgrade will run faster in dependency of the number of installed components.
Things to consider - Please Be Aware before doing component Clean Up:
Components removal on database is considered as major surgery on your database, before proceeindg with the components removal read the following topics carefully.
-> If you have no true reason to remove components on your databases, then ignore this clean up series topics
-> Remove the Oracle demo users (SCOTT, SH, OE etc).
-> Take a valid backup before – and ensure that you can restore and recover it
-> Test on a copy of your database first
-> Component dependencies exist – see: MOS Note: 2001512.1 for an example in Oracle Database 12.1.0.2
-> Workarounds are just recommendations – better workarounds may be available
-> Save your data – component removal may lead to loss of information if the component is in use
-> XDB is a mandatory component since Oracle Database 12c
-> Always spool into a file
-> Procedures work on Linux – on Windows some calls may vary
-> Finally check with Oracle Support if you have doubts and questions
Special requirements for CDBs:
If you plan to remove components from a Multitenant container database (CDB) then please consider the following recommendations:
All pluggable database must be opened:
catcon.pl must be used to execute removal scripts in many cases to clean up especially PDB$SEED.
You will find not much documented on component removal yet – therefore my blog posts are proposals only. Please double-check with Oracle Support.
Recompile in a Multitenant environment:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
You must set _exclude_seed_cdb_view to FALSE to see also objects belonging to the PDB$SEED
SQL> alter system set "_exclude_seed_cdb_view"=false scope=both;
Check_Components.sql
set line 200
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12
col CON_ID format 99
select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
Check_Invalid_Objects.sql
set line 200
set pages 1000
col owner format a12
col object_type format a12
col object_name format a30
col STATUS format a8
col CON_ID format 9
select con_id, owner, object_type, object_name, status from CDB_OBJECTS where status='INVALID' order by 1,2,3;
Component Clean Ups Step by Step:
-> APEX – Oracle Application Express Clean Up
-> OWM – Oracle Workspace Manager Clean Up
-> DV – Oracle Database Vault Clean Up
-> OLS – Oracle Label Security Clean Up
-> SDO – Oracle Spatial Data Option Clean Up
-> CONTEXT – Oracle Text Clean Up
-> ORDIM – Oracle Multi Media Clean Up
-> XOQ – Oracle OLAP API Clean Up
-> APS – Oracle OLAP Analytical Workspace Clean Up
-> AMD – Oracle OLAP Catalog Clean Up
-> OWB – Oracle Warehouse Builder Clean Up
-> EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
-> EM – Enterprise Manager Database Control Clean Up
-> JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
-> XDB – Oracle XML Database Clean Up - we can not remove its mandatory
APEX – Oracle Application Express Clean Up:
Oracle Database 11.2.0.4:
-------------------------
In Oracle Database 11.2.0.4 APEX gets installed by default as version 3.2.1.00.12. The removal procedure is documented in MOS Note:558340.1 – How to Uninstall Oracle HTML DB / Application Express from a 10G/11G Database.
SQL> @?/apex/apxremov.sql
SQL> drop package HTMLDB_SYSTEM;
That’s very simply and straight forward. And it works. The leftover package HTMLDB_SYSTEM is an artifact from older versions of APEX and should be removed (see MOS Note:1231863.1).
Oracle Database 12.1.0.2:
-------------------------
In Oracle Database 12.1.0.2 the APEX removal instructions are in the Application Express Installation Guide. The script for an APEX removal remains the same, regardless of executed in a non-CDB or CDB. APEX gets installed by default in version 4.2.5.00.08.
$ cd $ORACLE_HOME/apex
$ sqlplus / as sysdba
SQL> @apxremov.sql
SQL> drop PUBLIC SYNONYM APEX_SPATIAL;
SQL> drop PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOGIN;
Finally there may be two leftover synonyms APEX_SPATIAL and APEX_PKG_APP_INSTALL_LOGIN requiring clean up.
Oracle Database 12.2.0.1:
The default APEX version in Oracle Database 12.2.0.1 is APEX 5.0.4.00.12. Again, the removal scripts is the same for non-CDBs and CDBs. The documentation describes the process only for CDBs but fortunately there are no leftovers for non-CDBs.
NON-CDB Removal Steps:
$ cd $ORACLE_HOME/apex
$ sqlplus / as sysdba
SQL> @apxremov.sql
CDB Removal Steps:
In this case the script will utilized catcon.pl. Therefore I’m adding perl to the PATH variable.
$ cd $ORACLE_HOME/apex
$ PATH=$PATH:$ORACLE_HOME/perl/bin/perl
$ sqlplus / as sysdba
SQL> @apxremov.sql
Unfortunately there are leftovers after this process: the public synonym APEX_SPATIAL in all containers except CDB$ROOT and PDB$SEED will exist if Oracle Spatial is present. The removal must be done with catcon.pl.
Create a /home/oracle/clean_apex.sql script containing:
drop public synonym APEX_SPATIAL;
Execute /home/oracle/clean_apex.sql script with catcon.pl:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b clean_apex -d /home/oracle clean_apex.sql
Oracle Workspace Manager (OWM) Clean Up in Oracle Database 11.2-12.2:
Oracle Database 11.2.0.4:
The removal process for OWM is flawless:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/owmuinst.plb
Oracle Database 12.1.0.2
And the same process works in this release of the database:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/owmuinst.plb
Again, no leftovers and a fairly quick execution.
Oracle Database 12.2.0.1
NON-CDB
And again the same process for non-CDBs in Oracle Database 12.2.0.1:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/owmuinst.plb
No leftovers and a fairly quick execution.
CDB
In contrast the OWM removal from a CDB is a bit more complicated. If all is done in one pass then you’ll receive this error sequence:
ERROR at line 1:ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "WMSYS.LT", line 1
ORA-06512: at line 1
ORA-06512: at line 54ORA-06512: at line 54
ORA-06512: at line 91
Hence to complete the removal successfully and without errors it must be split up in two phases:
removal from all PDBs including the PDB$SEED:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b owm_clean_pdbs -d '''.''' owmuinst.plb
Removal from the CDB$ROOT
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b owm_clean_cdb -d '''.''' owmuinst.plb
Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2:
Oracle Database 11.2.0.4:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catnools.sql
Afterwards there will be 21 objects invalid.
SQL> col object_name format a30
SQL> col object_type format a15
SQL> set pages 2000
SQL> set line 200
SQL> select object_type, object_name from dba_objects where status='INVALID';
OBJECT_TYPE OBJECT_NAME
--------------- ------------------------------
VIEW DBA_AUDIT_TRAIL
SYNONYM DBA_AUDIT_TRAIL
VIEW USER_AUDIT_TRAIL
SYNONYM USER_AUDIT_TRAIL
VIEW DBA_AUDIT_SESSION
SYNONYM DBA_AUDIT_SESSION
VIEW USER_AUDIT_SESSION
SYNONYM USER_AUDIT_SESSION
VIEW DBA_AUDIT_STATEMENT
SYNONYM DBA_AUDIT_STATEMENT
VIEW USER_AUDIT_STATEMENT
SYNONYM USER_AUDIT_STATEMENT
VIEW DBA_AUDIT_OBJECT
SYNONYM DBA_AUDIT_OBJECT
VIEW USER_AUDIT_OBJECT
SYNONYM USER_AUDIT_OBJECT
VIEW DBA_AUDIT_EXISTS
SYNONYM DBA_AUDIT_EXISTS
VIEW DBA_COMMON_AUDIT_TRAIL
SYNONYM DBA_COMMON_AUDIT_TRAIL
PACKAGE BODY MGMT_DB_LL_METRICS
21 rows selected.
Recompile to clean up.
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql
Finally unlink the option once downtime is acceptable. This is optional but I recommend it.
$ chopt disable lbac
Oracle Database 12.1.0.2:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catnools.sql
Oracle Database 12.2.0.1:
EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
NON-CDB:
$ cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> @$ORACLE_HOME12102/rdbms/admin/catnools.sql
SQL> drop package LBAC_EXP;
SQL> drop package OLS_ENFORCEMENT;
CDB:
$ cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin
PDB$SEED:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnools_pdbs -d '''.''' catnools.sql
In addition remove OLS now from CDB$ROOT:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnools_cdb -d '''.''' catnools.sql
CON_ID OWNER OBJECT_TYPE OBJECT_NAME STATUS
------ ------------ ------------ ------------------------------ --------
1 SYS PACKAGE BODY LBAC_EXP INVALID
1 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
2 SYS PACKAGE BODY LBAC_EXP INVALID
2 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
3 SYS PACKAGE BODY LBAC_EXP INVALID
3 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
4 SYS PACKAGE BODY LBAC_EXP INVALID
4 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
$ vi clean_ols.sql
drop package LBAC_EXP;
drop package OLS_ENFORCEMENT;
Finally execute clean_ols.sql with catcon.pl.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b clean_ols -d '''.''' clean_ols.sql
Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2:
Oracle Database 11.2.0.4:
$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;
SQL> set pagesize 0
SQL> set feed off
SQL> spool dropsyn.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
SQL> spool off;
Then run the spool file:
SQL> @dropsyn.sql
Finally you’ll have to clean up two additional users:
SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;
Oracle Database 12.1.0.2:
$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;
SQL> set pagesize 0
SQL> set feed off
SQL> spool dropsyn.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
SQL> spool off;
Then run the spool file:
SQL> @dropsyn.sql
Finally you’ll have to clean up two additional users:
SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;
Oracle Database 12.2.0.1:
Start by dropping the user MDSYS:
$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;
SQL> set pagesize 0
SQL> set feed off
SQL> spool dropsyn.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
SQL> spool off;
Then run the spool file:
SQL> @dropsyn.sql
Finally you’ll have to clean up two additional users and a leftover package which belongs to Graph and got introduced in Oracle 12.2.0.1:
SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;
SQL> drop package SYS.SDO_RDF_EXP_IMP;
CDB:
At first I start with dropping the user MDSYS from all PDBs:
cd $ORACLE_HOME/rdbms/admin
vi drop_MDSYS.sql
drop user MDSYS cascade;
set pagesize 0
set feed off
set linesize 200
set termout off
set serverout on
spool dropsyn.sql
exec DBMS_OUTPUT.PUT_LINE('alter session set "_ORACLE_SCRIPT"=TRUE;');
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
exit
In addition I will prepare a script for later to remove the leftover Spatial users:
vi drop_others.sql
drop user mddata cascade;
drop user spatial_csw_admin_usr cascade;
Remove MDSYS and leftover-synonyms from PDBs
Now I can drop MDSYS from the PDBs and the PDB$SEED at first, then I clean up the leftover synonyms:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_MDSYS_pdbs -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b dropsyn_pdbs -d '''.''' dropsyn.sql
Remove MDSYS and leftover-synonyms from CDB$ROOT
Before I can remove MDSYS from the CDB$ROOT container I will have to shutdown all PDBs including the PDB$SEED as otherwise the drop statement fails with ORA-604 and ORA-14452: attempt to create, alter or drop an index on temporary table already in use.
sqlplus / as sysdba
SQL> alter pluggable database all close immediate;
SQL> alter pluggable database pdb$seed close;
SQL> exit
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_MDSYS_cdb -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b dropsyn_cdb -d '''.''' dropsyn.sql
Afterwards I’m opening all PDBs again:
sqlplus / as sysdba
SQL> alter pluggable database all open;
SQL> alter pluggable database pdb$seed open read only;
SQL> exit
In the next step I drop the remaining Spatial users:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_others_pdbs -d '''.''' drop_others.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_others_cdb -d '''.''' drop_others.sql
Finally I remove a leftover package from all containers:
$ vi drop_leftovers.sql
drop package sys.SDO_RDF_EXP_IMP;
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b drop_leftovers -d '''.''' drop_leftovers.sql
Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-12.2:
Oracle Database 11.2.0.4
You can approach the removal of Oracle Text simply with this script and a drop command:
$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql
Oracle Database 12.1.0.2:
$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql
Oracle Database 12.2.0.1
NON-CDB
The same process for Oracle Text works in Oracle Database 12.2.0.1 for non-CDBs as it did in previous releases. Start by simply running the removal script and a cleanup command:
$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql
CDB
At first I start the removal process from the PDBs:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnoctx_pdbs -d $ORACLE_HOME/ctx/admin catnoctx.sql
As this step has some leftovers I will clean them up with a short script:
$ vi ctx_leftovers.sql
drop procedure sys.validate_context;
drop package XDB.dbms_xdbt;
drop procedure xdb.xdb_datastore_proc;
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b ctx_leftovers_pdbs -d '''.''' ctx_leftovers.sql
Additionally I will repeat the same steps within the CDB$ROOT container:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoctx_cdb -d $ORACLE_HOME/ctx/admin catnoctx.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b ctx_leftovers_cdb -d '''.''' ctx_leftovers.sql
And finally I will have to remove a leftover public synonym from all containers:
$ vi ctx_rmsyn.sql
drop public synonym DBMS_XDBT;
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b ctx_rmsys -d '''.''' ctx_rmsyn.sql
Oracle Multimedia (ORDIM) Clean Up in Oracle Database 11.2-12.2:
Oracle Database 11.2.0.4
You can remove Oracle Multimedia with a single script which checks for usage and expects an interactive Y/N response before it proceeds.
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM
==> You must answer with y/n to allow the script to either proceed or cancel
Oracle Database 12.1.0.2
To remove ORDIM from Oracle Database 12.1.0.2 use the same procedure as in Oracle Database 11.2.0.4:.
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM
==> You must answer with y/n to allow the script to either proceed or cancel
Oracle Database 12.2.0.1
NON-CDB
Again, the same process as in previous releases applies. But it isn’t as flawless as in previous releases. Therefore some leftovers require a cleanup and a recompilation afterwards::.
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM
==> You must answer with y/n to allow the script to either proceed or cancel
The following objects must be cleaned up manually:
SYS JAVA DATA prv//////OBPJCDOAAAAAAAAA INVALID
SYS JAVA DATA prv//////NGOANDOGAAAAAAAA INVALID
SYS PACKAGE BODY ORD_ADMIN INVALID
SYS PACKAGE BODY ORDIMDPCALLOUTS INVALID
The recompilation cleans out the invalid Java Data. In addition you must drop the invalid packages manually.
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql
SQL> drop package SYS.ORD_ADMIN; --not necessary in Oracle 19c
SQL> drop package SYS.ORDIMDPCALLOUTS;
CDB
At first you’ll run an optional check for ORDIM usage:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b imremchk -d $ORACLE_HOME/ord/im/admin imremchk.sql
Please check the log file imremchk0.log.
Afterwards you’ll remove ORDIM from all PDBs first followed by a mandatory recompilation:
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b imremdo_pdbs -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
In the next phase you will remove ORDIM now from the CDB$ROOT and recompile again:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b imremdo_cdb -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
And finally you have to cleanup leftovers from all container:
$ vi dropim.sql
drop package SYS.ORD_ADMIN;
drop package SYS.ORDIMDPCALLOUTS;
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b dropim -d '''.''' dropim.sql
Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2:
Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2 requires recompilation and some workarounds in most releases. Nevertheless, I’m documenting the removal steps for all 3 OLAP parts:
XOQ – OLAP API
APS – OLAP Analytical Workspace
AMD – OLAP Catalog
Oracle Database 11.2.0.4
XOQ – OLAP API
To remove XQO you will have to execute to scripts but add recompilation as well as approximately 20 synonyms will stay invalid:
$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql
APS – OLAP Analytic Workspace
To clean up APS you must unlink OLAP first.
$ chopt disable olap
In the next step execute the removal script and recompile. The recompilation is necessary to clean up 3 invalid synonyms.
$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
AMD – OLAP Catalog
$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoamd.sql
Oracle Database 12.1.0.2
XOQ – OLAP API
To remove XQO you will have to execute to scripts but add recompilation as well as approximately 38 synonyms will stay invalid:
$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql
APS – OLAP Analytic Workspace
To clean up APS you must unlink OLAP first.
$ chopt disable olap
In the next step execute the removal script and recompile. The recompilation is necessary to clean up 3 invalid public synonyms.
$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
AMD – OLAP Catalog
Oracle Database 12.2.0.1
XOQ – OLAP API
NON-CDB
Remove XOQ by executing these scripts but add recompilation as well as approximately 20 synonyms will stay invalid:
$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql
CDB
The first removal script can run in all PDBs:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b olapidrp_pdbs -d $ORACLE_HOME/olap/admin/ olapidrp.plb
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB1' -e -b catnoxoq_pdb1 -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ sqlplus / as sysdba
SQL> alter pluggable database PDB1 close;
SQL> alter pluggable database PDB1 open;
Repeat this steps for all PDBs, one after another.
The PDB$SEED must be restarted “read only“:
SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed open read only;
Before you approach the removal from CDB$ROOT you must recompile:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
Afterwards you can approach the XOQ removal from CDB$ROOT:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b olapidrp_cdb -d $ORACLE_HOME/olap/admin/ olapidrp.plb
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoxoq_cdb -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
APS – OLAP Analytic Workspace
NON-CDB
At first unlink the option from the Oracle kernel:
chopt disable olap
$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
CDB:
:
chopt disable olap
If you want to remove APS you will have to do this PDB after PDB as during the removal of XOQ as otherwise you’ll receive an ORA-65023: active transaction exists in container PDBn. A recompilation afterwards is mandatory:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB2' -e -b catnoaps_pdb2 -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB1' -e -b catnoaps_pdb1 -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB$SEED' -e -b catnoaps_pdbseed -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
Then you can approach the removal from CDB$ROOT, again followed by a recompilation:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoaps_cdb -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2
Oracle Database 11.2.0.4:
$ sqlplus / as sysdba
@?/owb/UnifiedRepos/clean_owbsys.sql
select object_name, object_type from dba_objects where object_name='DBMS_OWB';
OBJECT_NAME OBJECT_TYPE
-------------------- ------------
DBMS_OWB PACKAGE
DBMS_OWB PACKAGE BODY
drop package SYS.DBMS_OWB;
Oracle Database 12.1.0.2:
select status, comp_id, version from dba_registry order by 2
STATUS COMP_ID VERSION
------------ ------------ ------------
OPTION OFF AMD 11.2.0.4.0
VALID APEX 4.2.5.00.08
VALID APS 12.1.0.2.0
VALID CATALOG 12.1.0.2.0
VALID CATJAVA 12.1.0.2.0
VALID CATPROC 12.1.0.2.0
VALID CONTEXT 12.1.0.2.0
VALID JAVAVM 12.1.0.2.0
VALID ORDIM 12.1.0.2.0
VALID OWB 11.2.0.4.0
VALID OWM 12.1.0.2.0
VALID SDO 12.1.0.2.0
VALID XDB 12.1.0.2.0
VALID XML 12.1.0.2.0
VALID XOQ 12.1.0.2.0
Expression Filter and Rules Manager (EXF, RUL) Clean Up in Oracle Database 11.2-12.2
Oracle Database 11.2.0.4:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catnoexf.sql
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.58.203)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias SRCDB122
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 03-APR-2021 10:51:39
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u03/app/oracle/product/12.2.0.1/db_2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/srlabgg3/srcdb122/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.58.203)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
The listener supports no services
The command completed successfully
[oracle@srlabgg3 admin]$ ps -ef |grep tns
root 22 2 0 10:27 ? 00:00:00 [netns]
oracle 11227 1 0 10:37 ? 00:00:00 /u03/app/oracle/product/12.1.0.2/db_3/bin/tnslsnr SRLAB121 -inherit
oracle 22556 1 1 10:51 ? 00:00:00 /u03/app/oracle/product/12.2.0.1/db_2/bin/tnslsnr SRCDB122 -inherit
oracle 22559 9427 0 10:51 pts/0 00:00:00 grep --color=auto tns
[oracle@srlabgg3 admin]$ ps -ef |grep pmn
oracle 22563 9427 0 10:51 pts/0 00:00:00 grep --color=auto pmn
[oracle@srlabgg3 admin]$ ps -ef |grep pmon
oracle 10420 1 0 10:36 ? 00:00:00 ora_pmon_srlab
oracle 13878 1 0 10:40 ? 00:00:00 ora_pmon_srcdb
oracle 22565 9427 0 10:51 pts/0 00:00:00 grep --color=auto pmon
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$ clear
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$ hostname
srlabgg3.localdomain
[oracle@srlabgg3 admin]$ ps -ef |grep pmon
oracle 6560 9427 0 13:00 pts/0 00:00:00 grep --color=auto pmon
oracle 10420 1 0 10:36 ? 00:00:01 ora_pmon_srlab
oracle 13878 1 0 10:40 ? 00:00:01 ora_pmon_srcdb
[oracle@srlabgg3 admin]$ ps -ef |grep tns
root 22 2 0 10:27 ? 00:00:00 [netns]
oracle 7408 9427 0 13:00 pts/0 00:00:00 grep --color=auto tns
oracle 11227 1 0 10:37 ? 00:00:00 /u03/app/oracle/product/12.1.0.2/db_3/bin/tnslsnr SRLAB121 -inherit
oracle 22556 1 0 10:51 ? 00:00:00 /u03/app/oracle/product/12.2.0.1/db_2/bin/tnslsnr SRCDB122 -inherit
[oracle@srlabgg3 admin]$ . oraenv
ORACLE_SID = [srcdb] ? srlab
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srlabgg3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 3 13:01:29 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRLAB
SQL> show pdbs
SQL>
SQL>
SQL> set line 200
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12
col CON_ID format 99
select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
CON_ID COMP_ID COMP_NAME SCHEMA STATUS VERSION
------ -------- ---------------------------------- ------------ ---------- ------------
0 APEX Oracle Application Express APEX_040200 VALID 4.2.5.00.08
0 APS OLAP Analytic Workspace SYS VALID 12.1.0.2.0
0 CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0
0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0
0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0
0 CONTEXT Oracle Text CTXSYS VALID 12.1.0.2.0
0 DV Oracle Database Vault DVSYS VALID 12.1.0.2.0
0 JAVAVM JServer JAVA Virtual Machine SYS VALID 12.1.0.2.0
0 OLS Oracle Label Security LBACSYS VALID 12.1.0.2.0
0 ORDIM Oracle Multimedia ORDSYS VALID 12.1.0.2.0
0 OWM Oracle Workspace Manager WMSYS VALID 12.1.0.2.0
0 RAC Oracle Real Application Clusters SYS OPTION OFF 12.1.0.2.0
0 SDO Spatial MDSYS VALID 12.1.0.2.0
0 XDB Oracle XML Database XDB VALID 12.1.0.2.0
0 XML Oracle XDK SYS VALID 12.1.0.2.0
0 XOQ Oracle OLAP API SYS VALID 12.1.0.2.0
16 rows selected.
SQL> SQL>
SQL>
SQL>
SQL>
SQL> set line 200
set pages 1000
col owner format a12
col object_type format a12
col object_name format a30
col STATUS format a8
col CON_ID format 9
select con_id, owner, object_type, object_name, status from CDB_OBJECTS where status='INVALID' order by 1,2,3;
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
no rows selected
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$ cd $ORACLE_HOME/apex
[oracle@srlabgg3 apex]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 3 13:09:48 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRLAB
SQL>
SQL> @apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_040200
Session altered.
PL/SQL procedure successfully completed.
no rows selected
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
old 3: wwv_flow_upgrade.flows_files_objects_remove('&APPUN');
new 3: wwv_flow_upgrade.flows_files_objects_remove('APEX_040200');
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
no rows selected
old 1: drop user &APPUN cascade
new 1: drop user APEX_040200 cascade
User dropped.
old 2: if '&UPGRADE' = '1' then
new 2: 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.
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
PL/SQL procedure successfully completed.
old 6: if '&UPGRADE' = '1' then
new 6: 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
********************************************************************
** You must exit this SQL*Plus session before running apexins.sql **
********************************************************************
SQL> drop PUBLIC SYNONYM APEX_SPATIAL;
Synonym dropped.
SQL> drop PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOGIN;
drop PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOGIN
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> set line 200
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12
col CON_ID format 99
select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
CON_ID COMP_ID COMP_NAME SCHEMA STATUS VERSION
------ -------- ---------------------------------- ------------ ---------- ------------
0 APS OLAP Analytic Workspace SYS VALID 12.1.0.2.0
0 CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0
0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0
0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0
0 CONTEXT Oracle Text CTXSYS VALID 12.1.0.2.0
0 DV Oracle Database Vault DVSYS VALID 12.1.0.2.0
0 JAVAVM JServer JAVA Virtual Machine SYS VALID 12.1.0.2.0
0 OLS Oracle Label Security LBACSYS VALID 12.1.0.2.0
0 ORDIM Oracle Multimedia ORDSYS VALID 12.1.0.2.0
0 OWM Oracle Workspace Manager WMSYS VALID 12.1.0.2.0
0 RAC Oracle Real Application Clusters SYS OPTION OFF 12.1.0.2.0
0 SDO Spatial MDSYS VALID 12.1.0.2.0
0 XDB Oracle XML Database XDB VALID 12.1.0.2.0
0 XML Oracle XDK SYS VALID 12.1.0.2.0
0 XOQ Oracle OLAP API SYS VALID 12.1.0.2.0
15 rows selected.
SQL> set line 200
set pages 1000
col owner format a12
col object_type format a12
col object_name format a30
col STATUS format a8
col CON_ID format 9
select con_id, owner, object_type, object_name, status from CDB_OBJECTS where status='INVALID' order by 1,2,3;
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
CON_ID OWNER OBJECT_TYPE OBJECT_NAME STATUS
------ ------------ ------------ ------------------------------ --------
0 PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOG INVALID
SQL>
SQL> drop PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOG;
Synonym dropped.
SQL> select con_id, owner, object_type, object_name, status from CDB_OBJECTS where status='INVALID' order by 1,2,3;
no rows selected
SQL> @?/rdbms/admin/owmuinst.plb
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
SQL> select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
CON_ID COMP_ID COMP_NAME SCHEMA STATUS VERSION
------ -------- ---------------------------------- ------------ -------- ------------
0 APS OLAP Analytic Workspace SYS VALID 12.1.0.2.0
0 CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0
0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0
0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0
0 CONTEXT Oracle Text CTXSYS VALID 12.1.0.2.0
0 DV Oracle Database Vault DVSYS VALID 12.1.0.2.0
0 JAVAVM JServer JAVA Virtual Machine SYS VALID 12.1.0.2.0
0 OLS Oracle Label Security LBACSYS VALID 12.1.0.2.0
0 ORDIM Oracle Multimedia ORDSYS VALID 12.1.0.2.0
0 RAC Oracle Real Application Clusters SYS OPTION O 12.1.0.2.0
FF
0 SDO Spatial MDSYS VALID 12.1.0.2.0
0 XDB Oracle XML Database XDB VALID 12.1.0.2.0
0 XML Oracle XDK SYS VALID 12.1.0.2.0
0 XOQ Oracle OLAP API SYS VALID 12.1.0.2.0
14 rows selected.
SQL> @?/rdbms/admin/catnools.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Trigger dropped.
Trigger dropped.
Trigger dropped.
Dropping role LBAC_DBA
Dropping context LBAC_CTX
Dropping context LBAC$LABELS
Dropping context ORA_OLS_SESSION_LABELS
Dropping public synonym ALL_SA_AUDIT_OPTIONS
Dropping public synonym ALL_SA_COMPARTMENTS
Dropping public synonym ALL_SA_DATA_LABELS
Dropping public synonym ALL_SA_GROUPS
Dropping public synonym ALL_SA_GROUP_HIERARCHY
Dropping public synonym ALL_SA_LABELS
Dropping public synonym ALL_SA_LEVELS
Dropping public synonym ALL_SA_POLICIES
Dropping public synonym ALL_SA_PROG_PRIVS
Dropping public synonym ALL_SA_SCHEMA_POLICIES
Dropping public synonym ALL_SA_TABLE_POLICIES
Dropping public synonym ALL_SA_USERS
Dropping public synonym ALL_SA_USER_COMPARTMENTS
Dropping public synonym ALL_SA_USER_GROUPS
Dropping public synonym ALL_SA_USER_LABELS
Dropping public synonym ALL_SA_USER_LEVELS
Dropping public synonym ALL_SA_USER_PRIVS
Dropping public synonym CDB_LBAC_POLICIES
Dropping public synonym CDB_LBAC_SCHEMA_POLICIES
Dropping public synonym CDB_LBAC_TABLE_POLICIES
Dropping public synonym CDB_OLS_STATUS
Dropping public synonym CDB_SA_AUDIT_OPTIONS
Dropping public synonym CDB_SA_COMPARTMENTS
Dropping public synonym CDB_SA_DATA_LABELS
Dropping public synonym CDB_SA_GROUPS
Dropping public synonym CDB_SA_GROUP_HIERARCHY
Dropping public synonym CDB_SA_LABELS
Dropping public synonym CDB_SA_LEVELS
Dropping public synonym CDB_SA_POLICIES
Dropping public synonym CDB_SA_PROGRAMS
Dropping public synonym CDB_SA_PROG_PRIVS
Dropping public synonym CDB_SA_SCHEMA_POLICIES
Dropping public synonym CDB_SA_TABLE_POLICIES
Dropping public synonym CDB_SA_USERS
Dropping public synonym CDB_SA_USER_COMPARTMENTS
Dropping public synonym CDB_SA_USER_GROUPS
Dropping public synonym CDB_SA_USER_LABELS
Dropping public synonym CDB_SA_USER_LEVELS
Dropping public synonym CDB_SA_USER_PRIVS
Dropping public synonym DBA_LBAC_POLICIES
Dropping public synonym DBA_LBAC_SCHEMA_POLICIES
Dropping public synonym DBA_LBAC_TABLE_POLICIES
Dropping public synonym DBA_OLS_STATUS
Dropping public synonym DBA_SA_AUDIT_OPTIONS
Dropping public synonym DBA_SA_COMPARTMENTS
Dropping public synonym DBA_SA_DATA_LABELS
Dropping public synonym DBA_SA_GROUPS
Dropping public synonym DBA_SA_GROUP_HIERARCHY
Dropping public synonym DBA_SA_LABELS
Dropping public synonym DBA_SA_LEVELS
Dropping public synonym DBA_SA_POLICIES
Dropping public synonym DBA_SA_PROGRAMS
Dropping public synonym DBA_SA_PROG_PRIVS
Dropping public synonym DBA_SA_SCHEMA_POLICIES
Dropping public synonym DBA_SA_TABLE_POLICIES
Dropping public synonym DBA_SA_USERS
Dropping public synonym DBA_SA_USER_COMPARTMENTS
Dropping public synonym DBA_SA_USER_GROUPS
Dropping public synonym DBA_SA_USER_LABELS
Dropping public synonym DBA_SA_USER_LEVELS
Dropping public synonym DBA_SA_USER_PRIVS
Dropping public synonym LBAC_LABEL_TO_CHAR
Dropping public synonym LBAC_TO_NUMERIC
Dropping public synonym LBAC_POLICY_ADMIN
Dropping public synonym SA_POLICY_ADMIN
Dropping public synonym LBAC_SESSION
Dropping public synonym LBAC_UTL
Dropping public synonym LDAP_ATTR
Dropping public synonym LDAP_ATTR_LIST
Dropping public synonym LDAP_EVENT
Dropping public synonym LDAP_EVENT_STATUS
Dropping public synonym DOMINATED_BY
Dropping public synonym DOM_BY
Dropping public synonym OLS_DOMINATED_BY
Dropping public synonym OLS_DOM_BY
Dropping public synonym DOMINATES
Dropping public synonym DOM
Dropping public synonym OLS_DOMINATES
Dropping public synonym OLS_DOM
Dropping public synonym GREATEST_LBOUND
Dropping public synonym GLBD
Dropping public synonym OLS_GREATEST_LBOUND
Dropping public synonym OLS_GLBD
Dropping public synonym LABEL_TO_CHAR
Dropping public synonym NUMERIC_LABEL_TO_CHAR
Dropping public synonym TAGSEQ_TO_CHAR
Dropping public synonym NUMERIC_TO_LBAC
Dropping public synonym LEAST_UBOUND
Dropping public synonym LUBD
Dropping public synonym OLS_LEAST_UBOUND
Dropping public synonym OLS_LUBD
Dropping public synonym MERGE_LABEL
Dropping public synonym STRICTLY_DOMINATED_BY
Dropping public synonym S_DOM_BY
Dropping public synonym OLS_STRICTLY_DOMINATED_BY
Dropping public synonym OLS_S_DOM_BY
Dropping public synonym STRICTLY_DOMINATES
Dropping public synonym S_DOM
Dropping public synonym OLS_STRICTLY_DOMINATES
Dropping public synonym OLS_S_DOM
Dropping public synonym OID_ENABLED
Dropping public synonym LBAC_AUDIT_ACTIONS
Dropping public synonym OLS_DIP_NTFY
Dropping public synonym OLS_LABEL_DOMINATES
Dropping public synonym ORA_GET_AUDITED_LABEL
Dropping public synonym PRIVS_TO_CHAR
Dropping public synonym SA_AUDIT_ADMIN
Dropping public synonym SA_COMPONENTS
Dropping public synonym SA_LABEL_ADMIN
Dropping public synonym SA_SESSION
Dropping public synonym SA_SYSDBA
Dropping public synonym SA_USER_ADMIN
Dropping public synonym SA_UTL
Dropping public synonym TO_LABEL_LIST
Dropping public synonym TO_LBAC_DATA_LABEL
Dropping public synonym TO_LBAC_LABEL
Dropping public synonym TO_NUMERIC_DATA_LABEL
Dropping public synonym TO_DATA_LABEL
Dropping public synonym TO_NUMERIC_LABEL
Dropping public synonym CHAR_TO_LABEL
Dropping public synonym USER_SA_SESSION
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
User dropped.
0 rows deleted.
Commit complete.
SQL>
SQL> select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
CON_ID COMP_ID COMP_NAME SCHEMA STATUS VERSION
------ -------- ---------------------------------- ------------ -------- ------------
0 APS OLAP Analytic Workspace SYS VALID 12.1.0.2.0
0 CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0
0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0
0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0
0 CONTEXT Oracle Text CTXSYS VALID 12.1.0.2.0
0 DV Oracle Database Vault DVSYS VALID 12.1.0.2.0
0 JAVAVM JServer JAVA Virtual Machine SYS VALID 12.1.0.2.0
0 ORDIM Oracle Multimedia ORDSYS VALID 12.1.0.2.0
0 RAC Oracle Real Application Clusters SYS OPTION O 12.1.0.2.0
FF
0 SDO Spatial MDSYS VALID 12.1.0.2.0
0 XDB Oracle XML Database XDB VALID 12.1.0.2.0
0 XML Oracle XDK SYS VALID 12.1.0.2.0
0 XOQ Oracle OLAP API SYS VALID 12.1.0.2.0
13 rows selected.
SQL> col status for a20
SQL> /
CON_ID COMP_ID COMP_NAME SCHEMA STATUS VERSION
------ -------- ---------------------------------- ------------ -------------------- ------------
0 APS OLAP Analytic Workspace SYS VALID 12.1.0.2.0
0 CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0
0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0
0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0
0 CONTEXT Oracle Text CTXSYS VALID 12.1.0.2.0
0 DV Oracle Database Vault DVSYS VALID 12.1.0.2.0
0 JAVAVM JServer JAVA Virtual Machine SYS VALID 12.1.0.2.0
0 ORDIM Oracle Multimedia ORDSYS VALID 12.1.0.2.0
0 RAC Oracle Real Application Clusters SYS OPTION OFF 12.1.0.2.0
0 SDO Spatial MDSYS VALID 12.1.0.2.0
0 XDB Oracle XML Database XDB VALID 12.1.0.2.0
0 XML Oracle XDK SYS VALID 12.1.0.2.0
0 XOQ Oracle OLAP API SYS VALID 12.1.0.2.0
13 rows selected.
SQL> drop user MDSYS cascade;
User dropped.
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
14
SQL>
SQL> select comp_id,comp_name,schema,version,status from dba_registry;
COMP_ID COMP_NAME SCHEMA VERSION STATUS
-------- ---------------------------------- ------------ ------------ --------------------
DV Oracle Database Vault DVSYS 12.1.0.2.0 INVALID
ORDIM Oracle Multimedia ORDSYS 12.1.0.2.0 VALID
XDB Oracle XML Database XDB 12.1.0.2.0 VALID
CATALOG Oracle Database Catalog Views SYS 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types SYS 12.1.0.2.0 VALID
JAVAVM JServer JAVA Virtual Machine SYS 12.1.0.2.0 VALID
XML Oracle XDK SYS 12.1.0.2.0 VALID
CATJAVA Oracle Database Java Packages SYS 12.1.0.2.0 VALID
APS OLAP Analytic Workspace SYS 12.1.0.2.0 VALID
XOQ Oracle OLAP API SYS 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters SYS 12.1.0.2.0 OPTION OFF
11 rows selected.
SQL>
SQL> @?/rdbms/admin/catcmprm.sql ORDIM
Session altered.
About to remove Oracle Multimedia.
Checking to see if anyone is using Oracle Multimedia.
Session altered.
Session altered.
Package created.
Package body created.
No errors.
Session altered.
Oracle Multimedia is not being used
PL/SQL procedure successfully completed.
Package dropped.
Session altered.
Are you sure you want to remove Oracle Multimedia (Y/N): Y
PL/SQL procedure successfully completed.
Session altered.
Removing Oracle Multimedia
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.
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.
Commit complete.
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.
Session altered.
ERROR:
ORA-01435: user does not exist
Session altered.
PL/SQL procedure successfully completed.
Commit complete.
PL/SQL procedure successfully completed.
drop user MDSYS cascade
*
ERROR at line 1:
ORA-01918: user 'MDSYS' does not exist
Session altered.
Session altered.
Session altered.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
SQL> SQL>
SQL>
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
Session altered.
Package dropped.
Package dropped.
Type dropped.
Type dropped.
Sequence dropped.
Type dropped.
Synonym dropped.
Procedure dropped.
Synonym dropped.
Function dropped.
Library dropped.
Type dropped.
Type dropped.
Type dropped.
Type dropped.
Package dropped.
Package dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Sequence dropped.
Library dropped.
DROP PUBLIC SYNONYM GV_Olapi_Session_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM V_Olapi_Session_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM GV_Olapi_Iface_Object_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM V_Olapi_Iface_Object_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM GV_Olapi_Iface_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM V_Olapi_Iface_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM GV_Olapi_Memory_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM V_Olapi_Memory_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM GV_Ksmhp FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_History_Seq FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_Session_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_Iface_Object_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_Iface_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_Memory_Op_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM Olapi_Memory_Heap_History FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM DBMS_XSOQ FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP PUBLIC SYNONYM DBMS_XSOQ_UTIL FORCE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
DROP VIEW GV_Olapi_Session_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW V_Olapi_Session_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW GV_Olapi_Iface_Object_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW V_Olapi_Iface_Object_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW GV_Olapi_Iface_Op_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW V_Olapi_Iface_Op_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW GV_Olapi_Memory_Op_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW V_Olapi_Memory_Op_History
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW GV_Ksmhp
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP SEQUENCE Olapi_History_Seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
DROP TABLE Olapi_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE Olapi_Session_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE Olapi_Iface_Object_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE Olapi_Iface_Op_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE Olapi_Memory_Op_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE Olapi_Memory_Heap_History CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP PROCEDURE OlapiHistoryRetention
*
ERROR at line 1:
ORA-04043: object OLAPIHISTORYRETENTION does not exist
DROP TRIGGER OlapiStartupTrigger
*
ERROR at line 1:
ORA-04080: trigger 'OLAPISTARTUPTRIGGER' does not exist
DROP TRIGGER OlapiShutdownTrigger
*
ERROR at line 1:
ORA-04080: trigger 'OLAPISHUTDOWNTRIGGER' does not exist
DROP PACKAGE DBMS_XSOQ
*
ERROR at line 1:
ORA-04043: object DBMS_XSOQ does not exist
DROP PACKAGE DBMS_XSOQ_UTIL
*
ERROR at line 1:
ORA-04043: object DBMS_XSOQ_UTIL does not exist
PL/SQL procedure successfully completed.
Synonym dropped.
Package dropped.
DROP PACKAGE GenDatabaseInterface
*
ERROR at line 1:
ORA-04043: object GENDATABASEINTERFACE does not exist
DROP PACKAGE GenConnectionInterface
*
ERROR at line 1:
ORA-04043: object GENCONNECTIONINTERFACE does not exist
DROP PACKAGE GenServerInterface
*
ERROR at line 1:
ORA-04043: object GENSERVERINTERFACE does not exist
DROP PACKAGE GenMdmPropertyIdConstants
*
ERROR at line 1:
ORA-04043: object GENMDMPROPERTYIDCONSTANTS does not exist
DROP PACKAGE GenMdmClassConstants
*
ERROR at line 1:
ORA-04043: object GENMDMCLASSCONSTANTS does not exist
DROP PACKAGE GenMdmObjectIdConstants
*
ERROR at line 1:
ORA-04043: object GENMDMOBJECTIDCONSTANTS does not exist
DROP PACKAGE GenMetadataProviderInterface
*
ERROR at line 1:
ORA-04043: object GENMETADATAPROVIDERINTERFACE does not exist
DROP PACKAGE GenCursorManagerInterface
*
ERROR at line 1:
ORA-04043: object GENCURSORMANAGERINTERFACE does not exist
DROP PACKAGE GenDataTypeIdConstants
*
ERROR at line 1:
ORA-04043: object GENDATATYPEIDCONSTANTS does not exist
DROP PACKAGE GenDefinitionManagerInterface
*
ERROR at line 1:
ORA-04043: object GENDEFINITIONMANAGERINTERFACE does not exist
DROP PACKAGE GenDataProviderInterface
*
ERROR at line 1:
ORA-04043: object GENDATAPROVIDERINTERFACE does not exist
PL/SQL procedure successfully completed.
Procedure dropped.
Function dropped.
Package dropped.
User dropped.
Role dropped.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
Session altered.
SQL> @?/rdbms/admin/utlrp.sql
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
COMP_ID COMP_NAME SCHEMA VERSION STATUS
-------- ---------------------------------- ------------ ------------ --------------------
DV Oracle Database Vault DVSYS 12.1.0.2.0 INVALID
XDB Oracle XML Database XDB 12.1.0.2.0 VALID
CATALOG Oracle Database Catalog Views SYS 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types SYS 12.1.0.2.0 VALID
JAVAVM JServer JAVA Virtual Machine SYS 12.1.0.2.0 VALID
XML Oracle XDK SYS 12.1.0.2.0 VALID
CATJAVA Oracle Database Java Packages SYS 12.1.0.2.0 VALID
APS OLAP Analytic Workspace SYS 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters SYS 12.1.0.2.0 OPTION OFF
9 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@srlabgg3 apex]$
[oracle@srlabgg3 apex]$
[oracle@srlabgg3 apex]$ chopt disable olap
Writing to /u03/app/oracle/product/12.1.0.2/db_3/install/disable_olap.log...
/usr/bin/make -f /u03/app/oracle/product/12.1.0.2/db_3/rdbms/lib/ins_rdbms.mk olap_off ORACLE_HOME=/u03/app/oracle/product/12.1.0.2/db_3
/usr/bin/make -f /u03/app/oracle/product/12.1.0.2/db_3/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u03/app/oracle/product/12.1.0.2/db_3
[oracle@srlabgg3 apex]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 3 14:55:14 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Advanced Analytics and Real Application Testing options
SQL>
SQL>
SQL> @?/olap/admin/catnoaps.sql
Session altered.
Procedure created.
PL/SQL procedure successfully completed.
Procedure dropped.
1 row deleted.
Session altered.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-04-03 14:55:47
Function created.
DECLARE
*
ERROR at line 1:
ORA-00068: invalid value 0 for parameter _query_execution_time_limit, must be
between 1881698932 and 808727084
ORA-06512: at line 22
Function dropped.
Warning: XDB now invalid, could not find xdbconfig
PL/SQL procedure successfully completed.
SQL> set lines 300 pages 20000
SQL>
SQL> col comp_name for a40
SQL>
SQL> select comp_id,comp_name,version,status,schema from dba_registry;
COMP_ID COMP_NAME VERSION STATUS SCHEMA
------------------------------ ---------------------------------------- ------------------------------ -----------
DV Oracle Database Vault 12.1.0.2.0 INVALID DVSYS
XDB Oracle XML Database 12.1.0.2.0 INVALID XDB
CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID SYS
CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID SYS
JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID SYS
XML Oracle XDK 12.1.0.2.0 VALID SYS
CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID SYS
RAC Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF SYS
8 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Advanced Analytics and Real Application Testing options
[oracle@srlabgg3 apex]$ cd ..
[oracle@srlabgg3 db_3]$ cd dv
[oracle@srlabgg3 dv]$ ls -lrt
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Mar 21 10:37 admin
[oracle@srlabgg3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 3 14:59:39 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/dvremov.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
DECLARE
*
ERROR at line 1:
ORA-47996: The RECYCLEBIN is turned on
ORA-06512: at line 9
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Advanced Analytics and Real Application Testing options
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$
[oracle@srlabgg3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 3 15:00:13 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Advanced Analytics and Real Application Testing options
SQL>
SQL>
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL>
SQL>
SQL> alter system set recyclebin=off;
alter system set recyclebin=off
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> alter session set recyclebin=off;
Session altered.
SQL>
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL> @?/rdbms/admin/dvremov.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
User dropped.
User dropped.
DECLARE
*
ERROR at line 1:
ORA-00068: invalid value 0 for parameter _query_execution_time_limit, must be
between 1881698932 and 808727084
ORA-06512: at line 3
ORA-06512: at line 9
No errors.
Type body created.
No errors.
Synonym created.
Grant succeeded.
Session altered.
Index "XDB"."XDB$COMPLEX_TYPE_AK" successfully re-enabled
Index "XDB"."XDB$COMPLEX_TYPE_CK" successfully re-enabled
Index "XDB"."XDB$COMPLEX_TYPE_SK" successfully re-enabled
Index "XDB"."XDB$ELEMENT_HER" successfully re-enabled
Index "XDB"."XDB$ELEMENT_PR" successfully re-enabled
Index "XDB"."XDB$ELEMENT_PS" successfully re-enabled
Index "XDB"."XDB$ELEMENT_TR" successfully re-enabled
Index "XDB"."XDB$RESOURCE_OID_INDEX" successfully re-enabled
Index "XDB"."XDBHI_IDX" successfully re-enabled
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
12.1.0.2.0 XDB VALID
CATALOG Oracle Database Catalog Views 12.1.0.2.0 SYS VALID
CATPROC Oracle Database Packages and Types 12.1.0.2.0 SYS VALID
JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 SYS VALID
XML Oracle XDK 12.1.0.2.0 SYS VALID
CATJAVA Oracle Database Java Packages 12.1.0.2.0 SYS VALID
RAC Oracle Real Application Clusters 12.1.0.2.0 SYS OPTION OFF
7 rows selected.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
662
1 row selected.
SQL>
SQL> @?/rdbms/admin/utlrp.sql
SQL> Rem
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 973082528 bytes
Database Buffers 520093696 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> set lines 300 pages 20000
SQL>
SQL>
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
------------------------------ ---------------------------------------- ------------------------------ -----------
XDB Oracle XML Database 12.1.0.2.0 VALID
CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID
XML Oracle XDK 12.1.0.2.0 VALID
CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
7 rows selected.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2
1 row selected.
SQL>
SQL> col owner for a20
SQL> col object_name for a40
SQL>
SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ---------------------------------------- ----------------------- -------
SYS DBA_DV_STATUS VIEW INVALID
PUBLIC DBA_DV_STATUS SYNONYM INVALID
2 rows selected.
SQL> drop public synonym DBA_DV_STATUS;
Synonym dropped.
SQL> drop view DBA_DV_STATUS;
View dropped.
SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID';
no rows selected
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
1 row selected.
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
------------------------------ ---------------------------------------- ------------------------------ -----------
XDB Oracle XML Database 12.1.0.2.0 VALID
CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID
XML Oracle XDK 12.1.0.2.0 VALID
CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
7 rows selected.
SQL>
Cheers!
Ramesh
Post a Comment: