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

我们先来看一个考题：

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

?答案是:B。

参考doc

试验如下：

session1：
SQL> create tablespace users datafile 'E:ORACLEPRODUCT10.2.0ORADATAORCLUSE
RS.DBF' SIZE 3M;

表空间已创建。

SQL> grant resumable to xys;

授权成功。

SQL> create or replace trigger trigg_system
2 after suspend on database
3 begin
4 dbms_resumable.set_timeout(10);
5 end;
6 /

警告: 创建的触发器带有编译错误。

SQL> show error
TRIGGER TRIGG_SYSTEM 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PL/SQL: Statement ignored
2/2 PLS-00201: 必须声明标识符 'DBMS_RESUMABLE'
SQL> create or replace trigger trigg_system
2 after suspend on database
3 begin
4 dbms_resumable.set_timeout(10);
5 end;
6 /

警告: 创建的触发器带有编译错误。

SQL> connect /as sysdba
已连接。
SQL> grant execute on dbms_resumable to xys;

授权成功。

SQL> connect xys/manager
已连接。
SQL> create or replace trigger trigg_system
2 after suspend on database
3 begin
4 dbms_resumable.set_timeout(10);
5 end;
6 /

触发器已创建

session2：
SQL> create table t_resumable tablespace users as select *from dba_objects;

表已创建。

SQL> insert into t_resumable select *from t_resumable;
insert into t_resumable select *from t_resumable
*
第 1 行出现错误:
ORA-01653: 表 XYS.T_RESUMABLE 无法通过 128 (在表空间 USERS 中) 扩展

SQL> alter session enable resumable;

会话已更改。

--注意这里insert操作会等待10秒，10秒之后出现了错误
SQL> insert into t_resumable select *from t_resumable;
insert into t_resumable select *from t_resumable
*
第 1 行出现错误:
ORA-30032: 挂起的 (可恢复) 语句已超时
ORA-01653: 表 XYS.T_RESUMABLE 无法通过 128 (在表空间 USERS 中) 扩展

--回到session1修改一下timeout时间（10秒有些短，来不及修改datafile 4的autoextend属性），单位是秒
SQL> create or replace trigger trigg_system
2 after suspend on database
3 begin
4 dbms_resumable.set_timeout(60);
5 end;
6 /

触发器已创建

session2：
--此时下面insert会等待60秒,如果60秒之后发现空间可用，则继续执行
SQL> insert into t_resumable select *from t_resumable;

已创建11413行。

--此期间在session1中修改datafile 4使其能自动扩展

SQL> alter database datafile 4 autoextend on;

数据库已更改。

SQL>