Sometimes we have different errors in the Oracle system because of duplicate objects in SYS/SYSTEM. This can happen as catalog.sql and catproc.sql were mistakenly run while connected as both users. In most cases, the only objects which should exist are those owned by SYS. The output can help determine which objects should be dropped from the SYSTEM schema.
The exception to this rule
(1)
The following objects are duplicates that will show up (and should not be removed) when running this script in 8.1.x and higher.
Without replication installed:
INDEX AQ$_SCHEDULES_PRIMARY VALID VALID
TABLE AQ$_SCHEDULES VALID VALID
If replication is installed by running catrep.sql:
INDEX AQ$_SCHEDULES_PRIMARY VALID VALID
PACKAGE DBMS_REPCAT_AUTH VALID VALID
PACKAGE BODY DBMS_REPCAT_AUTH VALID VALID
TABLE AQ$_SCHEDULES VALID VALID
(2) When the database is upgraded to 11g using DBUA, the following duplicate objects are also created
OBJECT_NAME OBJECT_TYPE -------- ------- Help TABLE Help_Topic_Seq Index
The objects created by sqlplus/admin/help/hlpbld.sql must be owned by SYSTEM because when sqlplus retrieves the help information, it refers to the SYSTEM schema only. DBCA runs this script as a SYSTEM user when it creates the database but DBUA runs this script as a SYS user when upgrading the database (reported as an unpublished BUG 10022360). You can drop the ones in the SYS schema.
(3) REPCAT$* objects should be owned by SYSTEM. If the duplicate exists in the SYS schema, you can drop it from the SYS schema.
(4) LOGSTDBY$* objects should be owned by SYSTEM. If the duplicate exists in the SYS schema, you can drop it from the SYS schema.
(5) SQLPLUS_PRODUCT_PROFILE should be available in the SYSTEM schema. If the duplicate exists in the SYS schema, you can drop it from the SYS schema.
PRODUCT_PRIVS is a VIEW created from SQLPLUS_PRODUCT_PROFILE and PRODUCT_USER_PROFILE is a SYNONYM of SQLPLUS_PRODUCT_PROFILE. Both belong to the SYSTEM schema.
How to clean up duplicate objects in SYS/SYSTEM
The below will show all objects and their statuses that are owned by both SYS and SYSTEM.
select
substr(decode(system.type#,
1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
22, 'LIBRARY', 23, 'DIRECTORY', 'Type='||system.type#),1,15) "Type",
system.name,
decode(sys.status, 0, 'N/A', 1, 'VALID', 'INVALID') "Sys",
decode(system.status, 0, 'N/A', 1, 'VALID', 'INVALID') "System"
from user$ u, obj$ system, obj$ sys
where u.name='SYSTEM'
and u.user#=system.owner#
and system.type#=sys.type#
and system.name=sys.name
and sys.owner#=0
and system.type#!=10 /* N/A objects */
order by 1,2
;
The below type of Output will be shown
Type NAME Sys System
--------------- ------------------------------ ------- -------
LIBRARY DBMS_AQ_LIB VALID VALID
PACKAGE DBMS_AQ VALID INVALID
PACKAGE DBMS_AQADM VALID INVALID
PACKAGE DBMS_AQ_IMPORT_INTERNAL VALID VALID
PACKAGE DBMS_REPCAT_AUTH VALID VALID
PACKAGE BODY DBMS_AQ_IMPORT_INTERNAL VALID INVALID
PACKAGE BODY DBMS_REPCAT_AUTH INVALID INVALID
TABLE AUD$ VALID VALID
TABLE PLAN_TABLE VALID VALID
VIEW DBA_QUEUE_SCHEDULES VALID INVALID
or
column object_name format a30 select object_name, object_type from dba_objects where object_name||object_type in (select object_name||object_type from dba_objects where owner = 'SYS') and owner = 'SYSTEM'; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------- ALL_DAYS VIEW CHAINED_ROWS TABLE COLLECTION TABLE COLLECTION_ID SEQUENCE DBA_LOCKS SYNONYM DBMS_DDL PACKAGE DBMS_SESSION PACKAGE DBMS_SPACE PACKAGE DBMS_SYSTEM PACKAGE DBMS_TRANSACTION PACKAGE DBMS_UTILITY PACKAGE
Important point
- But we have to be very careful while dropping the duplicate objects. The objects listed in the exception rule should not be touched.
- We should ensure we have a good database backup before dropping the objects. we can use the flashback database feature for it
- We should be able to use the drop command to get rid of objects or you can generate the drop command using the below script
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name not in ('AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','AQ$_SCHEDULES','PRODUCT_USER_PROFILE','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS','HELP','HELP_TOPIC_SEQ') and object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
Related Posts
- How to find the High Water mark of the Oracle Table
- Downtime Reduction during Patching
- How to find metadata of any objects in oracle
- How to find table whose statistics are locked
- Automatic Workload Repository