CODEWITHSUNDEEP
oracle-databaseobjectviewsynonymsql-scripts
SitGeek
SitGeek

Reputation: 45

Script to check to Invalid objects in Oracle and build script to compile the invalid objects

In Oracle DB, in some instances the objects (Views/Synonyms) are getting 'Invalid' (Out of compile) which is adding more work to me to maintain these issues frequently.

I'm planning to include some statements in the initial and end of my script in my every deployment 1. To check all invalid objects before my changes are deployed. 2. After the changes get deployed check all the invalid objects again and build a script automatically to fix the INVALID views & synonyms and execute it.

It would be of great help to me if anyone can help me in building a oracle script which covers my above mentioned points?

NOTE: I don't have the DBA ADMIN access, I use deployment tool to deploy the scripts

Thanks a lot in advance. Sitesh

Upvotes: 2

Views: 10357

Answers (1)

Srikanth Balaji
Srikanth Balaji

Reputation: 2718

This script will find the invalid objects,

select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
from dba_objects
where status != 'VALID'
  and OWNER in ('SCHEMA_1','SCHEMA_2','SCHEMA_3')
order by owner, object_type;

Use the below script to recompile all invalid objects in a given schema.

EXEC DBMS_UTILITY.compile_schema('SCHEMA_1', compile_all => false);

Upvotes: 5

Related Questions