Compile another’s Oracle schema
There will be times when you want to compile a schema which does not belong to you,
or for which you do not have the password.
The solution is to use the dbms_utility package as a DBA user.
Example, if you want to recompile all procedures,functions, packages and triggers belonging to scott
here is how to do it:
SQL> exec dbms_utility.compile_schema(schema=>’SCOTT’);
PL/SQL procedure successfully completed.
If you only want to recompile all invalid options, execute it like this :
SQL> exec dbms_utility.compile_schema(schema=>’SCOTT’,compile_all=>false);
PL/SQL procedure successfully completed.
If you want to use each object’s session settings instead of the calling user’s settings:
SQL> exec dbms_utility.compile_schema(schema=>’SCOTT’,compile_all=>false, reuse_settings=>true);
PL/SQL procedure successfully completed.
To see if any errors were generated
(you must run this command immediately after executing dbms_utility) :
SQL> show errors
No errors.
Another way to see if the compilations were successful :
SQL> select object_name, object_type from dba_objects where owner = ‘SCOTT’ and status = ‘INVALID’;
no rows selected
Recommended reading:
Posted: October 23rd, 2009 under Oracle Scripts.
Tags: compile oracle schema, dbms_utility