How to clean up duplicate objects in SYS/SYSTEM

Some times we have different type of errors in 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 be helpful to determine which objects should be dropped from the SYSTEM schema.

Important Note

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

 

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

 

• But we have to very careful while dropping the duplicate objects. The objects listed in Important note should not be touched.
• We should make sure we have good backup of database before dropping the objects. we can use flashback database feature for it
• We should be able to use the drop command to get rid of objects