resumable特性以及系統觸發器中的SUSPEND event測試

逍遙三人發表於2012-03-18
觸發器
先來看一道考題：
135. In the PROD database you have granted the RESUMABLE system privilege to the CONNECT role. 
Resumable space operation has been enabled for all user sessions. You want users NOT to be aware of 
any kind of space-related problems while performing  transactions.  Instead, you want  the problem  to be 
resolved by a database trigger automatically. 
Which combination of triggering time and event would you use to achieve this objective? 
A. AFTER CREATE 
B. AFTER SUSPEND 
C. BEFORE CREATE 
D. AFTER TRUNCATE 
E. BEFORE SUSPEND 
F. AFTER INSERT OR DELETE OR UPDATE 
G. BEFORE INSERT OR DELETE OR UPDATE 
Answer: B

測試：
1、建立測試表空間test、測試使用者test
SQL> create tablespace test datafile '/u01/app/oracle/oradata/exOa/test.dbf' size 3M;
SQL> create user test identified by test;

2、授權
SQL> grant resumable to test;
SQL> grant execute on dbms_resumable to test;
SQL> grant create session to test;
SQL> grant create table to test;

3、在使用者test下建立trigger
create or replace
trigger trigget_system
after suspend on database
begin
  dbms_resumable.set_timeout(100);
end;

4、在使用者test下建表
SQL> create table t tablespace test as select * from user_objects;

5、反覆執行
SQL> insert into t select * from t;
直到：
SQL> insert into t select * from t;
insert into t select * from t
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table TEST.T by 128 in tablespace TEST

6、改變會話的resumable屬性
SQL> alter session enable resumable ;

7、重新插入資料到t表
SQL> insert into t select * from t;

執行掛起

8、調整tablespace對應的資料檔案
SQL> alter database datafile 7 autoextend on;

9、步驟7中掛起的執行馬上執行成功
SQL> insert into t select * from t;

32768 rows created.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21584437/viewspace-718887/，如需轉載，請註明出處，否則將追究法律責任。

相關文章