先来看一道考题:
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/