一、引言:
ORACLE10G,新建空表以后立刻就分配Segment, Oracle11gR2,新建空表后没有立即分配Segment。这就是11GR2的新特性,延迟段创建,就是说从11GR2开始默认创建的表不会立即分配segment,不会占用磁盘空间,当第一条数据insert时才会分配空间。
二、实验模拟:
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
SQL> create table testnew(id int primary key,name varchar2(10));
Table created
SQL> create table testnew_IME(idint primary key,name varchar2(10)) segment creationimmediate;
Table created
SQL> create table testnew_def(idint primary key,name varchar2(10)) segment creationdeferred;
Table created
SQL> select segment_name from user_segments where segment_name like 'TESTNEW%';
SEGMENT_NAME
--------------------------------------------------------------------------------
TESTNEW_IME
SQL> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW';
INDEX_NAME TABLE_OWNER
------------------------------------------------------------
SYS_C0011192 JACK
SQL> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_IME';
INDEX_NAME TABLE_OWNER
------------------------------------------------------------
SYS_C0011193 JACK
SQL> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW_DEF';
INDEX_NAME TABLE_OWNER
------------------------------------------------------------
SYS_C0011194 JACK
SQL> select segment_name from user_segments where segment_name='SYS_C0011192';
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> select segment_name from user_segments where segment_name='SYS_C0011193';
SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_C0011193
SQL> select segment_name from user_segments where segment_name='SYS_C0011194';
SEGMENT_NAME
--------------------------------------------------------------------------------
SQL> insert into testnew values(1,'anbob.com');
1 row inserted
SQL> commit;
Commit complete
SQL> select segment_name from user_segments where segment_name like 'TESTNEW%';
SEGMENT_NAME
--------------------------------------------------------------------------------
TESTNEW
TESTNEW_IME
SQL> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name='TESTNEW';
INDEX_NAME TABLE_OWNER
------------------------------------------------------------
SYS_C0011192 JACK
SQL> select segment_name from user_segments where segment_name='SYS_C0011192';
SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_C0011192
SQL> truncate table testnew;
Table truncated
SQL> select segment_name from user_segments where segment_name like 'TESTNEW%';
SEGMENT_NAME
--------------------------------------------------------------------------------
TESTNEW
TESTNEW_IME
SQL> conn /as sysdba
已连接。
SQL> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred;
create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred
*
第 1 行出现错误:
ORA-14223: 此表不支持延迟创建段
---sys schema里不支持,exp也不会导出
注意:
11gR2默认是使用segmentcreation deferred建立,新建的无记录表不分配segment,当insert第一条记录时分配段空间,不会因truncate而回收,并且在sysschema里不支持,exp也不会导出。
关于这个主要还是跟deferred_segment_creation参数有关,在11gR2中该参数的值为true,说明当创建对象(如表),初始没有数据,不会立即创建segment。
如果该参数设置为false,表明之后的创建的表,初始没有数据,会立即创建segment。
下面看一下它的效果:
SQL> alter system set deferred_segment_creation=false;
System altered
SQL> create table jack(x int);
Table created
SQL> select segment_name,segment_type,extents,blocks from user_segments where segment_name='JACK';
SEGMENT_NAME SEGMENT_TYPE EXTENTS BLOCKS
-------------------------------------------------------------------------------------------------- ---------- ----------
JACK TABLE 1 8