Oracle 11g r2的新特性,延迟段创建 ,就是说从11GR2开始默认创建的表不会立及分配segment,不会占用磁盘空间,这听上去也是很合
Oracle 11g r2的新特性,延迟段创建 ,就是说从11GR2开始默认创建的表不会立及分配segment,,不会占用磁盘空间,这听上去也是很合理的,当第一条数据insert时才会分配空间
试验一把
sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> 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
anbob@ANBOB> create table testnew(id int primary key,name varchar2(10));
Table created.
anbob@ANBOB> create table testnew_IME(id int primary key,name varchar2(10)) segment creation immediate;
Table created.
anbob@ANBOB> create table testnew_def(id int primary key,name varchar2(10)) segment creation deferred;
Table created.
anbob@ANBOB> select segment_name from user_segments where segment_name like ‘TESTNEW%’;
SEGMENT_NAME
———————————————————————————
TESTNEW_IME
anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name=’TESTNEW’;
INDEX_NAME
TABLE_OWNER
—————————— ——————————
SYS_C0010903
ANBOB
anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name=’TESTNEW_IME’;
INDEX_NAME
TABLE_OWNER
—————————— ——————————
SYS_C0010904
ANBOB
anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name=’TESTNEW_DEF’;
INDEX_NAME
TABLE_OWNER
—————————— ——————————
SYS_C0010905
ANBOB
anbob@ANBOB> select segment_name from user_segments where segment_name=’SYS_C0010903′;
no rows selected
anbob@ANBOB> select segment_name from user_segments where segment_name=’SYS_C0010904′;
SEGMENT_NAME
———————————————————————————
SYS_C0010904
anbob@ANBOB> select segment_name from user_segments where segment_name=’SYS_C0010905′;
no rows selected
anbob@ANBOB> insert into testnew values(1,’anbob.com’);
1 row created.
anbob@ANBOB> commit;
Commit complete.
anbob@ANBOB> select segment_name from user_segments where segment_name like ‘TESTNEW%’;
SEGMENT_NAME
———————————————————————————
TESTNEW
TESTNEW_IME
anbob@ANBOB> select INDEX_NAME,TABLE_OWNER from USER_indexes where table_name=’TESTNEW’;
INDEX_NAME
TABLE_OWNER
—————————— ——————————
SYS_C0010903
ANBOB
anbob@ANBOB> select segment_name from user_segments where segment_name=’SYS_C0010903′;
SEGMENT_NAME
———————————————————————————
SYS_C0010903
anbob@ANBOB> truncate table testnew;
Table truncated.
anbob@ANBOB> select segment_name from user_segments where segment_name like ‘TESTNEW%’;
SEGMENT_NAME
———————————————————————————
TESTNEW
TESTNEW_IME
anbob@ANBOB> conn sys/oracle as sysdba
Connected.
sys@ANBOB> 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
*
ERROR at line 1:
ORA-14223: 此表不支持延迟创建段
note:
11g r2默认是使用segment creation deferred建立,新建的无记录表不分配sement,当insert 第一条记录时分配段空间,不会因truncate而回收,并且在sys schema里不支持,听说exp 也不会导出