asked on

How to compile all packages - Is a single command available in ora8

Fairly simple question.

Ive a need to compile/recomplile all pl/sql packages under a single user in Oracle 8. How can I do it.

I know I can extract out each package name etc, and create a file of compile <package>

but is there a command that when issued, will go away for a few hours and compile all packages/pocedures

like

compile all_packages <user>

ASKER CERTIFIED SOLUTION

hayrabedian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Stanley

something like this will work as well, and will not take hours; more like minutes.

open sql*plus prompt:

set pagsize 0
spool compile_pkg.run
select 'alter package ' || owner || '.'|| object_name || ' compile;'
from all_objects
where object_type = 'PACKAGE'
and owner = '&OWNER';
spool off
@compile_pkg.run

good luck,
daniels@asix.com

hayrabedian

if we go into details of this we have to acheave objects dependencies. So, in the common it is not as simple as drs66 said, but sometimes it works :)

Ammar

Apply the below SQL commands ..

SET HEADING OFF;
SET LONG 1000;
SET LINESIZE 1000;

SPOOL C:\Recompile.Sql

select 'alter '||decode(object_type, 'PACKAGE BODY', 'package', object_type) ||' '||OWNER||'.'||object_name||' compile' ||decode(object_type, 'PACKAGE BODY', ' body;', ';')
from dba_objects
where status = 'INVALID'
order by object_type,object_name;

spool off;
Start C:\Recompile.Sql
host del C:\T03.SQL

Thanx
Ammar

gamatt

Or to recompile all packages in the database use $ORACLE_HOME/rdbms/admin/utlrp.sql