resumable特性以及系统触发器中的SUSPEND event测试

先来看一道考题：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21584437/viewspace-718887/