oracleÖжÔÅÅÐòµÄ×ܽá
¡¡¡¡select * from perexl order by nlssort(danwei,'NLS_SORT=SCHINESE_PINYIN_M');
¡¡¡¡-- °´²¿Ê×ÅÅÐò
¡¡¡¡select * from perexl order by nlssort(danwei,'NLS_SORT=SCHINESE_STROKE_M');
¡¡¡¡-- °´±Ê»ÅÅÐò
¡¡¡¡select * from perexl order by nlssort(danwei,'NLS_SORT=SCHINESE_RADICAL_M');
¡¡¡¡--ÅÅÐòºó»ñÈ¡µÚÒ»ÐÐÊý¾Ý
¡¡¡¡select * from (select * from perexl order by
nlssort(danwei,'NLS_SORT=SCHINESE_PINYIN_M') )C where rownum=1
¡¡¡¡--½µÐòÅÅÐò
¡¡¡¡select * from perexl order by zongrshu desc
¡¡¡¡--ÉýÐòÅÅÐò
¡¡¡¡select * from perexl order by zongrshu asc
¡¡¡¡--½«nullsʼÖÕ·ÅÔÚ×îÇ°
¡¡¡¡select * from perexl order by danwei nulls first
¡¡¡¡--½«nullsʼÖÕ·ÅÔÚ×îºó
¡¡¡¡select * from perexl order by danwei desc nulls last
¡¡¡¡--decodeº¯Êý±Ènvlº¯Êý¸üÇ¿´ó£¬Í¬ÑùËüÒ²¿ÉÒÔ½«ÊäÈë²ÎÊýΪ¿Õʱת»»ÎªÒ»Ìض¨Öµ
¡¡¡¡select * from perexl order by decode(danwei,null,'µ¥Î»ÊÇ¿Õ', danwei)
¡¡¡¡-- ±ê×¼µÄrownum·ÖÒ³²éѯʹÓ÷½·¨
¡¡¡¡select *from (select c.*, rownum rn from personnel c)where rn >= 1and rn <= 5
¡¡¡¡--ÔÚoracleÓï¾ärownum¶ÔÅÅÐò·ÖÒ³µÄ½â¾ö·½°¸
¡¡¡¡--µ«ÊÇÈç¹û, ¼ÓÉÏorder by ÐÕÃû ÅÅÐòÔòÊý¾ÝÏÔʾ²»ÕýÈ·
¡¡¡¡select *from (select c.*, rownum rn from personnel c order by ³öÉúÄêÔÂ)where rn >=
1and rn <= 5
¡¡¡¡--½â¾ö·½·¨£¬ÔÙ¼ÓÒ»²ã²éѯ£¬Ôò¿ÉÒÔ½â¾ö
¡¡¡¡select *from (select rownum rn, t.*from (select ÐÕÃû, ³öÉúÄêÔÂ from personnel order
by ³öÉúÄêÔÂ desc) t)where rn >= 1and rn <= 5
¡¡¡¡--Èç¹ûÒª¿¼Âǵ½Ð§ÂʵÄÎÊÌ⣬ÉÏÃæµÄ»¹¿ÉÒÔÓÅ»¯³É£¨Ö÷ÒªÁ½ÕßÇø±ð£©
¡¡¡¡select *from (select rownum rn, t.*from (select ÐÕÃû,³öÉúÄêÔÂ from personnel order
by ³öÉúÄêÔÂ desc) t where rownum <= 10) where rn >= 3
¡¡¡¡--nvlº¯Êý¿ÉÒÔ½«ÊäÈë²ÎÊýΪ¿Õʱת»»ÎªÒ»Ìض¨Öµ,ÏÂÃæ¾ÍÊǵ±µ¥Î»Îª¿ÕµÄʱºòת»»³É“µ¥Î»ÊÇ¿Õ”
¡¡¡¡select * from perexl order by nvl(danwei,'µ¥Î»ÊÇ¿Õ')
Ïà¹ØÎĵµ£º
oracle grant
ÊÚȨÓï¾ä--select * from dba_users; ²éѯÊý¾Ý¿âÖеÄËùÓÐÓû§
--alter user TEST_SELECT account lock; ËøסÓû§
--alter user TEST_SELECT account unlock; ¸øÓû§½âËø
--create user xujin identified by xujin; ½¨Á¢Óû§
--grant create tablespace to xujin; ÊÚȨ
--grant select ......
³£¼ûºǫ́½ø³Ì°üÀ¨:
• Êý¾Ý¿âд½ø³Ì (DBWn):½«Êý¾Ý¿â»º³åÇø¸ßËÙ»º´æÖеľ¹ýÐ޸ĵĻº³åÇø(»ÒÊý¾Ý»º ³åÇø)ÒÔÒì²½·½Ê½Ð´Èë´ÅÅÌ
• ÈÕ־д½ø³Ì (LGWR):½«ÈÕÖ¾»º³åÇøÖгÆΪÖØ×öÐÅÏ¢µÄ»Ö¸´ÐÅϢдÈë´ÅÅÌÉϵÄÖØ×öÈÕ Ö¾Îļþ
• ¼ì²éµã½ø³Ì (CKPT):ÔÚ¿ØÖÆÎļþºÍÿ¸öÊý¾ÝÎļþÍ·ÖмǼ¼ì²éµãÐÅÏ¢
• ϵͳ¼àÊÓÆ÷½ø³ ......
ÎÄÕÂÒ»£ºSCNÊÇʲô£¿The System Change Number
system change number (SCN)ÊÇÒ»¸ö·Ç³£ÖØÒªµÄ±ê¼Ç£¬OracleʹÓÃËüÀ´±ê¼ÇÊý¾Ý¿âÔÚ¹ýȥʱ¼äÄÚµÄ״̬ºÍ¹ì¼£¡£
OracleʹÓÃSCNÀ´±£´æËùÓб仯µÄ¹ì¼£¡£SCNÊÇÒ»¸öÂ߼ʱÖÓÀ´¼Ç¼Êý¾Ý¿âʼþ¡£Ëü·Ç³£µÄÖØÒª£¬²¢²»ÊÇÖ»ÊÇΪÁ˻ָ´¡£
SCNÓеãÀàËÆÓÚsequence£¬OracleÔÚSGAÖÐÔ ......
Update SQLS will lead dead lock exception. Because by default, it use optimistic lock but not pessimistic lock.
Below description is exceprted from the attachment(It exceeds my image that this blog can not upload the attachment,hehe)
Optimistic locking offers an elegant solution to the problems o ......
ʲôÊÇË®Ïß(High Water Mark)?
----------------------------
ËùÓеÄoracle¶Î(segments£¬ÔÚ´Ë£¬ÎªÁËÀí½â·½±ã£¬½¨Òé°Ñsegment×÷Ϊ±íµÄÒ»¸öͬÒå´Ê) ¶¼ÓÐÒ»¸öÔÚ¶ÎÄÚÈÝÄÉÊý¾ÝµÄÉÏÏÞ£¬ÎÒÃÇ°ÑÕâ¸öÉÏÏÞ³ÆΪ"high water mark"»òHWM¡£Õâ¸öHWMÊÇÒ»¸ö±ê¼Ç£¬ÓÃÀ´ËµÃ÷ÒѾÓжàÉÙûÓÐʹÓõÄÊý¾Ý¿é·ÖÅä¸øÕâ¸ösegment¡£HWMͨ³£Ôö³¤µÄ·ù¶ ......