只读表空间中创建对象-deferred_segment_creation

有朋友发现可以在只读表空间里创建新的空表，提出疑问，这实际上是Oracle Database 11g的新特性Deferred Segment Creation在建表时的"副作用"，由于无需分配空间，新建的数据表只需要增加元数据，就出现了可以在只读表空间中创建空表的现象。

以下是一个测试演示过程，当deferred_segment_creation设置为TRUE时，可以在只读表空间中创建数据表，但是当插入记录需要分配空间时，则会出现错误：

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter tablespace users read only;

Tablespace altered.

SQL> connect eygle/eygle                    
Connected.
SQL> create table eygle (id number) tablespace users;


Table created.


SQL> select table_name,tablespace_name from dba_tables where table_name='EYGLE';


TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

EYGLE                          USERS


SQL> insert into eygle values(1);

insert into eygle values(1)

            *

ERROR at line 1:

ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in it

当设置该参数为False时，则创建数据表时就需要分配空间，直接抛出异常，无法创建：

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table e (id number) tablespace users;
create table e (id number) tablespace users
*
ERROR at line 1:
ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in it


SQL>  alter session set deferred_segment_creation=true;

Session altered.

记录一下文档中的描述：

You can drop items, such as tables or indexes, from a read-only
tablespace, but you cannot create or alter objects in a read-only
tablespace. You can execute statements that update the file description
in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to utilize the new description until the tablespace is made read/write.

是以为新特性记录。