오라클을 이관 후에는 컴파일을 일괄적으로 해야할 때가 존재한다...
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문장을 생성하여 실행합니다.
참고하세요^^
|
