Oracle SQL Script to Compile All Package, Body in a Schema

This script will compile all the Package and package body in the schema. If you wanted to compile all the packages in the all schema, change the user_objects to dba_objects and you have to connect as a DBA privileged user.

  1. Create a sql file and copy below script.
  2. Connect to sqlplus with the username where you wanted to compile all packages.

SET PAGES 0
SET LINES 120
SET FEEDBACK OFF
SET ECHO OFF
SPOOL COMP_PACKAGE.sql
SELECT 'SET PAGES 0 LINES 120 FEEDBACK ON ECHO ON' FROM DUAL;
SELECT 'SPOOL COMP_PACKAGE.log' from dual;
select 'alter package '||object_name||decode(object_type,'PACKAGE',' compile;',' compile body;')
from   user_objects
where   object_type in ('PACKAGE','PACKAGE BODY')
order by object_type;
SELECT 'SPOOL OFF' from dual;
SPOOL OFF
@COMP_PACKAGE.sql
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google