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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值