Compile Invalid objects in oracle

Compile Invalid Objects

are those objects which is not compiled properly or their relative objects is not present.

Example: I created one procedure with one view used in that procedure. After sometime i drop that view then that procedure become invalid. OR i am doing patching or upgradation activity of database it caused many objects drop and recreate which generates many object in invalid state.

Note: SYS or SYSTEM schema must not contain any invalid objects. If you find try to compile with utlrp.sql otherwise raise ticket on Oracle Support.

Check the database with invalid objects:
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
 

Solution:
If you are SYS User then you can try the following command:

SQL> @?/rdbms/admin/utlrp.sql;

SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql;

If some schema still contain invalid objects then you will check with application team for drop that objects if they are not needed it.

If you do not have permission as sys user then you can compile object one by one with helps of following commands:

1. Find the list of object and their types:

COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status
FROM dba_objects WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

2. Compile object with following commands:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Compile Complete Schema and Database with following Commands:

-- Schema level.
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s