ORACLE 11gR2的延迟段创建机制浅析

今天看到TOM大师的《深入oracle体系结构》一书中说不能在临时表空间创建PERMANENT（永久的）对象，于是心血来潮想做个实验。

实验环境：ORACLE 11gR2

1.找到临时表空间的名称，一般为TEMP,但是为了明确，所以查找一下

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME          FILE_NAME                                                                                                                               file_size(M) AUT
---------------- -----------------------------------------------
TEMP             E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF    

2.创建一个空表

SQL> create table testss  tablespace TEMP as select * from scott.emp where 1=2;

Table created.

咦，开玩笑，怎么创建成功了？？？不是说不能在临时表空间创建永久对象的吗？TOM大师不可能说错啊，那怎么回事？

然后我想是不是没有创建在临时表空间而是创建在别的表空间了（虽然我知道我写的语句正确，但是还是忍不住这样想）？于是我查了下表的信息

SQL> select a.table_name,b.tablespace_name,b.file_name
from dba_tables a,dba_temp_files b
where a.tablespace_name = b.tablespace_name;
  2    3  
TABLE_NAME           TABLESPACE_NAME          FILE_NAME
------------------------------ ------------------------------ 
TESTSS             TEMP            E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF

确实是在临时表空间啊，怎么回事？突然我想到oracle 11g r2有延迟段创建的机制，是不是就是这个原因呢？于是我又创建了一个有数据的表

3.创建一个有数据的表

SQL> create table tests  tablespace TEMP as select * from scott.emp;
create table tests  tablespace TEMP as select * from scott.emp
                                                            *
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

这次报错了，事实很明显的向我的想法靠拢，我想再试一下，就往第一次创建的空表里面插入了数据

4.往空表里面插入数据

SQL> insert into testss  select * from scott.emp;
insert into testss  select * from scott.emp
            *
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

又是报错，好了，我想这次应该可以很确定的证明自己的想法。刚好我有一个现成的oracle 10g r2的库，我想直接在10g里面试试

5.在10g库中创建空表和带数据的表

SQL> create table testss  tablespace TEMP as select * from scott.emp;
create table testss  tablespace TEMP as select * from scott.emp
                                                            *
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace


SQL> create table testss  tablespace TEMP as select * from scott.emp where 1=2;
create table testss  tablespace TEMP as select * from scott.emp where 1=2
                                                            *
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

在oracle 10g 中，不管是在临时表空间创建空表还是带数据的表，都会报错，好了，这次我放心的确定造成在11g中可以在临时表空间创建空表的原因就是延迟段创建机制。

那么什么是延迟段创建机制呢？

通俗的说，就是如果你在创建一个空表的时候，数据库是不会给他分配盘区的直到向这个空表插入第一条数据的时候，数据库才给它分配盘区，这样的好处是可以避免大量的空表占用空间，那么下面就让我们看看延迟段创建的工作模式

1.首先创建一个空表

SQL> create table testss as select * from scott.emp where 1=2;

Table created.

2.查看表的状态

SQL> select * from dba_segments where segment_name = 'TESTSS';

no rows selected

可以看到查不到这个段

3.给表插入数据后再次查看表的状态

SQL> insert into testss select * from scott.emp;

14 rows created.

SQL> commit;

SQL> select owner,segment_name,segment_type,tablespace_name from dba_segments where segment_name = 'TESTSS';

OWNER             SEGMENT_NAME                   SEGMENT_TYPE      TABLESPACE_NAME  EXTENTS
------------------------------ --------------------------------------------------------
EFM_TRA_352013           TESTSS                     TABLE        USERS              1

可以看出来数据库已经给这个表分配了盘区。
4.如果我直接创建一个带数据的表呢？

SQL> create table testss as select * from scott.emp;

Table created.

SQL> select owner,segment_name,segment_type,tablespace_name,extents from dba_segments where segment_name = 'TESTSS';

OWNER             SEGMENT_NAME                   SEGMENT_TYPE      TABLESPACE_NAME   EXTENTS
------------------------------ -----------------------------------------------------------------
EFM_TRA_352013           TESTSS                     TABLE        USERS               1

你会发现数据库在表创建完之后就给他分配了盘区。