Oracleʱ¼äÀàÐÍÊý¾ÝΪ0µÄBug
¿ª·¢×éÔÚÊý¾ÝǨÒÆʱ£¬±¨¸æ·¢ÏÖһЩÊýֵΪ'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-dd') from dual;
select to_date('0000-00-00', 'yyyy-mm-dd') from dual
ORA-01843: not a valid month
SQL> select to_date('0000-01-01', 'yyyy-mm-dd') from dual;
select to_date('0000-01-01', 'yyyy-mm-dd') from dual
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
µ«ÎªÊ²Ã´ÔÚÊý¾ÝÖл¹ÊdzöÏÖÁË'0000/00/00'ÄØ£¿¶Ô´ËÎÊÌâÉÔ΢Ñо¿ÁËһϣ¬·¢ÏÖOracleÔÚdateÀàÐ͵ÄÊý¾ÝÎÊÌâÉÏȷʵ´æÔÚһЩbug£¬Í¨¹ýһЩÌØÊâ·½·¨»¹ÊÇÄÜʹdateÀàÐÍ´æ´¢'0000/00/00'Êý¾Ý¡£ÏÈ¿´ÒÔϲÙ×÷£¬ÕâÊÇbugÖ®Ò»¡£
SQL> select date '0000-01-01' from dual;
DATE'0000-01-01'
----------------
0/0/0000
SQL> select date '0000-11-22' from dual;
DATE'0000-11-22'
----------------
0/0/0000
ÔÚʹÓÃdate¹Ø¼ü×Öʱ£¬Ê±¼ä¸ñʽÊÇÂÞÂí¸ñʽ¡£´Ëʱ£¬ÎÒÃÇ·¢ÏÖOracleûÓжÔÄê·ÝÊÇ·ñΪ0½øÐÐУÑé¡£²¢ÇÒ£¬Ö»ÒªÄê·ÝΪ0£¬Êý¾Ý¶¼»á±»×ª±äΪ'0000/00/00'¡£
ÔÙ¿´ÁíÍâÒ»ÖÖÇé¿ö£¬
SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;
TO_DATE('0001-01-01','YYYY-MM-
------------------------------
0/0/0000
SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-360 from dual;
TO_DATE('0001-01-01','YYYY-MM-
------------------------------
0/0/0000
¿ÉÒÔ¿´µ½£¬Oracle¶Ôʱ¼ä±í´ïʽµÄ½á¹ûҲûÓÐУÑéÄê·ÝÊÇ·ñΪ0£¬½áºÏÉÏÃæµÄbug£¬Ö»Òª¼ÆËã½á¹ûÄê·ÝΪ0£¬ÎÞÂÛÔ¡¢ÈÕÊýÖµ£¬½á¹û¶¼Îª'0000/00/00'¡£
ÔÙ¿´µÚÈýÖÖÇé¿ö£¬¾Í¸ü¼ÓÌØÊâÁË£ºÖ»Òª¶Ô100µ½1500ÄêÖ®ÄÚµÄËùÓÐÕû°ÙÄêµÄÈÕÆÚ½øÐмÆË㣬Èç¹û½á¹ûΪ2ÔÂ29µÄ»°£¬½á¹û¶¼Îª'0000/00/00'¡£
SQL> select date '0099-2-28' +1 from dual;
DATE'0099-2-28'+1
-----------------
3/1/0099
SQL> select date '0100-2-28' +1 from dual;
DATE'0100-2-28'+1
Ïà¹ØÎĵµ£º
ORACLE SQLÓï¾äÓÅ»¯×ܽá
1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, Ä ......
(1) v$sql
¡¡¡¡Ò»ÌõÓï¾ä¿ÉÒÔÓ³Éä¶à¸öcursor,ÒòΪ¶ÔÏóËùÖ¸µÄcursor¿ÉÒÔÓв»Í¬Óû§(ÈçÀý1)¡£Èç¹ûÓжà¸öcursor(×ÓÓαê)´æÔÚ£¬ÔÚV$SQLAREAΪËùÓÐcursorÌṩ¼¯ºÏÐÅÏ¢¡£
Àý1£º
ÕâÀï½éÉÜÒÔÏÂchild cursor
user A: select * from tbl
user B: select * from tbl
´ó¼ÒÈÏΪÕâÁ½ÌõÓï¾äÊDz»ÊÇÒ»ÑùµÄ°¡£¬¿ÉÄÜ»áÓкܶàÈË»á˵ÊÇÒ»Ñù ......
¸Õ¸ÕÔÚinthirtiesÀÏ´óµÄ²©¿ÍÀï¿´µ½ÕâƪÎÄÕ£¬Ð´µÄ²»´í£¬ÕýºÃ×Ô¼º×î½üÔÚѧϰPL/SQL£¬×ª¹ýÀ´Ñ§Ï°Ñ§Ï°¡£
==================================================================================
bulk collectÊÇ¿ÉÒÔ¿´×öÊÇÒ»ÖÖÅú»ñÈ¡µÄ·½Ê½£¬ÔÚÎÒÃǵÄplsqlµÄ´úÂë¶ÎÀï¾³£×÷ΪintoµÄÀ©Õ¹À´Ê¹Ó᣶ÔÓÚselect id into v from ... ......
ÓÐÁ½ÖÖº¬ÒåµÄ±í´óС¡£Ò»ÖÖÊÇ·ÖÅä¸øÒ»¸ö±íµÄÎïÀí¿Õ¼äÊýÁ¿£¬¶ø²»¹Ü¿Õ¼äÊÇ·ñ±»Ê¹Ó᣿ÉÒÔÕâÑù²éѯ»ñµÃ×Ö½ÚÊý£º
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
ÁíÒ»ÖÖ±íÊ ......
¡¡Ò»¡¢ÉèÖóõʼ»¯²ÎÊý job_queue_processes
¡¡¡¡sql> alter system set job_queue_processes=n;£¨n>0£©
¡¡¡¡job_queue_processes×î´óֵΪ1000
¡¡¡¡
¡¡¡¡²é¿´job queue ºǫ́½ø³Ì
¡¡¡¡sql>select name,description from v$bgprocess;
¡¡¡¡
¡¡¡¡¶þ£¬dbms_job package Ó÷¨½éÉÜ
¡¡¡¡°üº¬ÒÔÏÂ×Ó¹ý³Ì£º
......