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
14 comments:
Can I compile dbms_utility in sys schema
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]
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
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.
Thank you for putting these all together in one place - I needed the java class syntax; much appreciated.
Hollis
dbms_utility.compile_schema compiles objects in alphabetical order. Is there an util, that compiles in dependencies order?
I use Solomon Yakobson's script to compile objects in dependencies order. Very useful.
Mohammad, thanks for your interesting blog!
Greetz from Poland
Sedin
Hi All,
This above is not working for "synonym". can any one suggest me how to deal with invalid synonym?
Thanks,
Suchi
could you plz tel me the oracle how many type of oracle complier have?
could you plz tel me how many type of complier required in oracle/
?
could you plz tel me the oracle how many type of oracle complier have?
Hello it's me, I am also visiting this site on a regular basis, this website is truly pleasant and the users are genuinely sharing fastidious thoughts.
Have a look at my web page - Androsolve Testosterone Booster
I do not even know how I stopped up here, but I believed this post used to be
great. I don't recognise who you are but certainly you're going to a well-known blogger in case you
aren't already. Cheers!
Also visit my web page ... pure garcinia cambogia
I visit every day a few web sites and sites to read articles, except this webpage provides quality based posts.
My web-site; home jobs
Post a Comment