ORACLE JOBS
³£ÒªoracleÊý¾Ý¿â¶¨Ê±µÄ×Ô¶¯Ö´ÐÐһЩ½Å±¾£¬»ò×öÊý¾Ý¿â±¸·Ý£¬»ò×öÊý¾ÝµÄÌáÁ¶£¬»ò×öÊý¾Ý¿âµÄÐÔÄÜÓÅ»¯£¬°üÀ¨Öؽ¨Ë÷ÒýµÈµÈµÄ¹¤×÷,ÕâʱÐèÒªÓõ½Ò»¸öº¯Êýdbms_job.submit£¬À´Íê³ÉOracle¶¨Ê±Æ÷Jobʱ¼äµÄ´¦ÀíÉÏ¡£Ê¹ÓÃdbms_job.submitÕâ¸öº¯Êý£¬ÎÒÃÇÖ»ÐèÒª¿¼ÂÇÁ½¸öÊÂÇ飺°²ÅÅijһÈÎÎñ£¬ºÍ¶¨ÖÆÒ»¸öÖ´ÐÐÈÎÎñµÄʱ¼äµã¡£µ«×îÖØÒªÒ²ÊÇ×ÊÖµÄÊÂÇ飬ÎÒÈÏΪ»¹ÊÇÈ·¶¨Ò»¸öÖ´ÐÐÈÎÎñµÄʱ¼äµã¡£Ê±¼äµãÈ·¶¨ÁË£¬ÆäËûµÄÊÂÇé¾ÍºÃ°ìÁË¡£ÏÂÃæÊǺ¯Êýdbms_job.submitʹÓ÷½·¨£º
Java´úÂë
dbms_job.submit( job out binary_integer,
what¡¡¡¡¡¡¡¡¡¡¡¡¡¡in¡¡¡¡¡¡archar2,
next_date¡¡¡¡¡¡ ¡¡in¡¡¡¡¡¡date£¬
interval¡¡¡¡¡¡¡¡¡¡in¡¡¡¡¡¡varchar2,
no_parse¡¡¡¡¡¡¡¡¡¡in¡¡¡¡¡¡boolean)
ÆäÖУº
¡ñjob£ºÊä³ö±äÁ¿£¬ÊÇ´ËÈÎÎñÔÚÈÎÎñ¶ÓÁÐÖеıàºÅ£»
¡ñwhat£ºÖ´ÐеÄÈÎÎñµÄÃû³Æ¼°ÆäÊäÈë²ÎÊý£»
¡ñnext_date£ºÈÎÎñÖ´ÐеÄʱ¼ä£»
¡ñinterval£ºÈÎÎñÖ´ÐеÄʱ¼ä¼ä¸ô¡£
ÆäÖÐIntervalÕâ¸öÖµÊǾö¶¨JobºÎʱ£¬±»ÖØÐÂÖ´ÐеĹؼü£»µ±intervalÉèÖÃΪnullʱ£¬¸ÃjobÖ´ÐнáÊøºó£¬¾Í±»´Ó¶ÓÁÐÖÐɾ³ý¡£¼ÙÈçÎÒÃÇÐèÒª¸ÃjobÖÜÆÚÐÔµØÖ´ÐУ¬ÔòÒªÓÑsysdate£«m’±íʾ¡£ÈçºÎ¸üºÃµØÈ·¶¨Ö´ÐÐʱ¼äµÄ¼ä¸ôÐèÒªÎÒÃÇÕÆÎÕÒ»¸öº¯ÊýTRUNC¡£
1.TRUNC(for dates)
TRUNCº¯ÊýΪָ¶¨ÔªËضø½ØÈ¥µÄÈÕÆÚÖµ¡£
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏ£º
TRUNC£¨date[,fmt]£©
ÆäÖУº
date Ò»¸öÈÕÆÚÖµ
fmt ÈÕÆÚ¸ñʽ£¬¸ÃÈÕÆÚ½«ÓÉÖ¸¶¨µÄÔªËظñʽËù½ØÈ¥¡£ºöÂÔËüÔòÓÉ×î½üµÄÈÕÆÚ½ØÈ¥
ÏÂÃæÊǸú¯ÊýµÄʹÓÃÇé¿ö£º
1)°´Äê½Øβ
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'yyyy') from dual
-----------------------------------------------------------
2008-1-1
2)°´Ô½Øβ
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mm') from dual
--------------------------------------------------------
2008-3-1
3)°´ÈÕ½Øβ
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'dd') from dual
----------------------------------------------------------------------
2008-3-1
4)°´Ê±½Øβ
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'hh') from dual
-----------------------------
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
1¡¢±àдĿµÄ
ʹÓÃͳһµÄÃüÃûºÍ±àÂë¹æ·¶£¬Ê¹Êý¾Ý¿âÃüÃû¼°±àÂë·ç¸ñ±ê×¼»¯£¬ÒÔ±ãÓÚÔĶÁ¡¢Àí½âºÍ¼Ì³Ð¡£
2¡¢ÊÊÓ÷¶Î§
±¾¹æ·¶ÊÊÓÃÓÚ¹«Ë¾·¶Î§ÄÚËùÓÐÒÔORACLE×÷Ϊºǫ́Êý¾Ý¿âµÄÓ¦ÓÃϵͳºÍÏîÄ¿¿ª·¢¹¤×÷¡£
3¡¢¶ÔÏóÃüÃû¹æ·¶
3.1 Êý¾Ý¿âºÍSID
Êý¾Ý¿âÃû¶¨ÒåΪϵͳÃû+Ä£¿éÃû
¡ï È«¾ÖÊý¾Ý¿âÃûºÍÀý³ÌSID ÃûÒªÇóÒ»ÖÂ
¡ï ÒòSID ......
¡¡¡¡±¾ÎĽéÉÜÁËÔÚOracleÊý¾Ý¿âÖУ¬¶ÔÈÕÆÚ¡¢Ê±¼äµÄ¸÷ÖÖ²Ù×÷£¬°üÀ¨£ºÈÕÆÚʱ¼ä¼ä¸ô²Ù×÷¡¢ÈÕÆÚµ½×Ö·û²Ù×÷¡¢×Ö·ûµ½ÈÕÆÚ²Ù×÷¡¢trunk / ROUNDº¯ÊýµÄʹÓᢺÁÃ뼶µÄÊý¾ÝÀàÐ͵ȡ£
1.ÈÕÆÚʱ¼ä¼ä¸ô²Ù×÷
¡¡¡¡µ±Ç°Ê±¼ä¼õÈ¥7·ÖÖÓµÄʱ¼ä
¡¡¡¡select sysdate,sysdate - interval '7' MINUTE from dual
¡¡¡¡µ±Ç°Ê±¼ä¼õÈ¥7СʱµÄʱ¼ä
¡¡¡ ......
ÒÔÇ°»¹×¨ÃÅС×ܽá¹ýÒ»ÏÂORACLEÖйØÓÚNULLµÄһЩÎÊÌ⣬ÅöÇɽñÌìÔÚ¿´ÊéµÄ¹ý³ÌÖÐÓÖ¿´µ½ÁËÁíÍâÒ»¸öÒÔǰû·¢ÏÖµÄÐèҪעÒâµÄµØ·½£¬ÄǾÍÊÇÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ¡£
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- ---- ......