ITPub博客

首页 > Linux操作系统 > Linux操作系统 > resumable特性以及系统触发器中的SUSPEND event测试

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

原创 Linux操作系统 作者:逍遥三人 时间:2012-03-18 19:29:07 0 删除 编辑
先来看一道考题:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-07

  • 博文量
    73
  • 访问量
    109973