1) 일단 초기화 파라미터에
job_queue_processes 를 1이상 설정한다.
2) job등록
variable jobno number
begin
dbms_job.submit(:jobno, 'a;',sysdate, 'Trunc(sysdate, ''MI'') + (1/(24*60))',FALSE);
commit;
end;
/
위에 예제의 경우 a 라는 프로시져를 1분간격으로 실행하는 job를 등록한 예제 입니다.
select * from user_jobs를 보시면 해당 job이 등록되어 있을 겁니다.
자세한 내용은 오라클 블루틴이나 document의 dbms_job패키지 spec을 살펴보시기 바립니다.
**** job_queue_processes 변경 방법****
1. sqlplus "/as sysdba" 로 접속
2. show parameter job_queue_parameter 로 현재값을 확인
3. alter session 명령으로 변경(예, 10으로 변경할때)
>alter system set job_queue_parameter=10;
4. show parameter job_queue_parameter 로 변경된 값을 확인
5. DB 재시작시에도 변경이 적용되게 하기위해서는 iniXXX.ora 파일도
변경해야 함...
6. iniXXX.ora 파일 백업
7. vi로 편집
*.aq_tm_processes=1
*.background_dump_dest='/users/app/oracle/admin/HCCHR/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/users/app/oracle/oradata/HCCHR/control01.ctl','/extra1/oradata/control02.ctl','/extra2/oradata/control03.ctl'
*.core_dump_dest='/users/app/oracle/admin/HCCHR/cdump'
*.db_block_size=8192
*.db_cache_size=943718400
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='HCCHR'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HCCHRXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='HCCHR'
*.java_pool_size=104857600
*.job_queue_processes=10
*.large_pool_size=16777216
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=419430400
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/users/app/oracle/admin/HCCHR/udump'
*.background_dump_dest='/users/app/oracle/admin/HCCHR/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/users/app/oracle/oradata/HCCHR/control01.ctl','/extra1/oradata/control02.ctl','/extra2/oradata/control03.ctl'
*.core_dump_dest='/users/app/oracle/admin/HCCHR/cdump'
*.db_block_size=8192
*.db_cache_size=943718400
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='HCCHR'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HCCHRXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='HCCHR'
*.java_pool_size=104857600
*.job_queue_processes=10
*.large_pool_size=16777216
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=419430400
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/users/app/oracle/admin/HCCHR/udump'
8. 저장후 닫기
9. DB 재시작