Home » RDBMS Server » Server Administration » Clean Up Duplicate Objects Owned by SYS and SYSTEM (10g2 and IBM AIX 6.1)
Clean Up Duplicate Objects Owned by SYS and SYSTEM [message #533674] Thu, 01 December 2011 01:58 Go to next message
mmee
Messages: 38
Registered: July 2007
Member

hi

we are doing database upgradation 10g2 to 11gr2, while doing pre check before upgrdation ..we have found few duplicate objectes on sys and system schema..

SQL> 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'; SQL>   2    3    4    5    6    7

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES                  TABLE
AQ$_SCHEDULES_PRIMARY          INDEX
DBMS_REPCAT_AUTH               PACKAGE
DBMS_REPCAT_AUTH               PACKAGE BODY
HELP                           TABLE
HELP_TOPIC_SEQ                 INDEX
PLAN_TABLE                     TABLE

7 rows selected.

SQL> set pause off
SQL> set heading off
SQL> set pagesize 0
SQL> set feedback off
SQL> set verify off
SQL>
SQL> select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
  2  from dba_objects
  3  where object_name||object_type in
  4     (select object_name||object_type
  5      from dba_objects
  6      where owner = 'SYS')
  7  and owner = 'SYSTEM';

DROP TABLE SYSTEM.AQ$_SCHEDULES;
DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY;
DROP PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
DROP TABLE SYSTEM.HELP;
DROP INDEX SYSTEM.HELP_TOPIC_SEQ;
DROP TABLE SYSTEM.PLAN_TABLE;




As per metalink note "How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema [ID 1030426.6]"..

im going to do drop only below objects ..

DROP TABLE SYS.HELP;
DROP INDEX SYS.HELP_TOPIC_SEQ;
DROP TABLE SYSTEM.PLAN_TABLE;

and ignore below objects ...

DROP TABLE SYSTEM.AQ$_SCHEDULES;
DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY;
DROP PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;



could you pls advice me .. im doing right way?



Re: Clean Up Duplicate Objects Owned by SYS and SYSTEM [message #533684 is a reply to message #533674] Thu, 01 December 2011 02:24 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do what the note says.

Regards
Michel
Previous Topic: upgrade oracle10g to 11gr2
Next Topic: Privileges required for export
Goto Forum:
  


Current Time: Fri Apr 19 01:10:34 CDT 2024