oracle jobµÄ¼ò½éºÍʵÀý

Ô­ÎĵØÖ·£ºhttp://guyuanli.itpub.net/post/37743/484763
ÿÌì1µãÖ´ÐеÄoracle JOBÑùÀý
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job =>
X,
what => 'ETL_RUN_D_Date;',
next_date => to_date('2009-08-26
01:00:00','yyyy-mm-dd hh24:mi:ss'),
interval =>
'trunc(sysdate)+1+1/24',
no_parse =>
FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' ||
to_char(x));
COMMIT;
END;
/
ÒÔÉÏÊÇÃ÷È·Ö¸¶¨Ã¿ÌìµÄ1µãÖ´ÐдËjob,Èç¹ûÖ¸¶¨ÊÇÿÌìÖÐÎç12µãÖ´ÐÐintervalÐèÒªÖ¸¶¨Îª'trunc(sysdate)+1+12/24',Èç¹û½ö½öÖ¸¶¨intervalΪһÌì,ÕâÑùµ±ÄãÊÖ¹¤ÓÃdbms_job.run(job)È¥ÔËÐÐÒ»´Îʱ,jobÿÌìµÄÖ´ÐÐʱ¼äÊÇ»á¸Ä±äµÄ,Èç¹ûÄãÏëjobÿÌìÔڹ̶¨Ê±¼äÖ´ÐÐ,¿ÉÒԲο¼ÉÏÃæµÄÀý×Ó.
³õʼ»¯Ïà¹Ø²ÎÊýjob_queue_processes
alter system set job_queue_processes=39
scope=spfile;//×î´óÖµ²»Äܳ¬¹ý1000 ;job_queue_interval = 10 //µ÷¶È×÷ҵˢÐÂƵÂÊÃëΪµ¥Î»
job_queue_process ±íʾoracle
Äܹ»²¢·¢µÄjobµÄÊýÁ¿£¬¿ÉÒÔͨ¹ýÓï¾ä¡¡¡¡
show parameter job_queue_process;
À´²é¿´oracleÖÐjob_queue_processµÄÖµ¡£µ±job_queue_processֵΪ0ʱ±íʾȫ²¿Í£Ö¹oracleµÄjob£¬¿ÉÒÔͨ¹ýÓï¾ä
ALTER SYSTEM SET job_queue_processes = 10;
À´µ÷ÕûÆô¶¯oracleµÄjob¡£
Ïà¹ØÊÓͼ£º
dba_jobs
all_jobs
user_jobs
dba_jobs_running
°üº¬ÕýÔÚÔËÐÐjobÏà¹ØÐÅÏ¢
£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­
Ìá½»jobÓï·¨£º
begin
sys.dbms_job.submit(job => :job,
what =>
'P_CLEAR_PACKBAL;',
next_date => to_date('04-08-2008 05:44:09',
'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+
1/360');
commit;
end;
/
£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­
´´½¨JOB
variable jobno
number;
begin
dbms_job.submit(:jobno,
'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);
commit;
ÔËÐÐJOB
SQL> begin
dbms_job.run(:job1);
end;
/
ɾ³ýJOB
SQL> begin
dbms_job.remove(:job1);
end;
/
DBA_JOBS
£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½
×ֶΣ¨ÁУ© ÀàÐÍ ÃèÊö
JOB
NUMBER ÈÎÎñµÄΨһ±êʾºÅ
LOG_USER VARCHAR2(30) Ìá½»ÈÎÎñµÄÓû§
PRIV_USER VARCHAR2(30)
¸³ÓèÈÎÎñȨÏÞµÄÓû§
SCHEMA_USER VAR