ÕÆÎÕµÄOracle¶¨Ê±ÈÎÎñ
Ò»¡¢ÔÚPLSQLÖд´½¨±í£º
create table HWQY.TEST
(
CARNO VARCHAR2(30),
CARINFOID NUMBER
)
¶þ¡¢ÔÚPLSQLÖд´½¨´æ´¢¹ý³Ì£º
create or replace procedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id
from dual;
insert into test(test.carno,test.carinfoid) values(carinfo_id,''123'');
commit;
end pro_test;
Èý¡¢ÔÚSQLÃüÁî´°¿ÚÖÐÆô¶¯ÈÎÎñ£º
ÔÚSQL>ºóÖ´ÐУº
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
''pro_test;'',
SYSDATE,''sysdate+1/24/12'');
commit;
end;
/
Ìá½»ºóÌáʾ£º
Ó¢ÎÄ´úÂë
ËÄ¡¢¸ú×ÙÈÎÎñµÄÇé¿ö(²é¿´ÈÎÎñ¶ÓÁÐ)£º
SQL> select job,next_date,next_sec,failures,broken from user_jobs;
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1 2008-2-22 ?01:00:00 0 N
˵Ã÷ÓÐÒ»¸öÈÎÎñ´æÔÚÁË¡£
Ö´ÐÐselect * from test t²é¿´¶¨Ê±ÈÎÎñµÄ½á¹û¡£¿ÉÒÔ¿´³ö¶¨Ê±ÈÎÎñÊÇÕý³£Ö´ÐÐÁ˵ġ£
Î塢ֹͣÒѾÆô¶¯µÄ¶¨Ê±ÈÎÎñ:
ÏÈÖ´ÐÐselect job,next_date,next_sec,failures,broken from user_jobs;
ÒԲ鿴¶¨Ê±ÈÎÎñµÄjobºÅ¡£
ÔÚSQL>ÖÐÖ´ÐÐÏÂÃæµÄÓï¾äÍ£Ö¹Ò»¸öÒѾÆô¶¯µÄ¶¨Ê±ÈÎÎñ£º
begin
dbms_job.remove(1);
commit;
end;
/
±íʾֹͣjobΪ1µÄÈÎÎñ¡£
Ö´ÐкóÏÔʾÈçÏ£º
PL/SQL procedure successfully completed
Áù¡¢²é¿´½ø³ÌÊý£º
show parameter job_queue_processes;
±ØÐë>0£¬·ñÔòÖ´ÐÐÏÂÃæµÄÃüÁîÐ޸ģº
alter system set job_queue_processes=5;
Æß¡¢ÔÙ´´½¨Ò»¸öÈÎÎñ(ÿ5·ÖÖÓÖ´ÐÐÒ»´Î)£º
variable jobno number;
begin
dbms_job.submit(:jobno, ''pro_test;'',
sysdate,''sysdate+1/24/12'');
commit;
end;
/
½¨Á¢Ò»¸ö¶¨Ê±ÈÎÎñºó£¬ÔÚPLSQLÖв鿴JOB£¬ËüµÄsqlÓï¾äÀàËÆµÄÊÇÈçÏ£º
begin
sys.dbms_job.submit(job => :jobno,
what => ''pro_test;'',
next_date => to_date(''21-02-2008 17:37:26'', ''dd-mm-yyyy hh24:mi:ss''),
interval => ''sysdate+1/24/12'');
commit;
end;
/
ËùÒÔ£¬´´½¨Ò»¸öÈÎÎñµÄÍêÕûµÄ¸ñʽÊÇ£º
variable jobno number;
begin
Ïà¹ØÎĵµ£º
ÔÚÒ»°ãµÄPL/SQL³ÌÐò¿ª·¢ÖУ¬¿ÉÒÔʹÓÃSQLµÄDMLÓï¾äºÍÊÂÎñ¿ØÖÆÓï¾ä£¬µ«ÊÇDDLÓï¾ä¼°»á»°Óï¾äÈ´²»ÄÜÔÚPL/SQLÖÐÖ±½ÓʹÓã¬ÒªÏëʵÏÖÔÚPL/SQLÖÐʹÓÃDDLÓï¾ä¼°»á»°¿ØÖÆÓï¾ä£¬¿ÉÒÔͨ¹ý¶¯Ì¬SQLÀ´ÊµÏÖ¡£
Ëùν¶¯Ì¬SQLÊÇÖ¸ÔÚPL/SQL¿é±àÒëʱSQLÓï¾äÊDz»È·¶¨µÄ£¬ÀýÈç¸ù¾ÝÓû§ÊäÈë²ÎÊýµÄ²»Í¬¶ø ......
ÔÚ°²×°oracleµÄDBCA¹ý³ÌÖУ¬ÔÚ´´½¨²¢Æô¶¯oracleʵÀý£¨´´½¨¿Ë¡Êý¾Ý¿â£¬½ø¶ÈÌõÔÚ45%£©¾Í½øÐв»ÏÂÈ¥ÁË£¬ÖØ×°Á˼¸´ÎÖÕÓÚÕÒµ½ÎÊÌâµÄÖ¢½áÁË£¬½â¾ö¹ý³ÌÈçÏ£º
1¡¢×°Á˼¸±é¾ÍÊÇÄǸöÎÊÌ⣬ÎÒͻȻÏëµ½Òª¿´¿´°²×°ÈÕÖ¾¡£
2¡¢ÊÇÔÚ´´½ ......
ÎÄÕÂÌáµ½£¬ÓÃCache hit ratioµÄ·½·¨À´¼ì²éORACLEÐÔÄÜÎÊÌâ¹ýʱÁË£¬ÀïÃæÓоä·Ç³£ÐÎÏóµÄ»°£ºÕâÎÞÒìÓÚÒ»¸öÒ½ÉúÖ»ÖªµÀ¸ù¾ÝѪѹµÄÀ´ÖÎÁƲ¡ÈË£¬¶ø²¡ÈËÓÉÓÚÌÛÍ´£¬Éñ¾ÐË·Ü£¬ÑªÑ¹ÔÚÒ»¸öºÏÀíµÄ·¶Î§ÄÚ£¬Ò½ÉúÈ´¸æËß»¼Õߣ¬Äãû²¡£¬µÈÄãѪѹµÍµÄʱºòÔÙÀ´¡£Í¬ÑùµÄ£¬ÎÒÃDz»Äܽö½ö¸ù¾ÝCache hit ratioÀ´ÅжÏORACLEÊ ......
for example:
15:18:59 SQL> create table t1 (a int, b int);
±íÒÑ´´½¨¡£
ÒÑÓÃʱ¼ä: 00: 00: 00.15
......