invalid object

How will find an invalid object in a schema and how we will delete it..
Who is Participating?
sdstuberConnect With a Mentor Commented:
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....

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.
DavidSenior Oracle Database AdministratorCommented:
Outside of production databases, I wouldn't sweat having invalid objects.  Testers and developers commonly leave work-in-progress there.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
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.
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.
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
johnsoneSenior Oracle DBACommented:
I actually do the same thing.  Definitely thought that was too dangerous to post here.
tonydbaAuthor Commented:
Yes it is fine..
All Courses

From novice to tech pro — start learning today.