Oracle 10g中对resumable session的增强
从9i开始,Oracle提供了一种避免因为space Error而导致事务异常的操作,那就是resumable.通常,DBA在日常工作中,往往忽略了对空间资源上的需求,比如一个大的事务所需要的temp,undo,data space等等。直到出现了经典的ORA-01652才恍然大悟。
下面回顾一下这个特性。
一、回顾9i中的功能(复习一下好了) 1)权限设置 首先一个用户要设置resumable session,必须具有resumable的权限。 SQL> select * from system_privilege_map where name='RESUMABLE'; PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -236 RESUMABLE 0 SQL> SQL> conn study/study 已连接。 SQL> alter session enable resumable; ERROR: ORA-01031: 权限不足 SQL> SQL> conn /as sysdba 已连接。 SQL> grant resumable to study; 授权成功。 SQL> conn study/study 已连接。 SQL> alter session enable resumable; 会话已更改。 SQL> 当然了,resumable权限也被包含在其他role里面,比如dba,当一个用户拥有dba role时
,就自动拥有了resumable privilege. 2)ENALBE resumable session 在9i中,我们可以通过如下的设置,让一个session当遇到空间分配不足的时候继续。 SQL> alter session enable resumable; 3)DISABLE resumable session 和enable 相反 SQL> alter session disable resumable; 4)超时设置 resumable session的默认超时时间为7200秒,也就是2小时。 我们可以通过如下的SQL来调整timeout的时间,比如 SQL> alter session enable resumable timeout 1800; 会话已更改。 SQL> 5)监控 我们可以通过dba_resumable和alert日志来监控resumable session的状态。 SQL> alter session enable resumable timeout 10; 会话已更改。 SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME, 2 SQL_TEXT, ERROR_NUMBER, ERROR_MSG 3 from dba_resumable; USER_ID SESSION_ID STATUS START_TIME SUSPEND_TIME ---------- ---------- --------- ----------------- -------------- 61 49 NORMAL SQL_TEXT ERROR_NUMBER ERROR_MSG ------------- ------------ ------------- 0 SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; 。。。 SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME, 2 SQL_TEXT, ERROR_NUMBER, ERROR_MSG 3 from dba_resumable; USER_ID SESSION_ID STATUS START_TIME SUSPEND_TIME ---------- ---------- --------- -------------------- -------------------- 61 49 SUSPENDED 02/26/07 10:31:20 02/26/07 10:31:20 SQL_TEXT ERROR_NUMBER ------------------------------------------ ------------ create index inx_res_test on res_test 1536 (owner,object_id,object_name) tablespace t ERROR_MSG ---------------------------------------- ORA-01536: 超出表空间 'TEST' 的空间限额 超时以后: SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test * 第 1 行出现错误: ORA-30032: 挂起的 (可恢复) 语句已超时 ORA-01536: 超出表空间 'TEST' 的空间限额 SQL> 同时,当resumable session 被挂起时,在alert 日志文件中也会有相应的记录信息
,如下所示: Mon Feb 26 10:29:37 2007 statement in resumable session 'User STUDY(61), Session 49, Instance 1'
was suspended due to ORA-01536: 超出表空间 'TEST' 的空间限额 6)dbms_reumable包 Oracle也提供了一个dbms_resumable package来设置获取或异常终止一个
resumable session的操作。 这个包比较简单。我简单的示范一下。有兴趣的同志,可以参考Oracle的online doc. SQL> alter session enable resumable; 会话已更改。 SQL> exec dbms_resumable.set_session_timeout(49,1800); PL/SQL 过程已成功完成。 SQL> select dbms_resumable.get_session_timeout(49) from dual; DBMS_RESUMABLE.GET_SESSION_TIM ------------------------------ 1800 SQL> SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; ... {因为表空间不足,所以被挂起} 打开另外一个session,可以取消上面的操作,而不必等到timeout SQL> exec dbms_resumable.abort(49); PL/SQL procedure successfully completed SQL> 在session id 为49的交互界面将得到如下的返回信息: SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test * 第 1 行出现错误: ORA-01013: 用户请求取消当前的操作 SQL> 二、10g中的增强 在10g中,Oracle一样支持9i的上述方法,同时做了增强。 1)增加了一个resumable_timeout的参数 该参数可以在system和session level级均可以修改.对RAC db,每个instance可以单独设置. 而9i中只能在session一级中设置。比如:
SQL> show parameter resumable_timeout; NAME TYPE VALUE ------------------------------------ -------------- ------- resumable_timeout integer 0 SQL> alter system set resumable_timeout=60; 系统已更改。 SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test * 第 1 行出现错误: ORA-30032: 挂起的 (可恢复) 语句已超时 ORA-01652: 无法通过 8 (在表空间 TEST 中) 扩展 temp 段 SQL> 2)对分布式事务的支持 In 9i, users are not allowed to start a distributed transaction in a resumable enabled session. And if a session has a distributed transaction, users
are not allowed to enable resumable. These restrictions are removed in 10g. However, in a distributed transaction,
if users enable/disable resumable or change resumable_timeout, only the local instances are affected. In a distributed transaction, sessions on remote instances are suspended if resumable has been enabled in the remote instance. 3)增加了一个监控的视图 在10g中,我们也可以通过DBA_OUTSTANDING_ALERTS 来监控
resumable session.比如: SQL> create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test; create index inx_res_test on res_test(owner,object_id,object_name)
tablespace test * 第 1 行出现错误: ORA-30032: 挂起的 (可恢复) 语句已超时 ORA-01652: 无法通过 8 (在表空间 TEST 中) 扩展 temp 段 SQL> select object_name,object_type,reason,message_type,message_level 2 from dba_outstanding_alerts 3 where message_group='Space'; OBJECT_NAME OBJECT_TYPE REASON ------------ ------------ ----------------------------------------------- TEST TABLESPACE 对可恢复会话 User STUDY(61),Session 49,Instance 1 (会话 ID 为 49) 的操作被挂起 MESSAGE_TYPE MESSAGE_LEVEL ------------ ------------- Warning 5 SQL> 三、resumable操作在其他utiliy中的使用 在exp/imp,sql*loader工具,同样也支持resumable操作,本文不再多加介绍, 大家可以参考Oracle online doc获取更多的信息。 最后,这个特性还是很有用的。我们可以在临近下班的时候,开始一个很大的create index的操作,设置2小时的timeout,然后就可以回家吃饭了,如果中间因为space不 足而挂起,我们也可以及时的收到短信告警[别的朋友可能有其他的监控],调整后, 让事务继续。多爽!啥也不耽误
resumable特性以及系统触发器中的SUSPEND event测试!
之前一直没有搞清楚系统触发器中的suspend事件什么时候触发,正好看到一位puber问此问题,搜索一下doc,做了一个简单的测试。
参考doc:
http://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;
数据库已更改。