Solved

invalid object

Posted on 2014-04-01
9
521 Views
Last Modified: 2014-04-06
How will find an invalid object in a schema and how we will delete it..
0
Comment
Question by:tonydba
9 Comments
 
LVL 73

Accepted Solution

by:
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....


drop table YOURSCHEMA.YOUR_TABLE;
drop indexYOURSCHEMA.YOUR_INDEX;
drop procedure YOURSCHEMA.YOUR_PROCEDURE;
drop package YOURSCHEMA.YOUR_PACKAGE;
0
 
LVL 15

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.
0
 
LVL 23

Expert Comment

by:David
ID: 39970253
Outside of production databases, I wouldn't sweat having invalid objects.  Testers and developers commonly leave work-in-progress there.
0
 
LVL 34

Expert Comment

by:johnsone
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.
0
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 73

Expert Comment

by:sdstuber
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.
0
 
LVL 34

Expert Comment

by:johnsone
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.
0
 
LVL 73

Expert Comment

by:sdstuber
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
0
 
LVL 34

Expert Comment

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

Author Closing Comment

by:tonydba
ID: 39981686
Yes it is fine..
0

Featured Post

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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now