Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 7 June 2012

How to Compile All invalid Objects

In Oracle databases if we change or alter the structure of objects such as tables the status of dependent objects such as procedures, packages, functions, views, and triggers becomes invalid. Some types of schema objects reference other objects. For example a view contains a query that references tables or other views, and a PL/SQL subprograms might invoke other subprograms and uses reference table or views. these referenced are established at compile time, if the compiler cannot resolve them, then the dependent object being compiled is marked invalid.
An dependent object must be recompiled against the new definition of referenced object before the dependent object can be used. Recompilation occurs automatically when the invalid dependent object is referenced. So in order to compile all the invalid dependent you can run this SQL script.
Check invalid objects:
connect hrms/hrms@orcl3;
SQL> Select object_type, count(*) from dba_objects where status='INVALID' group by object_type;
--or--
set heading off;
set feedback off;
select 'Alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' || object_name || ' compile ' || decode(object_type,'PACKAGE BODY',' body;',';') from user_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW') and status = 'INVALID' order by object_type , object_name;
set heading on;
set feedback on;
/
More on check invalid object click on the link: http://shahiddba.blogspot.com/2012/07/script-to-report-all-invalid-objects-in.html
Re-validation of invalid object adds latency to the application execution. Thus it affect application performance
Fix Invalid Object:
connect sys/oracle@orcl3 as sysdba;
exec dbms_utility.compile_schema ('HRMS');
Note: if some object are still invalidated then use "show error", "select * from user_errors" command in sqlplus to diagnose. If no error then no problem.
or
SQL>@C:\oracle1\rdbms\admin\utlrp.sql
Note: You can find it ORACLE-HOME\RDBMS\ADMIN
Now again connect with HRMS users and check the invalid coloumn with the same SQL
SQL> Select object_type, count(*) from dba_objects where status='INVALID';
Note: Sometimes you have to run it more than 1 times because an object will become valid after the compile only when all objects it depended on are valid.
As we know each time and object changes the last_ddl_time from (ALL_OBJECTS, DBA_OBJECTS, USER_OBJECTS) is modified. so to find the last ddl performed check out the last_ddl_time from (all_objects, dba_objects, ‎user_objects) view.

Select CREATED, TIMESTAMP, last_ddl_time from all_objects‎ WHERE OWNER='HRMS' AND OBJECT_TYPE='TABLE' AND OBJECT_NAME='PAYROLL_MAIN_FILE';‎
In the above query ‘HRMS’ is the schema name and payroll_main_file is the table name.‎
Note: Package header are only compiled once because each time change in object occurs package body is invalidated but it is automatically compiled during execution (in case of local object). Where as a remote object is depends on local object. Oracle strongly recommends that you recompile local dependent objects manually, rather than relying on the automatic dependency mechanism if remote objects exist.

2 comments:

  1. Hi there

    Select object_type, count(*) from dba_objects where status='INVALID';

    missing group by

    ReplyDelete