PL/SQL學習之oracle排序系列二(下)
2024-08-29 13:37:44
供稿：網友
下一次我們將分享自動治理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
