下一次我们将分享自动治理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
|
