Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a sql query like below to check duplicate objects in SYS/SYSTEM.

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' and OBJECT_NAME not in
('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES',
'DBMS_REPCAT_AUTH');

I have a list of large duplicated objects. My question / problem is : how to get rid of the objects? What is the best way to approach this?

share|improve this question
    
How did this happen? –  Colin 't Hart Apr 24 '13 at 18:41
    
Are you sure they're not different EDITIONs of objects? (perhaps due to an upgrade?) –  Phil Apr 24 '13 at 19:05
    
@Phil Like you said I have been doing sanity operations before upgrade. –  Cell-o Apr 24 '13 at 19:07
    
Have you proviously ran catproc by accident as the wrong user, or something else weird? Seems odd. –  Phil Apr 24 '13 at 19:18
add comment

1 Answer 1

up vote 0 down vote accepted

It seems you have run one of cat*.sql scripts as SYSTEM instead of SYS. Not cool.

Alternatives:

  1. Contact support.
  2. ...or determine what exactly has been done, and then having the SQL script(s) revert the actions one by one. There is nothing in Oracle to help you, you need to determine by your own means (some old notes? old screenlogs? clues in old .bash_history?).
  3. ...or schedule downtime, expdp everything what is relevant in the entire database, re-create, impdp (of course don't impdp SYSTEM schema).
  4. ...or if you are unable to proceed with either 1, 2, 3, don't perform DROPs by any heuristics/guesswork. Continue as you are. Nothing really wrong with your database - just some extra objects in SYSTEM (not in SYS, I hope). They could stand in a way of some future action, but they might not, nobody knows. Normally perform cold backup before upgrade. Chances are the upgrade will be successful. Schedule steps 1,2,3 for later.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.