resumable_timeout 参数说明

http://203.208.39.132/search?q=cache:442TAM_ydcAJ:blog.oracle.com.cn/index.php/12012/viewspace-6280.html+RESUMABLE_TIMEOUT&cd=1&hl=zh-CN&ct=clnk&gl=cn&client=aff-os-360se&st_usg=ALhdy290zy_Ck3SMisXCD3DcyvYx92J_WA

128. 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

[@more@]

主意，resumable_timeout在10g中才可以使用。

resumable session

以前我从没用过这个，第一次是看到Huang Yong在用

后来自己用了用，感觉挺不错，推荐给大家

这个功能主要是防止因为空间不够的原因导致session failed，

例如你create一个很大index,三天三夜后，完成了99%，这时候表空间用完了导致create失败，那只能吐血了

有了resumable session后，他会等待一段时间，等你有了空间后继续

使用很简单，首先user要有resumable权限，如果没有，可以grant resumable to user

在session开始时，alter session enable resumable就可以了

对于application可以通过logon trigger:

SQL> create or replace trigger logon_set_resumable
2 after logon
3 on xfan.schema
4 begin
5 execute immediate ‘alter session enable resumabletimeout 1200′;
6 end;
7 /

Trigger created.

这样当你空间不够的时候，session会等你，alert.log中有类似如下的信息

statement in resumable session ‘User CG15_USER(19), Session 1455, Instance 1′ was suspended due to

ORA-01652: unable to extend temp segment by12800 intablespace CATYINDX01

当你扩展表空间后，该session继续工作，alert.log中也有记录:

session in resumable session ‘User CG15_USER(19), Session 1455, Instance 1′ was resumed

当然在job开始之前预先加好空间还是上上之选，这个功能是为了以防万一

注意, 设定resumable_timeout的user , 必须有resumable 权限.

如:
SQL>altersessionenableresumable;ERROR:
ORA-01031:insufficient privileges

SQL> grant resumable to user;

SQL> alter session set resumable_time=9000;

详细信息可以参考metalink文档：136941.1（Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors

metalink参考：https://metalink.oracle.com/metalink/plsql/f?p=130:14:5469944998771384890::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,240991.1,1,1,1,helvetica

https://metalink.oracle.com/metalink/plsql/f?p=130:14:5469944998771384890::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,152013.1,1,1,1,helvetica

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/450962/viewspace-1027517/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/450962/viewspace-1027517/