invalid object

Posted on 2014-04-01
Last Modified: 2014-04-06
How will find an invalid object in a schema and how we will delete it..
Question by:tonydba
LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 39969950
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....

LVL 16

Expert Comment

by:Walter Ritzel
ID: 39969962
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.
LVL 23

Expert Comment

ID: 39970253
Outside of production databases, I wouldn't sweat having invalid objects.  Testers and developers commonly leave work-in-progress there.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 34

Expert Comment

ID: 39970509
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.
LVL 74

Expert Comment

ID: 39970525
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.
LVL 34

Expert Comment

ID: 39970535
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.
LVL 74

Expert Comment

ID: 39970561
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
LVL 34

Expert Comment

ID: 39970613
I actually do the same thing.  Definitely thought that was too dangerous to post here.

Author Closing Comment

ID: 39981686
Yes it is fine..

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sort a spool into file output in oracle 1 44
help on oracle query 5 52
PL/SQL Display based on value 4 39
Retreiving column names in Windows but not in Unix 11 57
Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question