Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

invalid object

How will find an invalid object in a schema and how we will delete it..
0
tonydba
Asked:
tonydba
1 Solution
 
sdstuberCommented:
select * from dba_objects where status != 'VALID';

or, if you know what schema you want to search...

select * from dba_objects where status != 'VALID' and owner = 'YOUR_SCHEMA'

the drop syntax changes for each object type but follows the same structure

for example....


drop table YOURSCHEMA.YOUR_TABLE;
drop indexYOURSCHEMA.YOUR_INDEX;
drop procedure YOURSCHEMA.YOUR_PROCEDURE;
drop package YOURSCHEMA.YOUR_PACKAGE;
0
 
Walter RitzelSenior Software EngineerCommented:
To identify:

select * from dba_objects where status='INVALID' order by owner;

To delete, that will depend on the object. DROP VIEW, DROP INDEX, etc... according to the object type.
0
 
DavidSenior Oracle Database AdministratorCommented:
Outside of production databases, I wouldn't sweat having invalid objects.  Testers and developers commonly leave work-in-progress there.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johnsoneSenior Oracle DBACommented:
If you are sure that you want to drop them, then this is how I do it.

select 'drop ' || object_type || ' ' || owner || '.' || object_name || ';'
from dba_objects
where status != 'VALID' and owner = 'YOUR_SCHEMA';

Spool the output of that query to a file.  LOOK AT THE FILE AND MAKE SURE IT IS CORRECT.  Then you can run the file.
0
 
sdstuberCommented:
if you simply spool the above query you'll likely get errors when you run it.


For example.  If   PACKAGE xyz is invalid, then PACKAGE BODY xyz will be invalid too.

If you drop the PACKAGE then the body will be dropped along with it, but when you drop the package body you'll get an error because the object doesn't exist.

Same thing with types and type bodies or java classes and java source.

that doesn't make them wrong, it just means you need to make sure you check which errors indicate a real failure and which ones you can ignore.
0
 
johnsoneSenior Oracle DBACommented:
I usually have a where clause on the query to exclude things like package body.  Since it appears that asker is new, I wanted them to really look at the file before they ran it.

Ideally, if you have never done it before, I actually don't recommend running the file.  I highly recommend cutting and pasting the commands one at a time.  You get a better understanding of what is going on that way.
0
 
sdstuberCommented:
I don't exclude the bodies because the body can be invalid while the header is valid.  

And yes, I agree the individual drops should be examined.  
Actually, that's why I didn't spool them , but rather just gave the syntax examples.
My normal approach for these would be to use a pl/sql block that iterated through the drops, captured "not exists" exceptions, ignored them but reported all other exceptions.

It's nicely efficient but I only run it in very controlled circumstances and it seemed a little dangerous for this thread
0
 
johnsoneSenior Oracle DBACommented:
I actually do the same thing.  Definitely thought that was too dangerous to post here.
0
 
tonydbaAuthor Commented:
Yes it is fine..
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now