How to compile Invlaid Objects in Oracle

Before you are going to accomplished our task i.e how to compile the invalid objects we need to understand what does it mean to when you say Invalid objects and why should  you care about the invalid oracle objects.

As you know the database objects are nothing but the tables,schema,views, triggers,sequences, functions, procedure etc etc. And every time we are inserting ,updating the database and do many changes to this objects in the database.

So Invalid Objects in the database is nothing about when we do some thing on the database and it is not work for you.

For example you are running a Schema objects (such as triggers, procedures, or views) might be invalidated when changes are made to objects on which they depend. For example, if a PL/SQL procedure contains a query on a table and you modify table columns that are referenced in the query, then the PL/SQL procedure becomes invalid. You re-validate schema objects by recompiling them.


 Some of the objects may be invalid due to insufficient permissions granted to the object owner. Like file permission and directory permission to certain user are not granted.
At this time what we do, we try to change the file permission and find out the bug which causes the objects to work out.

The query to find out the invalid objects in the Oracle database.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where status=’INVALID’;

And if you want to sort out the invalid objects then mentioned the object type like procedure, Trigger, function, Sequences,view etc etc.

Example:

Select object_name from dba_objects where object_type = 'TRIGGER' and status = 'INVALID';

Now the below portion we are going to discuss how to compile the invalid objects.

There are mainly many ways to recompile the objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile


Manually Recompile:

 This is the best approach to recompile the Invalid objects.

So here is the process.

SQL> Spool filename ex. spool compileagain.sql

I guess as a DBA you know about spool command and why it is used.

spool is a command which is used to get the output to a file.

In a generalized way the syntax would be.

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

 SQL> spool off;

Example

SQL> spool compileagain.sql

sql> select 'alter package 'name of the object for ex: itemloc_traits' compile body:'
       from user_objects where status <>'valid'  and object_type='PACKAGE BODY

sql> spool off

Now you have taken the out to a file called compileagain.sql now run this script to recompile the objects.

sql> @compileagain.sql


You are done if you got your expected output.

Option2:  By DBMS_DDL.ALTER_COMPILE


SYNTAX :
Exec dbms_ddl.alter_compile(Object Type,Schema Name, Object Name);

Where   Object type: name of the type of the object might be it is procedure,funcation,trigger, view,sequence, java class etc etc.

Schema Name: Database User Name

Object Name: Name of the objects which you need to recompile.

Example:

sql> dbms_ddl.alter_compile('TRIGGER', 'HR', 'PRODDD');

Trigger successfully compiled.

  
Option3: DBMS_UTILITY

It's a schema level recompilation.

This method re compiles all the objects in a specific schema.

Syntax be: sql> exec dbma_utility.compile_schema(schama, compile all)

Schema: Database User Name

Compile All: All the database in the specified schema.

sql> exec dbms_utility.compile_schema('HR');

Procedure successfully compiled

  


Have a great day and have fun !!


Please refer for more:

http://dbataj.blogspot.in/2007/08/how-to-compile-invalid-objects.html