Re-Compiling ALL INVALID objects in your Oracle database
This script will re-compile all invalid objects of all schemas in yuor database. This is quite powerfull script as it will try to re-compile everything in the database that is invalid. So you must need to run this script as a highly priviledged user (such as SYS or SYSTEM or a user with DBA role)
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE HR.CALLPROC COMPILE;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
Note above that I am using the schema name before the object name e.g. HR.CALLPROC). This is to make sure that we are re-compile the invalid object in its own schema. The list file templist.sql will be run against the database to run all these commands in one go.
Here is the script:
Re-Compiling INVALID objects in own (users) schema
Here is a very handy script which you can use to re-compile all the invalid objects in your schema.
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE CALL COMPILE;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
Then the list file templist.sql will be run against the database to run all this commands in one go. Which means you just need to run this one script to re-compile all your invalid objects in your schema. Note that I am using here USER_OBJECTS – which is a VIEW owned by SYS user. Continue reading
Recent Comments