Home » Oracle » Oracle Database » How to clean up duplicate objects in SYS/SYSTEM

How to clean up duplicate objects in SYS/SYSTEM

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.

See also  row migration and row chaining in oracle

(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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top