Compile Invalid objects in oracle

Compile Invalid Objects like procedure, function, packages in Oracle

Compile those objects which is not compiled properly or their relative objects is not present or recreated during any deployment or upgradation. It will impact other object which has dependences on them and left them invalid.

Example: I created one procedure with one view used in that procedure. After sometime i recreate that view then that procedure become invalid OR when you do patching or upgradation activity on the database it caused many objects drop and recreate which cause 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;
OR
SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql;

If schema still contain invalid objects then you will check with application team and drop them if not needed.

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 according to their types:

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);

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.