Solved

invalid object

Posted on 2014-04-01
9
523 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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 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

13 Experts available now in Live!

Get 1:1 Help Now