oracle建空表不要数据,Oracle 11g r2 新建空表不分配semgent

Oracle 11g r2的新特性，延迟段创建 ，就是说从11GR2开始默认创建的表不会立及分配segment，不会占用磁盘空间，这听上去也是很合理的，当第一条数据insert时才会分配空间

试验一把

sys@ANBOB> conn anbob/anbob

Connected.

anbob@ANBOB>select*fromv$version;

BANNER

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

OracleDatabase11g 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

TNSforLinux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

anbob@ANBOB>createtabletestnew(idintprimarykey,namevarchar2(10));

Tablecreated.

anbob@ANBOB>createtabletestnew_IME(idintprimarykey,namevarchar2(10)) segment creation immediate;

Tablecreated.

anbob@ANBOB>createtabletestnew_def(idintprimarykey,namevarchar2(10)) segment creation deferred;

Tablecreated.

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_namelike'TESTNEW%';

SEGMENT_NAME

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

TESTNEW_IME

anbob@ANBOB>selectINDEX_NAME,TABLE_OWNERfromUSER_indexeswheretable_name='TESTNEW';

INDEX_NAME                     TABLE_OWNER

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

SYS_C0010903                   ANBOB

anbob@ANBOB>selectINDEX_NAME,TABLE_OWNERfromUSER_indexeswheretable_name='TESTNEW_IME';

INDEX_NAME                     TABLE_OWNER

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

SYS_C0010904                   ANBOB

anbob@ANBOB>selectINDEX_NAME,TABLE_OWNERfromUSER_indexeswheretable_name='TESTNEW_DEF';

INDEX_NAME                     TABLE_OWNER

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

SYS_C0010905                   ANBOB

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_name='SYS_C0010903';

norowsselected

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_name='SYS_C0010904';

SEGMENT_NAME

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

SYS_C0010904

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_name='SYS_C0010905';

norowsselected

anbob@ANBOB>insertintotestnewvalues(1,'anbob.com');

1 row created.

anbob@ANBOB>commit;

Commitcomplete.

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_namelike'TESTNEW%';

SEGMENT_NAME

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

TESTNEW

TESTNEW_IME

anbob@ANBOB>selectINDEX_NAME,TABLE_OWNERfromUSER_indexeswheretable_name='TESTNEW';

INDEX_NAME                     TABLE_OWNER

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

SYS_C0010903                   ANBOB

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_name='SYS_C0010903';

SEGMENT_NAME

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

SYS_C0010903

anbob@ANBOB>truncatetabletestnew;

Tabletruncated.

anbob@ANBOB>selectsegment_namefromuser_segmentswheresegment_namelike'TESTNEW%';

SEGMENT_NAME

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

TESTNEW

TESTNEW_IME

anbob@ANBOB> conn sys/oracleassysdba

Connected.

sys@ANBOB>createtabletestnew_def(idintprimarykey,namevarchar2(10)) segment creation deferred;

createtabletestnew_def(idintprimarykey,namevarchar2(10)) segment creation deferred

*

ERRORatline 1:

ORA-14223: 此表不支持延迟创建段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 也不会导出