今天看OCP考试的一道题目是关于resumable的，首先resumbale就不是不太懂，结果看它看出别的问题来了呵呵，具体的题目如下：

QUESTION NO: 107

You notice that a long-running transaction is suspended due to a space constraint, and there is no

AFTER SUSPEND triggered event addressing the issue. You also note that the critical transaction

is just about to reach the RESUMABLE_TIMEOUT value. Which of these actions is appropriate?

A. Abort the session, fix the space problem, then resubmit the transaction.

B. Use the DBMS_RESUMABLE.SET_SESSION_TIMEOUT procedure to extend the time-out for

the session while you fix the problem.

C. Do nothing, let the transaction fail, then fix the problem.

D. Use Segment Shrink to clean up the table.

E. Use the DBMS_RESUMABLE.SET_TIMEOUT procedure to extend the time-out for the session

while you fix the problem.

Answer: B

     先是看了如下网址中的内容了解resumable，当通过创建数据文件超出表空间的大小来模拟这种延时挂起的时候问题出现了，呵呵，上面的全是开场白，下面才是要论述的内容：

OS：redhat 5.4

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.查看表空间是否为自动扩展：

如果为自动扩展可以通过alter  database datafile ‘文件路径’ autoextend off；来关闭，这里是为了试验的需求。

SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id;

FILE_ID FILE_NAME TABLESPACE_NAME AUT
---------- --------------------------------------------- -------------------- ---
1 /u01/app/oracle/oradata/zxy/system01.dbf SYSTEM YES
2 /u01/app/oracle/oradata/zxy/sysaux01.dbf SYSAUX YES
3 /u01/app/oracle/oradata/zxy/undotbs01.dbf UNDOTBS1 YES
4 /u01/app/oracle/oradata/zxy/users01.dbf USERS YES
5 /u01/app/oracle/oradata/zxy/tbs01.dbf OGG NO

2.查看OGG表空间的剩余量：

SQL> SELECT SUM(BYTES)/1024/1024
2 FROM DBA_FREE_SPACE
3 WHERE TABLESPACE_NAME = 'OGG';
SUM(BYTES)/1024/1024
--------------------
8.75

3.创建500M的表看看是否报不能创建的错误：

SQL> create table t1 (id number)
2 tablespace OGG
3 storage (initial 500m);
Table created.

结果确是能顺利的创建表。这是为什么呢？

3.通过查询资料和论坛的提问了解到原来11gR2新增加了一个延时段：deferred_segment_creation是控制它的参数；11gR2默认是打开它的，如果之前把其设置为flase就会报错了，

  SQL> alter system set deferred_segment_creation=false scope=memory;

  System altered.

SQL> create table t1 (id number)
  2    tablespace OGG
  3   storage (initial 500m);
create table t1 (id number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace OGG

其中conpents中对其描述如下：

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time

如果这个参数是true状态则创建的时候不会报错，也就说在创建的时候并不会为其分配extent，无论你创建一个多么大的表，只有当插入数据的时候才会为其分配extent，如果这个参数为true的时候 storage (initial XXXm)也是不起作用的。

SQL> select segment_name
  2  from user_segments
  3  where segment_name like 'T1';

no rows selected

SQL> insert into t1 values(1);
insert into t1 values(1)
            *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace OGG

如此看来这个特性还是挺有用的，有时候创建了表不用就不会额外的占用空间。还可以参考http://yangtingkun.itpub.net/post/468/495759。