下一次我们将分享自动管理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
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 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
2
on 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) 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 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
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
