oracle µÄDMLÃüÁîµÄÏêϸ´¦Àí¹ý³Ì
ÔÎĵØÖ·£ºhttp://book.csdn.net/bookfiles/732/10073222578.shtml
¶ÔÓÚDMLÓï¾äÀ´Ëµ£¬Ö»ÒªÐÞ¸ÄÁËÊý¾Ý¿é£¬OracleÊý¾Ý¿â¾Í»á½«ÐÞ¸ÄǰµÄÊý¾Ý±£ÁôÏÂÀ´£¬±£´æÔÚundo segmentÀ¶øundo segmentÔò±£´æÔÚundo±í¿Õ¼äÀï¡£´ÓOracle 9iÆð£¬ÓÐÁ½ÖÖundoµÄ¹ÜÀí·½Ê½£º×Ô¶¯Undo¹ÜÀí£¨Automatic Undo Management£¬¼ò³ÆAUM£©ºÍÊÖ¹¤Undo¹ÜÀí£¨Manual Undo Management£¬¼ò³ÆMUM£©¡£Oracle 9i֮ǰֻÄÜʹÓÃMUM£¬¶øÇÒÔÚMUMÖУ¬undo segmentÓÖ½Ð×örollback segment¡£´ÓOracle 9iÆð£¬Oracle¾Í½¨ÒéʹÓÃAUM£¬¶ø²»Ó¦ÔÙʹÓÃMUMÁË¡£
7.1 DMLÓï¾äÓëundo
µ±ÎÒÃÇ·¢³öÒ»ÌõDML£¨±ÈÈçupdate t set col1='A' where col1='B'£©Óï¾äʱ£¬ÆäÖ´Ðйý³Ì¿É´óÖ¸ÅÀ¨ÎªÒÔϼ¸²½¡£
1. ÔÚshared poolÀï½øÐнâÎö£¬´Ó¶øÉú³ÉÖ´Ðмƻ®¡£¾ßÌå½âÎö¹ý³Ì¼ûµÚ5Õ¡£
2.¼ÙÉè¸ù¾ÝÖ´Ðмƻ®£¬µÃ³öcol1='B'µÄ¼Ç¼´æ·ÅÔÚ10ºÅÊý¾ÝÎļþµÄ54ºÅÊý¾Ý¿éÀï¡£
3. ·þÎñÆ÷½ø³ÌÔÚbuffer cacheÀïÕÒÒ»¸ö¿ÉÓõÄundoÊý¾Ý¿é£¬Èç¹ûûÓз¢ÏÖ£¬Ôòµ½undo±í¿Õ¼äÀïÕÒÒ»¸ö¿ÉÓõÄundo¿é£¬²¢µ÷Èëbuffer cache¡£¼ÙÉè»ñµÃµÄundoÊý¾Ý¿éºÅΪ24ºÅ£¬Î»ÓÚ11ºÅundoÊý¾ÝÎļþÀï¡£
4. ½«¸Ä±äǰµÄÖµ£¬Ò²¾ÍÊÇA·ÅÈë11ºÅundoÊý¾Ý¿é¡£
5. ÓÉÓÚundoÊý¾Ý¿é·¢ÉúÁ˱仯£¬ÓÚÊDzúÉúÖØ×ö¼Ç¼£¬¼ÙÉèÖØ×ö¼Ç¼ºÅΪ120¡£
ÐкŠÊÂÎñid file# block# row column value
120 T1 24 11 10 col1 A
6.ÔÚbuffer cacheÀïÕÒµ½54ºÅÊý¾Ý¿é¡£Èç¹ûûÓз¢ÏÖ£¬Ôò´Ó10ºÅÊý¾ÝÎļþÀïµ÷Èë¡£
7. ½«¸Ä±äºóµÄÖµ£¬Ò²¾ÍÊÇB·ÅÈë54ºÅÊý¾Ý¿é¡£
8. ÓÉÓÚÊý¾Ý¿é·¢ÉúÁ˱仯£¬ÓÚÊDzúÉúÖØ×ö¼Ç¼£¬¼ÙÉèÖØ×ö¼Ç¼ºÅΪ121¡£
ÐкŠÊÂÎñid file# block# row column value
121 T1 10 54 10 col1 &n
Ïà¹ØÎĵµ£º
Oracle Enterprise Manager Oracle EMÊÇÓû§°²×°Oracle£¬´´½¨ÁËÊý¾Ý¿â²¢ÅäÖúÃÍøÂç×ÊÔ´ºó£¬Oracle¸½´ø°²×°µÄÒ»¸öÊý¾Ý¿â¹ÜÀíÈí¼þ¡£Í¬Ê±£¬EMÒ²Ìṩ¶ÔÐÔÄܵ÷ÊÔÓаïÖúµÄ¹¤¾ß£¬ÈçSQL*LoaderºÍRecovery Manager £¨RMAN£©µÈ¡£ Oracle Diagnostics Pack (ODP) ODPÓ¦¸ÃËãÊÇOracleÊý¾Ý¿â»·¾³ÐÔÄܵ÷ÊÔµÄ×îÖ÷ÒªºÍ×îÓÐЧµÄ¹¤¾ß£¬ËüÌá¹ ......
--ÔÚÓ¦ÓóÌÐòÖÐÍùÍùÓÐЩ¹Ì¶¨µÄÊý¾Ý¿â²Ù×÷£¬ÎÒÃdz£³£ÎªÖÂл¹Ì¶¨µÄ²Ù×÷¶¨ÒåÁËһЩ´æ´¢¹ý³Ì¡£ÕâЩ´æ´¢¹ý³Ì²»½ö
--¿ÉÒÔ¼ò»¯¿Í»§¶ËµÄÓ¦ÓóÌÐòµÄ¿ª·¢ºÍά»¤¡£¶øÇÒ»¹¿ÉÒÔÌá¸ßÓ¦ÓõÄÔËÐÐÐÔÄÜ¡£
-- ´´½¨´æ´¢¹ý³ÌµÄÓï·¨¡£
create [or replace] procedure <¹ý³ÌÃû>[(²ÎÊý)]
is|as
[¾Ö²¿±äÁ¿ÉùÃ÷]
begin
......
--´´½¨´¥·¢Æ÷£¨Ðм¶´¥·¢Æ÷£©
create or replace trigger tri_update_emp_bak
after update
on emp_bak
for each row --ÿ¸üÐÂÒ»ÐÐ ¾Í´¥·¢Ò»´Î
begin
--oracle ÀïÃæ ¶Ô´¥·¢Æ÷ Ò²ÌṩÁËÌØÊâµÄ¶ÔÏó :NEW :OLD À´·ÃÎÊ ¸üÐÂǰºóµÄÊý¾Ý
dbms_output.put_line('¸üкó' || :NEW.sal);
dbms_outpu ......
×÷ÕߣºÒ×ÎÄ¿¡
ÓÊÏ䣺yiwenjun@126.com
Ê×ÏÈÒª°ÑOracle µÄ¼àÌýºÍ·þÎñÁ½Ïî´ò¿ª
´ò¿ªodbcÊý¾ÝÔ´ÅäÖýçÃæ,Ñ¡ÔñϵͳDSN£¬Ìí¼Ó£¬Ñ¡ÔñOracleÇý¶¯³ÌÐò
¹Ø¼üÊÇÏÂÃæ²ÎÊýµÄÉèÖÃ
Data source name:×Ô¼ºÎª¸ÃÊý¾ÝÔ´ÃüµÄÃû
description:Ëæ±ã×Ô¼ºÃèÊöÀ²
TNS server name:ÏÂÀ²Ëµ¥ÓУ¨Èç¹ûûÓеϰ£¬¾Í×Ô¼ºÔÚnet configuration assist ......
ORACLE SQLÐÔÄÜÓÅ»¯
ORACLE SQLÐÔÄÜÓÅ»¯ÏµÁÐ (Ò»)
1. Ñ¡ÓÃÊʺϵÄORACLEÓÅ»¯Æ÷
ORACLEµÄÓÅ»¯Æ÷¹²ÓÐ3ÖÖ:
a. RULE (»ùÓÚ¹æÔò) b. COST (»ùÓڳɱ¾) c. CHOOSE (Ñ¡ÔñÐÔ)
ÉèÖÃȱʡµÄÓÅ»¯Æ÷,¿ÉÒÔͨ¹ý¶Ôinit.oraÎļþÖÐOPTIMIZER_MODE²ÎÊýµÄ¸÷ÖÖÉùÃ÷,ÈçRULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . Ä㵱ȻҲÔÚSQ ......