Oracle ²ÎÊýÎļþ×ܽá
1:pfileºÍspfile
ÔÚ9i֮ǰ£¬²ÎÊýÎļþÖ»ÓÐÒ»ÖÖ£¬ËüÊÇÎı¾¸ñʽµÄ£¬³ÆÎªpfile£¬ÔÚ9i¼°ÒÔºóµÄ°æ±¾ÖУ¬ÐÂÔöÁË·þÎñÆ÷²ÎÊýÎļþ,³ÆÎªspfile,ËüÊǶþ½øÖƸñʽµÄ¡£ÕâÁ½ÖÖ²ÎÊýÎļþ¶¼ÊÇÓÃÀ´´æ´¢²Î ÊýÅäÖÃÒÔ¹©oracle¶ÁÈ¡µÄ£¬µ«Ò²Óв»Í¬µã£¬×¢ÒâÒÔϼ¸µã£º
1)pfileÊÇÎı¾Îļþ£¬spfileÊǶþ½øÖÆÎļþ£»
2)¶ÔÓÚ²ÎÊýµÄÅäÖã¬pfile¿ÉÒÔÖ±½ÓÒÔÎı¾±à¼Æ÷´ò¿ªÊÖ¹¤ÅäÖ㬶øspfile²»ÐУ¬±ØÐëÔÚÊý¾Ý¿âÆô¶¯ºó£¬Í¨¹ýsqlÃüÁî½øÐÐÔÚÏßÐ޸쬵±È»spfileÒ²¿ÉÓÃÎı¾±à¼Æ÷´ò¿ª£¬°ÑÀïÃæÎı¾µÄÄÚÈݸ´ÖƳöÀ´·ÅÔÚpfileÖУ¬µ«¾ö¶Ô²»¿ÉÒÔÖ±½ÓÐ޸ģ¬Ö±½ÓÐ޸ĺó»áʹÎļþÎÞ·¨¶ÁÈ¡¡£
3)pfileÅäÖøıäºó£¬ÒªÊ¹ÓÃÆäÉúЧ£¬±ØÐëÖØÐÂÆô¶¯Êý¾Ý¿â£¬spfileµÄÅäÖÃÉúЧʱÏÞºÍ×÷ÓÃÓò¿ÉÒÔÓÉÐ޸IJÎÊýµÄsqlÃüÁîÖ¸¶¨£¬¿ÉÒÔÁ¢¼´ÉúЧ£¬Ò²¿ÉÒÔ²»Á¢¼´ÉúЧ¡£µ±È»ÓÐЩ²ÎÊýµÄÐ޸ıØÐëÖØÆôÊý¾Ý¿â²ÅÄÜÉúЧ£»
4)¿ÉÒÔÓÃsqlÃüÁîÓÉpfile´´½¨spfile,Ò²¿ÉÒÔÓÉspfile´´½¨pfile£»
5)Èç¹ûÊÇÊÖ¶¯´´½¨Êý¾Ý¿â¶ø²»ÊÇͨ¹ýDBCA£¬Ôò¿ªÊ¼´´½¨Êý¾Ý¿âʱ£¬ÄãÖ»Äܶ¨Òåpfile ¡£ÒòΪËüÊÇÎı¾¸ñʽµÄ£»
ÏÖÔÚÒ»°ãÆÕͨÉú²ú¿â¶¼²ÉÓÃspfileÆô¶¯Êý¾Ý¿â£¬pfileȷʵûÓÐÓõÄÀíÓÉÁË£¬¸Ð¾õÖ»ÓÐÒÔÏÂÇé¿ö²Å»áʹÓÃpfile£º
1)ÈëÃÅѧϰʱ£¬ÎªÁËÀí½â³õʼ»¯²ÎÊý
2)´´½¨Êý¾Ý¿âʱ£¬ÒòΪÕâʱ»¹Ã»spfile
3)»Ö¸´Êý¾Ý¿âʱ
4)²ÉÓÃRACµÄÉú²ú¿âÖÐÈç¹ûÒª¹²ÓÃspfile£¬¿ÉÒÔ½¨Ò»¸ö³õʼµÄpfile,È»ºóÔÚpfileÖ¸¶¨spfileΪ¹²ÏíÉ豸Îļþ
...............
2.´´½¨spfileºÍpfileÓï·¨
´´½¨SPFILE(Ö»Äܽ¨ÔÚÊý¾Ý¿âËùÔڵĻúÆ÷ÉÏ)
CREATE SPFILE [= 'spfile_name'] from PFILE [= 'pfile_name'];
Èç¹ûÃüÁîÖÐδָ¶¨spfileµÄ·¾¶Ôò»áÔÚȱʡ·¾¶´´½¨spfileÎļþ£¨%ORACLE_HOME%databasespfile<SID>.ora£©
Õâ¸öÓëspfile²ÎÊýÎÞ¹Ø(racÖй²ÏíspfileʱҪעÒâ)
´´½¨PFILE
CREATE PFILE [= 'pfile_name'] from SPFILE [= 'spfile_name'];
Èç¹ûÃüÁîÖÐδָ¶¨spfileµÄ·¾¶Ôò»á´Óȱʡ·¾¶£¨%ORACLE_HOME%databasespfile<SID>.ora£©´´½¨pfileÎļþ
×¢£ºÓï·¨Öд´½¨ºÍÖ¸¶¨µÄspfileºÍpfile¶¼ÊÇ·þÎñÆ÷·¾¶
×¢£º²»ÄÜ´´½¨ÒѾÓÉÊý¾Ý¿âʵÀýÆô¶¯µÄspfile(ÔÚnomount״̬ÏÂÒ²²»ÐÐ)
3.Êý¾Ý¿âÆô¶¯Ê±spfileºÍpfileµÄ¼ÓÔØË³Ðò
³õʼ»¯²ÎÊýÊÇÔÚÊý¾Ý¿âʵÀýÆô¶¯Ê±(startup nomount)¼ÓÔØ¡£
Èç¹ûstartupÃüÁîÖÐδָ¶¨pfile²ÎÊý£¬ÔÚwindowsϵͳÖÐÊý¾Ý¿âÆô¶¯Ê±»á°´ÈçÏÂ˳Ðò²éÕÒÆô¶¯²ÎÊýÎļþ:
%ORACLE_HOME%databaseSPFILE<SID>.ora
%ORACLE_HOME%databaseSPFILE.ora
%ORACLE_HOME%
Ïà¹ØÎĵµ£º
ÒÔÉÏÎª×ªÔØ,¶àÊý²¢Î´ÑéÖ¤.
ÆäÖÐ,select trunc(sysdate,'dd') from dual,µÃµ½µÄÊÇ: 2009-3-23 ÉÏÎç12:00:00 ,ÓëÏÂÎIJ¢²»Ò»Ñù!
oracle trunc()º¯ÊýµÄÓ÷¨
1.TRUNC(for dates)
TRUNCº¯ÊýΪָ¶¨ÔªËضø½ØÈ¥µÄÈÕÆÚÖµ¡£
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏ£º
TRUNC£¨date[,fmt]£©
ÆäÖУº
date Ò»¸öÈÕÆÚÖµ
fmt ÈÕÆÚ¸ñʽ£¬¸ÃÈÕÆÚ½« ......
Oracle ·ÖÎöº¯ÊýʹÓýéÉÜ
·ÖÎöº¯ÊýÊÇoracle816ÒýÈëµÄÒ»¸öȫеĸÅÄî,ΪÎÒÃÇ·ÖÎöÊý¾ÝÌṩÁËÒ»ÖÖ¼òµ¥¸ßЧµÄ´¦Àí·½Ê½.ÔÚ·ÖÎöº¯Êý³öÏÖÒÔǰ,ÎÒÃDZØÐëʹÓÃ×ÔÁª²éѯ,×Ó²éѯ»òÕßÄÚÁªÊÓͼ,ÉõÖÁ¸´ÔӵĴ洢¹ý³ÌʵÏÖµÄÓï¾ä,ÏÖÔÚÖ»ÒªÒ»Ìõ¼òµ¥µÄsqlÓï¾ä¾Í¿ÉÒÔʵÏÖÁË,¶øÇÒÔÚÖ´ÐÐЧÂÊ·½ÃæÒ²ÓÐÏ൱´óµÄÌá¸ß.ÏÂÃæÎÒ½«Õë¶Ô·ÖÎöº¯ ......
²éѯ¼°É¾³ýÖØ¸´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ý ......
Stream ÊÇOracle µÄÏûÏ¢¶ÓÁÐ(Ò²½ÐOracle Advanced Queue)¼¼ÊõµÄÒ»ÖÖÀ©Õ¹Ó¦ÓᣠOracle µÄÏûÏ¢¶ÓÁÐÊÇͨ¹ý·¢²¼/¶©Ôĵķ½Ê½À´½â¾öʼþ¹ÜÀí¡£Á÷¸´ÖÆ(Stream replication)Ö»ÊÇ»ùÓÚËüµÄÒ»¸öÊý¾Ý¹²Ïí¼¼Êõ£¬Ò²¿ÉÒÔ±»ÓÃ×÷Ò»¸ö¿ÉÁé»î¶¨ÖƵĸ߿ÉÓÃÐÔ·½°¸¡£ Ëü¿ÉÒÔʵÏÖÁ½¸öÊý¾Ý¿âÖ®¼äÊý¾Ý¿â¼ ......
½ñÌìÔÚÍøÉÏÂҹ䣬¿´µ½Ò»Æª²»´íµÄÎÄÕ£¬¹ØÓÚORACLEÃÜÂëÎļþµÄ£¬ÀíÂÛѧϰ+¸öÈËʵ¼ù£¬ºÇºÇ£¬¸´ÖƹýÀ´£¬·½±ãÒÔºóѧϰ¡£
===============================================================================
ÀíÂÛ²¿·Ö
Oracle¹ØÏµÊý¾Ý¿âϵͳÒÔÆä׿ԽµÄÐÔÄÜ»ñµÃÁ˹㷺µÄÓ¦Ó㬶ø±£Ö¤Êý¾Ý¿â°²È«ÐÔÊÇÊý¾Ý¿â¹ÜÀí¹¤×÷µÄÖØÒªÄÚÈÝ¡£±¾ ......