OracleÊý¾Ý¿âÖÐÐòÁÐÓ÷¨½²½â
ÐòÁÐ(SEQUENCE)ÊÇÐòÁкÅÉú³ÉÆ÷£¬¿ÉÒÔΪ±íÖеÄÐÐ×Ô¶¯Éú³ÉÐòÁкţ¬²úÉúÒ»×éµÈ¼ä¸ôµÄÊýÖµ(ÀàÐÍΪÊý×Ö)¡£ÆäÖ÷ÒªµÄÓÃ;ÊÇÉú³É±íµÄÖ÷¼üÖµ£¬¿ÉÒÔÔÚ²åÈëÓï¾äÖÐÒýÓã¬Ò²¿ÉÒÔͨ¹ý²éѯ¼ì²éµ±Ç°Öµ£¬»òʹÐòÁÐÔöÖÁÏÂÒ»¸öÖµ¡£
´´½¨ÐòÁÐÐèÒªCREATE SEQUENCEϵͳȨÏÞ¡£ÐòÁеĴ´½¨Óï·¨ÈçÏ£º
CREATE SEQUENCE ÐòÁÐÃû
[INCREMENT BY n]
[START WITH n]
[MAXVALUE/ MINVALUE nNOMAXVALUE]
[CYCLENOCYCLE]
[CACHE nNOCACHE];
ÆäÖУº
INCREMENT BY ÓÃÓÚ¶¨ÒåÐòÁеIJ½³¤£¬Èç¹ûÊ¡ÂÔ£¬ÔòĬÈÏΪ1£¬Èç¹û³öÏÖ¸ºÖµ£¬Ôò´ú±íÐòÁеÄÖµÊÇ°´Õմ˲½³¤µÝ¼õµÄ¡£
START WITH ¶¨ÒåÐòÁеijõʼֵ(¼´²úÉúµÄµÚÒ»¸öÖµ)£¬Ä¬ÈÏΪ1¡£
MAXVALUE¶¨ÒåÐòÁÐÉú³ÉÆ÷ÄܲúÉúµÄ×î´óÖµ¡£Ñ¡ÏîNOMAXVALUEÊÇĬÈÏÑ¡Ï´ú±íûÓÐ×î´óÖµ¶¨Ò壬Õâʱ¶ÔÓÚµÝÔöÐòÁУ¬ÏµÍ³Äܹ»²úÉúµÄ×î´óÖµÊÇ10µÄ27´Î·½;¶ÔÓڵݼõÐòÁУ¬×î´óÖµÊÇ-1¡£
MINVALUE¶¨ÒåÐòÁÐÉú³ÉÆ÷ÄܲúÉúµÄ×îСֵ¡£Ñ¡ÏîNOMAXVALUEÊÇĬÈÏÑ¡Ï´ú±íûÓÐ×îСֵ¶¨Ò壬Õâʱ¶ÔÓڵݼõÐòÁУ¬ÏµÍ³Äܹ»²úÉúµÄ×îСֵÊÇ?10µÄ26´Î·½;¶ÔÓÚµÝÔöÐòÁУ¬×îСֵÊÇ1¡£
CYCLEºÍNOCYCLE±íʾµ±ÐòÁÐÉú³ÉÆ÷µÄÖµ´ïµ½ÏÞÖÆÖµºóÊÇ·ñÑ»·¡£CYCLE´ú±íÑ»·£¬NOCYCLE´ú±í²»Ñ»·¡£Èç¹ûÑ»·£¬Ôòµ±µÝÔöÐòÁдﵽ×î´óֵʱ£¬Ñ»·µ½×îСֵ;¶ÔÓڵݼõÐòÁдﵽ×îСֵʱ£¬Ñ»·µ½×î´óÖµ¡£Èç¹û²»Ñ»·£¬´ïµ½ÏÞÖÆÖµºó£¬¼ÌÐø²úÉúÐÂÖµ¾Í»á·¢Éú´íÎó¡£
CACHE(»º³å)¶¨Òå´æ·ÅÐòÁеÄÄÚ´æ¿éµÄ´óС£¬Ä¬ÈÏΪ20¡£NOCACHE±íʾ²»¶ÔÐòÁнøÐÐÄڴ滺³å¡£¶ÔÐòÁнøÐÐÄڴ滺³å£¬¿ÉÒÔ¸ÄÉÆÐòÁеÄÐÔÄÜ¡£
ɾ³ýÐòÁеÄÓï·¨ÊÇ£º
DROP SEQUENCE ÐòÁÐÃû;
ɾ³ýÐòÁеÄÈËÓ¦¸ÃÊÇÐòÁеĴ´½¨Õß»òÓµÓÐDROP ANY SEQUENCEϵͳȨÏÞµÄÓû§¡£ÐòÁÐÒ»µ©É¾³ý¾Í²»Äܱ»ÒýÓÃÁË¡£
ÐòÁеÄijЩ²¿·ÖÒ²¿ÉÒÔÔÚʹÓÃÖнøÐÐÐ޸ģ¬µ«²»ÄÜÐÞ¸ÄSATRTWITHÑ¡Ïî¡£¶ÔÐòÁеÄÐÞ¸ÄÖ»Ó°ÏìËæºó²úÉúµÄÐòºÅ£¬ÒѾ²úÉúµÄÐòºÅ²»±ä¡£ÐÞ¸ÄÐòÁеÄÓï·¨ÈçÏ£º
´´½¨ºÍɾ³ýÐòÁÐ
Àý1£º´´½¨ÐòÁУº
CREATE SEQUENCE ABC INCREMENT BY 1 START WITH 10 MAXVALUE9999999NOCYCLE NOCACHE;
Ö´Ðнá¹û£º
ÐòÁÐÒÑ´´½¨¡£
²½Öè2£ºÉ¾³ýÐòÁУº
DROP SEQUENCE ABC;
Ö´Ðнá¹û£º
ÐòÁÐÒѶªÆú¡£
˵Ã÷£ºÒÔÉÏ´´½¨µÄÐòÁÐÃûΪABC£¬ÊǵÝÔöÐòÁУ¬ÔöÁ¿Îª1£¬³õʼֵΪ10¡£¸ÃÐòÁв»Ñ»·£¬²»Ê¹ÓÃÄڴ档ûÓж¨Òå×îСֵ£¬Ä¬ÈÏ×îСֵΪ1£¬×î´óֵΪ9999999¡£
ÐòÁеÄʹÓÃ
Èç¹ûÒѾ´´½¨ÁËÐòÁУ¬ÔõÑù²ÅÄÜÒýÓÃÐòÁÐÄØ?·½·¨ÊÇʹÓÃCURRVALºÍNEXTVALÀ´ÒýÓÃÐòÁеÄÖµ¡£
ÔÚ±àºÅµÄ¹ý³ÌÖУ¬²úÉú¼ä϶µÄÔÒò¶àÖÖ¶àÑù¡£Èç¹ûÒ
Ïà¹ØÎĵµ£º
(1) v$sql
¡¡¡¡Ò»ÌõÓï¾ä¿ÉÒÔÓ³Éä¶à¸öcursor,ÒòΪ¶ÔÏóËùÖ¸µÄcursor¿ÉÒÔÓв»Í¬Óû§(ÈçÀý1)¡£Èç¹ûÓжà¸öcursor(×ÓÓαê)´æÔÚ£¬ÔÚV$SQLAREAΪËùÓÐcursorÌṩ¼¯ºÏÐÅÏ¢¡£
Àý1£º
ÕâÀï½éÉÜÒÔÏÂchild cursor
user A: select * from tbl
user B: select * from tbl
´ó¼ÒÈÏΪÕâÁ½ÌõÓï¾äÊDz»ÊÇÒ»ÑùµÄ°¡£¬¿ÉÄÜ»áÓкܶàÈË»á˵ÊÇÒ»Ñù ......
°´ÕÕOracleDocumentÖеÄÃèÊö£¬v$sysstat´æ´¢×ÔÊý¾Ý¿âʵÀýÔËÐÐÄÇ¿ÌÆð¾Í¿ªÊ¼ÀÛ¼ÆȫʵÀý(instance-wide)µÄ×ÊԴʹÓÃÇé¿ö¡£
ÀàËÆÓÚv$sesstat£¬¸ÃÊÓͼ´æ´¢ÏÂÁеÄͳ¼ÆÐÅÏ¢£º
1>.ʼþ·¢Éú´ÎÊýµÄͳ¼Æ(È磺user commits)
2>.Êý¾Ý²úÉú£¬´æÈ¡»òÕß²Ù×÷µÄtotalÁÐ(È磺redo size)
3>.Èç¹ûTIMED_STATISTICSֵΪtrue,Ôòͳ¼Æ»¨·Ñ ......
ÓÐÁ½ÖÖº¬ÒåµÄ±í´óС¡£Ò»ÖÖÊÇ·ÖÅä¸øÒ»¸ö±íµÄÎïÀí¿Õ¼äÊýÁ¿£¬¶ø²»¹Ü¿Õ¼äÊÇ·ñ±»Ê¹Ó᣿ÉÒÔÕâÑù²éѯ»ñµÃ×Ö½ÚÊý£º
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
»òÕß
Select Segment_Name,Sum(bytes)/1024/1024 from User_Extents Group By Segment_Name
ÁíÒ»ÖÖ±íÊ ......
¿ª·¢×éÔÚÊý¾ÝǨÒÆʱ£¬±¨¸æ·¢ÏÖһЩÊýֵΪ'0000/00/00'µÄdateÊý¾Ý£¬µ¼ÖÂÊý¾ÝǨÒÆʧ°Ü¡£
Õâ¸öÎÊÌâÓеãÆæ¹Ö£¬ÒòΪÔÚOracleÖУ¬dateÀàÐ͵ÄÊý¾ÝµÄÈ¡Öµ·¶Î§ÊÇ´Ó-4712/12/31µ½9999/12/31Ö®¼ä£¬²¢ÇÒÄê·Ý²»ÄÜΪ0¡£Ò²¾ÍÊÇ˵'0000/00/00'ÊÇÒ»¸ö·Ç·¨Êý¾Ý£¬²»ÎªOracleËù½ÓÊÜ¡£
SQL> select to_date('0000-00-00', 'yyyy-mm- ......