oracleËÀËø²éѯ¼°´¦Àí
²éѯ·¢ÉúËÀËøµÄselectÓï¾ä
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
---------------------------------------------------------
¹ØÓÚÊý¾Ý¿âËÀËøµÄ¼ì²é·½·¨
Ò»¡¢ Êý¾Ý¿âËÀËøµÄÏÖÏó
³ÌÐòÔÚÖ´ÐеĹý³ÌÖУ¬µã»÷È·¶¨»ò±£´æ°´Å¥£¬³ÌÐòûÓÐÏìÓ¦£¬Ò²Ã»ÓгöÏÖ±¨´í¡£
¶þ¡¢ ËÀËøµÄÔÀí
µ±¶ÔÓÚÊý¾Ý¿âij¸ö±íµÄijһÁÐ×ö¸üлòɾ³ýµÈ²Ù×÷£¬Ö´ÐÐÍê±Ïºó¸ÃÌõÓï¾ä²»Ìá
½»£¬ÁíÒ»Ìõ¶ÔÓÚÕâÒ»ÁÐÊý¾Ý×ö¸üвÙ×÷µÄÓï¾äÔÚÖ´ÐеÄʱºò¾Í»á´¦Óڵȴý״̬£¬
´ËʱµÄÏÖÏóÊÇÕâÌõÓï¾äÒ»Ö±ÔÚÖ´ÐУ¬µ«Ò»Ö±Ã»ÓÐÖ´Ðгɹ¦£¬Ò²Ã»Óб¨´í¡£
Èý¡¢ ËÀËøµÄ¶¨Î»·½·¨
ͨ¹ý¼ì²éÊý¾Ý¿â±í£¬Äܹ»¼ì²é³öÊÇÄÄÒ»ÌõÓï¾ä±»ËÀËø£¬²úÉúËÀËøµÄ»úÆ÷ÊÇÄÄһ̨¡£
1£©ÓÃdbaÓû§Ö´ÐÐÒÔÏÂÓï¾ä
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
Èç¹ûÓÐÊä³öµÄ½á¹û£¬Ôò˵Ã÷ÓÐËÀËø£¬ÇÒÄÜ¿´µ½ËÀËøµÄ»úÆ÷ÊÇÄÄһ̨¡£×Ö¶Î˵Ã÷£º
Username£ºËÀËøÓï¾äËùÓõÄÊý¾Ý¿âÓû§£»
Lockwait£ºËÀËøµÄ״̬£¬Èç¹ûÓÐÄÚÈݱíʾ±»ËÀËø¡£
Status£º ״̬£¬active±íʾ±»ËÀËø
Machine£º ËÀËøÓï¾äËùÔڵĻúÆ÷¡£
Program£º ²úÉúËÀËøµÄÓï¾äÖ÷ÒªÀ´×ÔÄĸöÓ¦ÓóÌÐò¡£
2£©ÓÃdbaÓû§Ö´ÐÐÒÔÏÂÓï¾ä£¬¿ÉÒԲ鿴µ½±»ËÀËøµÄÓï¾ä¡£
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
ËÄ¡¢ ËÀËøµÄ½â¾ö·½·¨
Ò»°ãÇé¿öÏ£¬Ö»Òª½«²úÉúËÀËøµÄÓï¾äÌá½»¾Í¿ÉÒÔÁË£¬µ«ÊÇÔÚʵ¼ÊµÄÖ´Ðйý³ÌÖС£Óû§¿É
Äܲ»ÖªµÀ²úÉúËÀËøµÄÓï¾äÊÇÄÄÒ»¾ä¡£¿ÉÒÔ½«³ÌÐò¹Ø±Õ²¢ÖØÐÂÆô¶¯¾Í¿ÉÒÔÁË¡£
¡¡¾³£ÔÚOracleµÄʹÓùý³ÌÖÐÅöµ½Õâ¸öÎÊÌ⣬ËùÒÔÒ²×ܽáÁËÒ»µã½â¾ö·½·¨¡£
¡¡¡¡1£©²éÕÒËÀËøµÄ½ø³Ì£º
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
from V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
¡¡¡¡2£©killµôÕâ¸öËÀËøµÄ½ø³Ì£º
¡
Ïà¹ØÎĵµ£º
а²×°ÁËһ̨Êý¾Ý¿â·þÎñÆ÷£¬°æ±¾ÊÇ10.2.0.1µÄ£¬ÒòΪÏÖÔÚ×îеÄÊÇ10.2.0.4µÄ¡£ °æ±¾µÍÁË£¬bug¶à¡£ËùÒԾͶÔÊý¾Ý¿â×öÁËÒ»¸öÉý¼¶¡£
·þÎñÆ÷ÊÇwindows 2003µÄϵͳ£¬¶¼ÊÇ¿ÉÊÓ»°²Ù×÷¡£ ÏÂÒ»²½µÄÎÊÌâ¡£ 10.2.0.4µÄpatchset ÀïÃæÒ²ÓÐÏêϸµÄ°²×°ËµÃ÷¡£ Ö®×ö¸ö×ܽá ......
Ŀǰ£¬ÕýÔò±í´ïʽÒѾÔںܶàÈí¼þÖеõ½¹ã·ºµÄÓ¦Ó㬰üÀ¨*nix£¨Linux, UnixµÈ£©£¬HPµÈ²Ù×÷ϵͳ£¬PHP£¬C#£¬JavaµÈ¿ª·¢»·¾³¡£
Oracle 10gÕýÔò±í´ïʽÌá¸ßÁËSQLÁé»îÐÔ¡£ÓÐЧµÄ½â¾öÁËÊý¾ÝÓÐЧÐÔ£¬ ÖØ¸´´ÊµÄ±æÈÏ, Î޹صĿհ׼ì²â£¬»òÕß·Ö½â¶à¸öÕýÔò×é³É
µÄ×Ö·û´®µÈÎÊÌâ¡£
Oracle 10gÖ§³ÖÕýÔò±í´ïʽµÄËĸöк¯Êý·Ö±ðÊÇ£ºREGEXP_L ......
1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection ......
oracle distinct
¹Ø¼ü×Ö: distinctµÄһЩÔÓ̸
distinctÕâ¸ö¹Ø¼ü×ÖÀ´¹ýÂ˵ô¶àÓàµÄÖØ¸´¼Ç¼ֻ±£ÁôÒ»Ìõ£¬µ«ÍùÍùÖ»Óà ËüÀ´·µ»Ø²»Öظ´¼Ç¼µÄÌõÊý£¬¶ø²»ÊÇÓÃËüÀ´·µ»Ø²»ÖؼǼµÄËùÓÐÖµ¡£ÆäÔÒòÊÇdistinctÖ»ÓÐÓöþÖØÑ»·²éѯÀ´½â¾ö£¬¶øÕâÑù¶ÔÓÚÒ»¸öÊý¾ÝÁ¿·Ç³£´óµÄÕ¾À´Ëµ£¬ÎÞÒÉÊÇ»áÖ±½ÓÓ°Ï쵽ЧÂʵġ£
ÏÂÃæÏÈÀ´¿´¿´Àý×Ó£º
table ......
OracleÖеÄdecodeÓ÷¨
Decode(Ìõ¼þ£¬Öµ1£¬ÏÔʾֵ1£¬Öµ2£¬ÏÔʾֵ2£¬…… Öµn£¬ÏÔʾֵn)
Ó¦ÓþÙÀý£º
select t.res_id,
t.res_size || '(kb)' as res_size,
decode(t.res_type,1,'Ä£°åÇø','0','ÎĵµÇø') res_type,
......