• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to clean up duplicate objects in SYS/SYSTEM

How to clean up duplicate objects in SYS/SYSTEM

January 30, 2016 by techgoeasy Leave a Comment

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

 

 

Filed Under: Oracle, Oracle Database Tagged With: How to clean up duplicate objects in SYS/SYSTEM

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us