반응형


오라클을 이관 후에는 컴파일을 일괄적으로 해야할 때가 존재한다...

1. 일괄 컴파일

 

Set heading off      
Set feedback off      
Set pages 1000      
 
 spool obj.sql
 select 'set termout on' from dual;
 select 'set echo on' from dual;      
      
 select 'alter trigger '||owner||'.'||object_name||' compile;'
 from dba_objects
 where status <> 'VALID'
 and object_type='TRIGGER';
     
 select 'alter package '||owner||'.'||object_name||' compile;'      
 from dba_objects      
 where status <> 'VALID'       
 and object_type='PACKAGE';      

 select 'alter package '||owner||'.'||object_name||' compile body;'     
 from dba_objects      
 where status <> 'VALID'       
 and object_type='PACKAGE BODY';

 select 'alter procedure '||owner||'.'||object_name||' compile;'       
 from dba_objects      
 where status <> 'VALID'       
 and object_type='PROCEDURE';      

 select 'alter function '||owner||'.'||object_name||' compile;'
 from dba_objects      
 where status <> 'VALID'
 and object_type='FUNCTION';

 select 'alter view '||owner||'.'||object_name||' compile;'       
 from dba_objects      
 where status <> 'VALID'       
 and object_type='VIEW';       
 
 spool off

[출처] [Oracle] 데이타베이스 한꺼번에 Compile하기|작성자 미친예언자




2. 일괄 컴파일

 host rm -rf comp1.sql comp2.sql
set pages 300
select count(1) as INVALID_OBJECT_COUNT from dba_objects where status !='VALID';
set pages 0
set line 1000
set echo off
set feedback off
set space 0
col compile for a1000
select systimestamp as start_time from dual;
spool comp1.sql
select 'prompt '||object_type||' '||object_name||' compiling'||'
prompt '||'
alter '||object_type||' '||owner||'.'||object_name||' compile;' as compile
from dba_objects
where status !='VALID'
and object_type !='PACKAGE BODY';
spool off
host ls -al comp1.sql
host echo "select systimestamp from dual;" >> comp1.sql
start comp1.sql
show error
spool comp2.sql
select 'prompt '||object_type||' '||object_name||' compiling'||'
prompt '||'
alter package '||owner||'.'||object_name||' compile body;' as compile
from dba_objects
where status !='VALID'
and object_type ='PACKAGE BODY';
spool off
host ls -al comp2.sql
host echo "select systimestamp from dual;" >> comp2.sql
start comp2.sql
show error
set echo on
set feedback on
set pages 3000
set space 1
col owner for a20
col object_name for a28
col object_type for a20
col status for a16
select owner,object_name,object_type,status from dba_objects
where status !='VALID';
select count(1) INVALID_OBJECT_COUNT from dba_objects where status !='VALID';
select systimestamp as end_time from dual;


3. 일괄 컴파일

 1. SQL> @?/rdbms/admin/utlrp.sql

2. utlrp를 parallel하게...
execute utl_recomp.recomp_parallel(4);

3. 만약 ERP라면...위의 두개로 해결이 안될때에는...
cd $AD_TOP/sql/adcompsc를 사용합니다.
adcompsc <== 이게 산타님 말씀하신것 처럼 alter문장을 생성하여 실행합니다.
참고하세요^^

반응형

+ Recent posts