- 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。
参考dochttp://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2075
试验如下:
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;
数据库已更改。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/96210/viewspace-1056787/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/96210/viewspace-1056787/