oracle Ô¼Êø(constraint)µÄ¼¸¸ö²ÎÊýµÄСÑо¿
Ê×ÏȸãÇåϼ¸¸ö¸ÅÄ
ORACLEÖУ¬Ô¼Êø·Ödeferred ¸ú immediate 2ÖÖ£º
deferred:Èç¹û Oracle ÔÚÊÂÎñÌá½»£¨commit£©Ê±²Å¶ÔÔ¼ÊøÖ´Ðмì²é£¬Ôò³Æ´ËÔ¼ÊøÊÇÑӳٵģ¨deferred£©¡£Èç¹ûÊý¾ÝÎ¥·´ÁËÑÓ³ÙÔ¼Êø£¬Ìá½»²Ù×÷½«µ¼ÖÂÊÂÎñ±»»Ø¹ö£¨undo£©¡£
immediate:Èç¹ûÔ¼ÊøÊǼ´Ê±µÄ£¨immediate£©£¨·ÇÑӳٵģ©£¬Ôò´ËÔ¼Êø½«ÔÚÓï¾äÖ´ÐнáÊøºó½øÐмì²é¡£Èç¹ûÊý¾ÝÎ¥·´ÁËÑÓ³ÙÔ¼Êø£¬Óï¾ä½«±»Á¢¼´»Ø¹ö¡£
Ò»°ãÇé¿öÏ£¬ÎÒÃÇÓõÄÔ¼Êø³õʼ¶¼ÊÇimmediateÐ͵Ä(ĬÈÏ)£¬¶øÇÒ²»ºÃתΪdeferredÐÍ¡£µ«ÊÇÈç¹û³õʼÊÇdeferrable(ÐèÒªÊÖ¶¯Ö¸¶¨),ÄÇdeferred¸úimmediate 2ÖÖ״̬¿ÉÒÔËæÒâת»»¡£
´ËÍ⣬ԼÊøÓÐÒÔÏÂ4ÖÖ״̬£º
ENABLE£¨ÆôÓã©È·±£ËùÓÐÊäÈëµÄÊý¾Ý¶¼×ñ´ÓÔ¼Êø£¨constraint£©
DISABLE£¨½ûÓã©×ÜÊÇÔÊÐíÊäÈëÊý¾Ý£¬ÎÞÂÛÊý¾ÝÊÇ·ñ×ñ´ÓÔ¼Êø
VALIDATE£¨ÑéÖ¤£©È·±£ÒÑ´æÔÚµÄÊý¾Ý×ñ´ÓÔ¼Êø
NOVALIDATE£¨ÎÞÑéÖ¤£©ÔÊÐíÒÑ´æÔÚµÄÊý¾Ý²»×ñ´ÓÔ¼Êø
ENABLE VALIDATE Óë ENABLE Ïàͬ¡£Oracle ½«¼ì²éÔ¼Êø£¬²¢±£Ö¤ËùÓÐÊý¾Ý¾ù×ñ´ÓÔ¼Êø¡£
ENABLE NOVALIDATE ±íʾËùÓÐвåÈë»ò±»Ð޸ĵÄÊý¾Ý¶¼±ØÐë×ñ´ÓÔ¼Êø£¬µ«ÔÊÐíÒÑ´æÔÚµÄÊý¾Ý²»×ñ´ÓÔ¼Êø¡£
DISABLE NOVALIDATE Óë DISABLE Ïàͬ¡£Oracle ²»»á¼ì²éÔ¼Êø.
DISABLE VALIDATE ½«½ûÓÃÔ¼Êø£¬ÒƳýÔ¼ÊøʹÓõÄË÷Òý£¬²¢½ûÖ¹ÐÞ¸ÄÔ¼Êø¼üµÄÊý¾Ý¡£
Ô¼Êø²»ÂÛÄÄÖÖÀàÐÍ£¬ÒªÄܹ»ÉúЧ£¬±ØÐë״̬ÊÇenable²ÅÐС£
SQLPLUS(oracle 10.2.0.1):
--deferred ¸ú immediateµÄ¶Ô±ÈÊÔÑé-----------------------------
SQL> drop table aa purge;
Table dropped.
SQL> create table aa ( id number,name varchar2(20),constraint pk primary key(id));
Table created.
SQL> col constraint_name for a11
SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from u
ser_constraints where table_name='AA';
CONSTRAINT_ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
----------- - ----- -------- -------------- --------- -------------
PK P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
--¿ÉÒÔ¿´µ½£¬Ä¬ÈϵÄÊÇNOT DEFERRABLE£¬ÏÂÃæÎÒÃǽ«ËüתΪimmediateÊÔÊÔ
SQL> set constraint pk immediate;
set constraint pk imme
Ïà¹ØÎĵµ£º
1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
¡¡¡¡select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
¡¡¡¡from dba_tablespaces t, dba_data_files d
¡¡¡¡where t.tablespace_name = d.tablespace_name
¡¡¡¡group by t.tablespace_name;
¡¡¡¡
¡¡¡¡2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
¡¡¡¡select tablespace_ ......
ÓÐһЩûÓÐʹÓÃÊý¾Ý¿âÖмä¼þµÄÈí¼þ¿ÉÄÜ»áÅöµ½ÔÚ²»Í¬µÄÓ¦Óó¡¾°ÐèÒªÓò»Í¬µÄÊý¾Ý¿â£¬ÕâÑù¿ÉÄܾÍÐèÒª½«Êý¾Ý¿â´úÂë×öÒÆÖ²£¬ÎÒÕâÀïÕûÀíÁËһЩ´Óoracleµ½mysqlµÄÒÆÖ²¹ý³ÌÖеÄ×¢ÒâÊÂÏ
1¡£sequenceÉú³É
¿ÉÒÔÓÃÀàËƵĶ«Î÷À´×ö,ÈçÏ£º
create table myseq ( &nb ......
/**
* ¸üмǼ£¬Ô×Ó²Ù×÷£¬²»»ácommit
* @param sql_id
* @param condition
* @return
* @throws SQLException
*/
&nbs ......
http://www.anysql.net/oracle/oracle_olap_dimension.html
ÔÚOracleµÄÊý¾Ý²Ö¿â(OLAP)ÖÐ, ʵÌ廯ÊÓͼ(MVIEW), ²éѯÖØд(Query Rewrite)ºÍά(Dimension)ÊǷdz£ÖØÒªµÄÓÅ»¯ÊÖ¶Î, ¶ÔÓÚÇ°Á½ÕßÎÒ²»ÏëÔÚÕâ¶ùÖظ´½²ÁË, Ö÷ÒªÀ´ÌåÑéÒ»ÏÂάµÄ×÷ÓÃ. Òª·¢»ÓάµÄ×÷ÓÃ, »¹ÊÇÐèÒªÓõ½Ç°ÃæÁ½Õß, ÏÂÃæÊÇÎÒÉè¼ÆµÄÖ»ÓÐÒ»¸öά±íµÄ×î¼òµ¥µÄÀý×Ó ......
¶ÔÓÚÎÒÃÇÕâ¸öÏîÄ¿À´Ëµ£¬Êý¾Ý¿âµÄ´æÈ¡µÄÐÔÄܾö¶¨ÁËÊý¾ÝÌṩµÄÐÔÄÜ¡£ÓÅ»¯µÄ´óÖµÄÔÀíÖ»ÓÐÁ½¸ö£ºÒ»ÊÇÊý¾Ý·Ö¿é´æ·Å£¬±ãÓÚÊý¾ÝµÄת´¢ºÍ¹ÜÀí£»¶þÊÇÖм䴦Àí£¬Ìá¸ßÊý¾ÝÌṩµÄËٶȡ£
»ùÓÚÉÏÃæÁ½¸ö¸ù±¾µÄÔÀí£¬½èÖúÓÚÊý¾Ý²Ö¿âµÄ¸ÅÄÁоÙÊý¾Ý¿âµÄÓÅ»¯·½Ê½£º
1£® ·ÖÇø
ÔÚÊý¾Ý²Ö¿âÖУ¬ÊÂʵ±í£¬Ë÷Òý±í£¬Î¬¶È±í·Ö´¦ÓÚÈý¸ö²»Í ......