PL/SQL学习之oracle排序系列二

　　下一次我们将分享自动管理PGA
　　
　　set constraint,alter session set constraint,有条件的unique限制
　　
　　set constraint 子句是用来设置deferrable constraint的状态的，可以设置constraint的状态为immediate或deferred，具体语法请看
　　http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_104a.htm#2066962
　　
　　它的作用域在事务级别，一旦事务结束constraint的状态恢复初始值
　　
　　SQL 10G>create table t
　　( x int constraint check_x check ( x > 0 ) deferrable initially immediate,
　　y int constraint check_y check ( y > 0 ) deferrable initially deferred
　　)
　　/
　　
　　SQL 10G>conn test/test
　　Connected.
　　SQL 10G>desc user_constraints
　　Name　　　　　　　　　　　　　　　　　　　Null?　　Type
　　----------------------------------------- -------- ----------------------------
　　OWNER　　　　　　　　　　　　　　　　　　 NOT NULL VARCHAR2(30)
　　CONSTRAINT_NAME　　　　　　　　　　　　　 NOT NULL VARCHAR2(30)
　　CONSTRAINT_TYPE　　　　　　　　　　　　　　　　　　VARCHAR2(1)
　　TABLE_NAME　　　　　　　　　　　　　　　　NOT NULL VARCHAR2(30)
　　SEARCH_CONDITION　　　　　　　　　　　　　　　　　 LONG
　　R_OWNER　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(30)
　　R_CONSTRAINT_NAME　　　　　　　　　　　　　　　　　VARCHAR2(30)
　　DELETE_RULE　　　　　　　　　　　　　　　　　　　　VARCHAR2(9)
　　STATUS　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(8)
　　DEFERRABLE　　　　　　　　　　　　　　　　　　　　 VARCHAR2(14)
　　DEFERRED　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(9)
　　VALIDATED　　　　　　　　　　　　　　　　　　　　　VARCHAR2(13)
　　GENERATED　　　　　　　　　　　　　　　　　　　　　VARCHAR2(14)
　　BAD　　　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(3)
　　RELY　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(4)
　　LAST_CHANGE　　　　　　　　　　　　　　　　　　　　DATE
　　INDEX_OWNER　　　　　　　　　　　　　　　　　　　　VARCHAR2(30)
　　INDEX_NAME　　　　　　　　　　　　　　　　　　　　 VARCHAR2(30)
　　INVALID　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(7)
　　VIEW_RELATED　　　　　　　　　　　　　　　　　　　 VARCHAR2(14
　　
　　查看constraint的初始值
　　
　　SQL 10G> select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERRED from user_constraints where table_name='T';
　　CONSTRAINT_NAME　　　　　　　　C STATUS　 DEFERRABLE　　 DEFERRED
　　------------------------------ - -------- -------------- ---------
　　CHECK_X　　　　　　　　　　　　C ENABLED　DEFERRABLE　　 IMMEDIATE
　　CHECK_Y　　　　　　　　　　　　C ENABLED　DEFERRABLE　　 DEFERRED
　　
　　由于x列的初始值为immediate，所以当发生insert的时候就直接报错了
　　
　　SQL 10G>insert into t values(-1,1);
　　insert into t values(-1,1)
　　*
　　ERROR at line 1:
　　ORA-02290: check constraint (TEST.CHECK_X) violated
　　
　　设定constraint为deferred
　　
　　SQL 10G>set constraints all deferred;
　　Constraint set.
　　SQL 10G>insert into t values(-1,1);
　　1 row created.
　　SQL 10G>commit;
　　commit
　　*
　　ERROR at line 1:
　　ORA-02091: transaction rolled back
　　ORA-02290: check constraint (TEST.CHECK_X) violated
　　
　　可以看到constraint设置起作用了
　　
　　再次insert又报错误，因为set constraint的作用域是事务级的，已经恢复到初始设置
　　
　　SQL 10G>insert into t values(-1,1);
　　insert into t values(-1,1)
　　*
　　ERROR at line 1:
　　ORA-02290: check constraint (TEST.CHECK_X) violated
　　
　　使用alter session 来进行session级别的constraint设置
　　
　　SQL 10G>alter session set constraints=deferred;
　　Session altered.
　　SQL 10G>insert into t values(-1,1);
　　1 row created.
　　SQL 10G>commit;
　　commit
　　*
　　ERROR at line 1:
　　ORA-02091: transaction rolled back
　　ORA-02290: check constraint (TEST.CHECK_X) violated
　　
　　SQL 10G>insert into t values(-1,1);
　　1 row created.
　　SQL 10G>commit;
　　commit
　　*
　　ERROR at line 1:
　　ORA-02091: transaction rolled back
　　ORA-02290: check constraint (TEST.CHECK_X) violated
　　
　　可以发现在session级别内constraint的设置都保持有效
　　
　　下面来演示一下有条件的unique限制
　　
　　SQL 10G>drop table t;
　　Table dropped.
　　
　　SQL 10G>create table t(a varchar2(10),b number);
　　Table created.
　　SQL 10G>create unique index uni_t
　　2　on t( case when a = 'ACTIVE' then b end );
　　Index created.
　　
　　SQL 10G>insert into t values('a',1);
　　1 row created.
　　
　　SQL 10G>insert into t values('a',1);
　　1 row created.
　　
　　SQL 10G>commit;
　　Commit complete.
　　
　　SQL 10G>insert into t values('ACTIVE',1);
　　1 row created.
　　
　　SQL 10G>insert into t values('ACTIVE',1);
　　insert into t values('ACTIVE',1)
　　*
　　ERROR at line 1:
　　ORA-00001: unique constraint (TEST.UNI_T) violated
　　
　　通过函数索引就实现了有条件的unique限制，不考虑性能问题的话倒是一个好的方法
　　
　　数据库打开情况下删除数据文件会发生什么(unix)
　　
　　创建测试表空间及表
　　
　　SQL 10G>create tablespace testearse datafile '/opt/oracle/oradata/dbtest/testearse.dbf' size 1m;
　　Tablespace created.
　　
　　SQL 10G>create table testearse(a number) tablespace　testearse;
　　Table created.
　　
　　看看有哪些进程关联到这个数据文件
　　
　　SQL 10G>!
　　[oracle@csdba ~]$ lsof |grep testearse
　　oracle　　 4424　oracle　 33uW　　 REG　　　　8,9　　1056768　　 852911 /opt/oracle/oradata/dbtest/testearse.dbf
　　oracle　　25121　oracle　 15u　　　REG　　　　8,9　　1056768　　 852911 /opt/oracle/oradata/dbtest/testearse.dbf
　　
　　删除这个数据文件
　　
　　[oracle@csdba ~]$ rm /opt/oracle/oradata/dbtest/testearse.dbf
　　[oracle@csdba ~]$
　　
　　再观看lsof的结果，发现相关的状态已经变成deleted，但是文件还是保持打开状态
　　
　　[oracle@csdba ~]$ lsof |grep testearse
　　oracle　　 4424　oracle　 33uW　　 REG　　　　8,9　　1056768　　 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
　　oracle　　25121　oracle　 15u　　　REG　　　　8,9　　1056768　　 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
　　
　　oracle　　4424　　 1　0 Sep14 ?　　　　00:00:35 ora_dbw0_dbtest
　　oracle　 25121 25893　0 14:41 ?　　　　00:00:00 oracledbtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
　　
　　SQL 10G>insert into testearse values(10);
　　1 row created.
　　SQL 10G>commit;
　　Commit complete.
　　SQL 10G>alter system checkpoint;
　　System altered.
　　SQL 10G>insert into testearse values(10);
　　1 row created.
　　SQL 10G>commit;
　　SQL 10G>select * from testearse;
　　A
　　----------
　　10
　　10
　　
　　SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
　　Session altered.
　　
　　SQL 10G>select * from testearse;
　　A
　　----------
　　10
　　10
　　
　　由于本身sqlplus的process和testearse.dbf还建立连接，所以这时仍然可以对这个表进行操作
　　
　　退出sqlplus，重新开启sqlplus
　　
　　SQL 10G>exit
　　Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
　　With the Partitioning and Data Mining Scoring Engine options
　　
　　[oracle@csdba ~]$ lsof |grep testearse
　　oracle　　 4424　oracle　 33uW　　 REG　　　　8,9　　1056768　　 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
　　
　　这时只剩下dbwr进程和testearse.dbf还建立连接
　　
　　[oracle@csdba bdump]$　sqlplus "/ as sysdba"
　　SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 10 15:47:58 2005
　　Copyright (c) 1982, 2005, Oracle.　All

查看本文来源