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 10Gcreate 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 10Gconn test/test
Connected.
SQL 10Gdesc user_constraints
NameNull?Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPEVARCHAR2(1)
TABLE_NAMENOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNERVARCHAR2(30)
R_CONSTRAINT_NAMEVARCHAR2(30)
DELETE_RULEVARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATEDVARCHAR2(13)
GENERATEDVARCHAR2(14)
BADVARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGEDATE
INDEX_OWNERVARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALIDVARCHAR2(7)
VIEW_RELATED VARCHAR2(14
查看constraint的初始值
SQL 10G select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERRED from user_constraints where table_name='T';
CONSTRAINT_NAMEC STATUS DEFERRABLE DEFERRED
------------------------------ - -------- -------------- ---------
CHECK_XC ENABLEDDEFERRABLE IMMEDIATE
CHECK_YC ENABLEDDEFERRABLE DEFERRED
由于x列的初始值为immediate，所以当发生insert的时候就直接报错了
SQL 10Ginsert 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 10Gset constraints all deferred;
Constraint set.
SQL 10Ginsert into t values(-1,1);
1 row created.
SQL 10Gcommit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHECK_X) violated
可以看到constraint设置起作用了
再次insert又报错误，因为set constraint的作用域是事务级的，已经恢复到初始设置
SQL 10Ginsert 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 10Galter session set constraints=deferred;
Session altered.
SQL 10Ginsert into t values(-1,1);
1 row created.
SQL 10Gcommit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHECK_X) violated
SQL 10Ginsert into t values(-1,1);
1 row created.
SQL 10Gcommit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST.CHECK_X) violated
可以发现在session级别内constraint的设置都保持有效
下面来演示一下有条件的unique限制
SQL 10Gdrop table t;
Table dropped.
SQL 10Gcreate table t(a varchar2(10),b number);
Table created.
SQL 10Gcreate unique index uni_t
2on t( case when a = 'ACTIVE' then b end );
Index created.
SQL 10Ginsert into t values('a',1);
1 row created.
SQL 10Ginsert into t values('a',1);
1 row created.
SQL 10Gcommit;
Commit complete.
SQL 10Ginsert into t values('ACTIVE',1);
1 row created.
SQL 10Ginsert 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 10Gcreate tablespace testearse datafile '/opt/oracle/oradata/dBTest/testearse.dbf' size 1m;
Tablespace created.
SQL 10Gcreate table testearse(a number) tablespacetestearse;
Table created.
看看有哪些进程关联到这个数据文件
SQL 10G!
[oracle@csdba ~]$ lsof grep testearse
oracle 4424oracle 33uW REG8,91056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf
oracle25121oracle 15uREG8,91056768 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 4424oracle 33uW REG8,91056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
oracle25121oracle 15uREG8,91056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
oracle4424 10 Sep14 ?00:00:35 ora_dbw0_dbtest
oracle 25121 258930 14:41 ?00:00:00 oracledbtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SQL 10Ginsert into testearse values(10);
1 row created.
SQL 10Gcommit;
Commit complete.
SQL 10Galter system checkpoint;
System altered.
SQL 10Ginsert into testearse values(10);
1 row created.
SQL 10Gcommit;
SQL 10Gselect * from testearse;
A
----------
10
10
SQL 10GALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
SQL 10Gselect * from testearse;
A
----------
10
10
由于本身sqlplus的process和testearse.dbf还建立连接，所以这时仍然可以对这个表进行操作
退出sqlplus，重新开启sqlplus
SQL 10Gexit
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 4424oracle 33uW REG8,91056768 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
