Compile oracle schema steps
before implementing it on a production system !
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
