oracle¶¨Ê±ÈÎÎñ£¨dbms_job£©
½ñÌì×ܽáÏÂoracleµÄÈÎÎñ¶ÓÁйÜÀíÆ÷(job queue ),ÒÔºóÒ²·½±ã²éѯ.
ÎÒÃÇÒª×ö¶¨Ê±ÈÎÎñʱ,ÓÐÁ½ÖÖ°ì·¨
Ò»ÖÖÊÇ: ²Ù×÷ϵͳµÄ¶¨Ê±,winµÄ¶¨Ê±ÈÎÎñ,unixµÄcrontab
Ò»ÖÖÊÇ: Êý¾Ý¿â¼¶µÄ¶¨Ê±,ËýµÄЧÂʸü¸ß,
ÔÙÓдóÁ¿µÄ±í¼¶²Ù×÷ʱ,½¨ÒéÓÃÊý¾Ý¿â±¾ÉíµÄjob queue,ÕâÑù·½±ã,ЧÂʸß;Èç¹ûÓÃϵͳ¼¶¶¨Ê±,
»áÔö¼ÓºÜ¶à±à³Ì¹¤×÷,³É±¾Ôö¼ÓÁË,»¹ºÜÈÝÒ׳ö´í,ÊÂÇéÔ½¼òµ¥³ö´íµÄ¼¸ÂÊԽС.
ÔÙʹÓÃjob queue֮ǰ,ÎÒÃÇ»¹Òª¼òµ¥ÅäÖÃÏÂ,oracle¶¨Ê±Ö´ÐÐjob queue µÄºǫ́½ø³ÌÊÇSNP,ÒªÆô¶¯
snp,Ê×ÏÈ¿´ÏµÍ³Ä£Ê½ÊÇ·ñÖ§³Ö
sql> alter system enable restricted session;
»ò
sql> alter system disenable restricted session;
ÀûÓÃÉÏÃæµÄÃüÁî¸ü¸ÄϵͳµÄ»á»°·½Ê½Îªdisenable restricted,ΪsnpµÄÆô¶¯´´½¨Ìõ¼þ.
ÔÙÓоÍÊÇÅäÖÃjob queueµÄÆô¶¯²ÎÊý,snpµÄÆô¶¯²ÎÊýλÓÚoracleµÄ³õʼ»¯ÎļþÖÐ,
job_queue_processes=10 (oracle10gde ĬÈÏÖµ)
job_queue_interval=N
µÚÒ»Ðж¨Òåsnp½ø³ÌµÄÆô¶¯¸öÊýΪ10,Õý³£µÃÅ®¹ÚÒ»·¶Î§ÊÇ0-36,¸ù¾ÝÈÎÎñµÄ¶àÉÙ,¿ÉÒÔÅäÖÃ
²»Í¬µÄÊýÖµ.
µÚ¶þÐж¨Òåϵͳÿ¸ô¼¸Ã뻽ÐѸýø³ÌÒ»´Î.ȱʡÊÇ60,Õý³£·¶Î§ÊÇ1-3600Ãë.ÊÂʵÉÏ£¬¸Ã½ø³ÌÖ´ÐÐÍê
µ±Ç°ÈÎÎñºó£¬¾Í½øÈë˯Ãß״̬£¬Ë¯ÃßÒ»¶Îʱ¼äºó£¬ÓÉϵͳµÄ×ܿظºÔð½«Æä»½ÐÑ¡£
Èç¹û¸ÃÎļþÖÐûÓÐÉÏÃæÁ½ÐУ¬Çë°´ÕÕÈçÉÏÅäÖÃÌí¼Ó¡£ÅäÖÃÍê³Éºó£¬ÐèÒªÖØÐÂÆô¶¯Êý¾Ý¿â£¬Ê¹ÆäÉúЧ
¡£×¢Ò⣺Èç¹ûÈÎÎñÒªÇóÖ´Ðеļä¸ôºÜ¶ÌµÄ»°£¬NµÄÅäÖÃÒ²ÒªÏàÓ¦µØÐ¡Ò»µã¡£
²é¿´job queueµÄÏêϸÐÅÏ¢,²éѯÊý¾Ý¿â×Öµä user_jobs
eg:
sql> select job,next_date,next_sec,broken from user_jobs;
°üº¬ÒÔÏÂ×Ó¹ý³Ì£º
Broken()¹ý³Ì¡£
change()¹ý³Ì¡£
Interval()¹ý³Ì¡£
Isubmit()¹ý³Ì¡£
Next_Date()¹ý³Ì¡£
Remove()¹ý³Ì¡£
Run()¹ý³Ì¡£
Submit()¹ý³Ì¡£
User_Export()¹ý³Ì¡£
What()¹ý³Ì¡£
1¡¢
Broken()¹ý³Ì¸üÐÂÒ»¸öÒÑÌá½»µÄ¹¤×÷µÄ״̬£¬µäÐ͵ØÊÇÓÃÀ´°ÑÒ»¸öÒÑÆÆ¹¤×÷±ê¼ÇÎªÎ´ÆÆ¹¤×÷¡£
Õâ¸ö¹ý³ÌÓÐÈý¸ö²ÎÊý£ºjob ¡¢brokenÓënext_date¡£
PROCEDURE Broken (job IN binary_integer,
Broken IN boolean,
next_dat
Ïà¹ØÎĵµ£º
Oracle³£Ó÷ÖÎöº¯Êý
ROW_NUMBER
·µ»ØÓÐÐò×éÖÐÒ»ÐÐµÄÆ«ÒÆÁ¿£¬´Ó¶ø¿ÉÓÃÓÚ°´Ìض¨±ê×¼ÅÅÐòµÄÐкÅ
row_number() over(partition by ... order by ...)
RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁ ......
¡¡¡¡OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓÃ……
¡¡¡¡OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏàµ±Ó ......
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´ÐУ¬
......
·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄijÖÖ¾ÛºÏÖµ£¬ËüºÍ¾ÛºÏº¯ÊýµÄ²»Í¬Ö®´¦ÊÇ
¶ÔÓÚÿ¸ö×é·µ»Ø¶àÐУ¬¶ø¾ÛºÏº¯Êý¶ÔÓÚÿ¸ö×éÖ»·µ»ØÒ»ÐС£
SQL> select t.empno,t.ename,sum(t.sal)
2 from emp t;
select t.empno,t.ename,sum(t.sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> selec ......
/*
--DML´¥·¢Æ÷ÀàÐÍ:
·ÖΪÓï¾ä¼¶´¥·¢Æ÷ºÍÐм¶´¥·¢Æ÷.
Óï¾ä¼¶´¥·¢Æ÷²»ÐèÒª¼Ófor each row¹Ø¼ü×Ö,²¢ÇÒ²»ÄÜʹÓÃ:new ºÍ :old¹Ø¼ü×Ö.¶ÔÕû¸ösqlÓï¾äÖ»Ö´ÐÐÒ»´Î´¥·¢Æ÷.
Ðм¶´¥·¢Æ÷ÐèÒª¼Ófor each row¹Ø¼ü×Ö,¿ÉÒÔʹÓÃ:newºÍ:old¹Ø¼ü×Ö,¶ÔÕû¸ösqlÓï¾äÓ°Ïìµ½µÄÿһÐж¼Ö´ÐÐÒ»´Î´¥·¢Æ÷.
--:newºÍ:old¹Ø¼ü×Ö×ܽá
:newÖ¸µÄÊǵ±Ç°D ......