resumeable space allocation
使用resumable大致有以下特点：
1，RESUMABLE_TIMEOUT 初始参数值设置为一个非0的数值，通过alter session enable resumable开启
2，一个可恢复的语句在下列条件发生时将会挂起：
out of space
maximum extents reached
space quota exceeded
3，当有resumable语句挂起的时候，alert log会记录错误报告，如果用户在挂起后使用trigger时间，
trigger将会执行。用户可以使用plsql的DBMS_RESUMABLE包或者dba_resumable和user_resumable视图来获取错误信息
4，通过挂起事务来挂起一个语句。因此，所有的事务资源是通过一个语句来挂起和恢复的。
5，如果错误条件解决掉了，挂起语句将自动回复执行，挂起警报也会解除。
6，可以通过dbms_resumable.abort()来强制挂起某个语句，这个过程的执行需要dba权限。
7，一个resumable语句可以被挂起和恢复多次。
那什么是resumable？或者它包含哪些操作？
1，查询
select语句在排序时临时表空间不够的情况
2，dml
insert,update,delete都是，且不关心执行这些语句的结构，比如oci,sqlj,plsql或者其他接口，
同时，insert into ...select from external tabls 也是 resumable
3，impoert/export
4,ddl
create table .. as select
create index
alter index..rebuild
alter table..move partition
alter table..split partition
alter index..rebuild partition
alter index..split partition
create materialized view
create materialized view log
常见的报错信息：
out of condition
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...
Maximum extents reached condition
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...
Space quota exceeded condition
ORA-1536 space quote exceeded for tablespace string
查看和修改系统中相关的视图或者参数。
SQL>
SQL> select * from system_privilege_map where name = 'RESUMABLE';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-236 RESUMABLE 0
SQL> alter session enabel resumable;
alter session enabel resumable
ORA-00922: missing or invalid option
SQL> alter session enable resumable;
Session altered
SQL> show parameter resumable_timeout;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 0
SQL> alter session set resumable_timeout=60;
Session altered
SQL> select * from dba_outstanding_alerts
2 ;
SEQUENCE_ID REASON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE REASON TIME_SUGGESTED CREATION_TIME SUGGESTED_ACTION ADVISOR_NAME METRIC_VALUE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL HOSTING_CLIENT_ID MODULE_ID PROCESS_ID HOST_ID HOST_NW_ADDR INSTANCE_NAME INSTANCE_NUMBER USER_ID EXECUTION_CONTEXT_ID ERROR_INSTANCE_ID
----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------ ------------ ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- --------------- ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
SQL>
可以建个小表空间来测试：
先看看这个表空间不够的错误
SQL> alter session enable resumable;
Session altered
SQL> show parameter resumable_timeout;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 0
SQL> alter session set resumable_timeout=60;
Session altered
SQL> select * from dba_outstanding_alerts
2 ;
SEQUENCE_ID REASON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE REASON TIME_SUGGESTED CREATION_TIME SUGGESTED_ACTION ADVISOR_NAME METRIC_VALUE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL HOSTING_CLIENT_ID MODULE_ID PROCESS_ID HOST_ID HOST_NW_ADDR INSTANCE_NAME INSTANCE_NUMBER USER_ID EXECUTION_CONTEXT_ID ERROR_INSTANCE_ID
----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------ ------------ ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- --------------- ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
SQL> create tablespace test_resumable datafile '$ORACLE_HOME/test/test_resumable.ora' size 2m;
Tablespace created
SQL>
SQL> alter user test default tablespace test_resumable;
User altered
SQL> select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST_RESUMABLE
SQL> create table t_test_resumable as select * from all_objects union all select * from all_objects;
create table t_test_resumable as select * from all_objects union all select * from all_objects
ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 128 in tablespace TEST_RESUMABLE
SQL> select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST_RESUMABLE
SQL>
SQL> select * from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID COORD_INSTANCE_ID COORD_SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARAMETER1 ERROR_PARAMETER2 ERROR_PARAMETER3 ERROR_PARAMETER4 ERROR_PARAMETER5 ERROR_MSG
---------- ---------- ----------- ----------------- ---------------- --------- ---------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
61 129 1 NORMAL 60 User TEST(61), Session 129, Instance 1 select * from dba_resumable 0
挂起时间60秒到了之后就报错了，下面把挂起时间修改大一些，然后在这段时间内扩展表空间，使得create语句可以顺利执行：
SQL> create tablespace test_resumable datafile '$ORACLE_HOME/test/test_resumable1.ora' size 2m;
Tablespace created
SQL> alter user test default tablespace test_resumable;
User altered
SQL> select * from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID COORD_INSTANCE_ID COORD_SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARAMETER1 ERROR_PARAMETER2 ERROR_PARAMETER3 ERROR_PARAMETER4 ERROR_PARAMETER5 ERROR_MSG
---------- ---------- ----------- ----------------- ---------------- --------- ---------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------- ----------------------------------------------------------
SQL> alter session enable resumable;
Session altered
SQL> show parameters resumable;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 0
SQL> alter session enable resumable timeout 3600;
Session altered
SQL> create table t_test_resumable as select * from all_objects union all select * from all_objects;
因为空间不够，到这里的时候挂起，进入等待时间。
在另外一个session中执行：
SQL> select * from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID COORD_INSTANCE_ID COORD_SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARAMETER1 ERROR_PARAMETER2 ERROR_PARAMETER3 ERROR_PARAMETER4 ERROR_PARAMETER5 ERROR_MSG
---------- ---------- ----------- ----------------- ---------------- --------- ---------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
61 129 1 SUSPENDED 3600 12/16/09 02:41:57 12/16/09 02:41:59 User TEST(61), Session 129, Instance 1 create table t_test_resumable as select * from all_objects union all select * f 1652 128 TEST_RESUMABLE ORA-01652: unable to extend temp segment by 128 in tablespace TEST_RESUMABLE
SQL>
SQL> alter database datafile '$ORACLE_HOME/test/test_resumable1.ora' resize 50m;
Database altered
SQL> select * from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID COORD_INSTANCE_ID COORD_SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARAMETER1 ERROR_PARAMETER2 ERROR_PARAMETER3 ERROR_PARAMETER4 ERROR_PARAMETER5 ERROR_MSG
---------- ---------- ----------- ----------------- ---------------- --------- ---------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
61 129 1 NORMAL 3600 12/16/09 02:43:57 User TEST(61), Session 129, Instance 1 0
SQL>
然后再看看前面的create的session：
Table created
SQL>
顺利完成了。
在create index 或者m view或者一些重要select的时候可以适当设置，然后通过获取dba_resumable或者alert等的错误信息，来报告，发邮件或者短信，比较实用。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-622342/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-622342/
521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
