Search This Blog

Loading...

Sunday, August 5, 2007

How to compile invalid objects


My Signature Article





Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips




There are five ways to recompile invalid objects in schema.



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



DBMS_DDL.ALTER_COMPILE



Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.



DBMS_UTILITY.COMPILE_SCHEMA



Definition

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.



UTL_RECOMP



Definition

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.


Note: Required SYS user to run this package.



UTLRP.SQL scripts



Definition

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21


PL/SQL procedure successfully completed.


TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26


PL/SQL procedure successfully completed.


Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.



Best Approach is manually recompiling all Invalid Objects



Spool recompile.sql

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

Spool off
@recompile.sql


Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER


Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql


Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql


Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql


Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql




Objects need to recompile are:
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY


Please send me feedback at star_taj@yahoo.com

11 comments:

neeraj said...

Can I compile dbms_utility in sys schema

Mohammad Taj said...

NO, you can't compile sys objects through DBMS_UTILITY. you have to use UTLRP.SQL script.


[quote]
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> exec dbms_utility.compile_schema('SYSTEM');

PL/SQL procedure successfully completed.


SQL> exec dbms_utility.compile_schema('SYS');
BEGIN dbms_utility.compile_schema('SYS'); END;

*
ERROR at line 1:
ORA-20001: Cannot recompile SYS objects
ORA-06512: at "SYS.DBMS_UTILITY", line 366
ORA-06512: at line 1

[/quote]

Vince said...

So how can I get this to run:

alter package DBMS_UTILITY compile body;

On my test system, where it shows this as INVALID it runs and runs and runs. I did a sqlplus '/ as sysdba' and worked from the SQL>

I ended up doing a Ctrl+C and got:

alter package DBMS_UTILITY compile body
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Mohammed Taj said...

Hello,
If you want to recompile dbms_utility packages then it working well on SQL with sysdba privs user.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter package DBMS_UTILITY compile body;

Package body altered.

Note: After recompile it is showing INVALID then you have to re-create DBMS_UTILITY packages through run CATPROC.SQL scripts.

Anonymous said...

Thank you for putting these all together in one place - I needed the java class syntax; much appreciated.
Hollis

Anonymous said...

dbms_utility.compile_schema compiles objects in alphabetical order. Is there an util, that compiles in dependencies order?

Sedin said...

I use Solomon Yakobson's script to compile objects in dependencies order. Very useful.

Mohammad, thanks for your interesting blog!

Greetz from Poland
Sedin

sonu said...

Hi All,

This above is not working for "synonym". can any one suggest me how to deal with invalid synonym?

Thanks,
Suchi

jasmeet said...

could you plz tel me the oracle how many type of oracle complier have?

jasmeet said...

could you plz tel me how many type of complier required in oracle/
?

jasmeet said...

could you plz tel me the oracle how many type of oracle complier have?