How to compile the entire oracle schema?

Objects which are seem to be not compiled are said to be as ‘Invalid’.
So to compile such objects we need to alter there status as ‘Valid’ , indirectly oracle server compiles those objects to set there status as ‘Valid’.

Below sql statement lists out all the objects that are invalid and relevant sql query to make them valid or compile those objects.

Query :-

select 'alter ' || decode(object_Type,'PACKAGE BODY','PACKAGE',object_Type) || ' ' || object_name || ' compile;' from user_objects where status='INVALID';


So we can spool this result set to a sql file using a batch file, then running the spooled file compiles all the invalid objects or un compiled objects.

Batch File :


@echo off
setlocal
set /p schema=Please enter your TNSname:
set /p username=Please enter your Schema Username:
set /p password=Please enter your Schema Password:
sqlplus %username%/%password%@%schema% @Script1.sql
sqlplus %username%/%password%@%schema% @Script2.sql



Where here script1.sql consists of the above mentioned query and spools the result set to scrit2.sql.

Script1.sql :


set heading off
set linesize 1500
set pagesize 1000;
set feedback off
spool Script2.sql
select 'alter ' || decode(object_Type,'PACKAGE BODY','PACKAGE',object_Type) || ' ' || object_name || ' compile;' from user_objects where status='INVALID';
select ‘commit;’ from dual;
select ‘exit;’ from dual;
spool off
exit;


So script2.sql consists the relevant queries that compile those UN complied oracle objects.
Also batch files execute those statements from script2.sql.



After running batch file it asks for the database schema name, user name and password to log in through sql plus.
Output after running the batch file :