在使用create table中的storage参数时,在11g和10g中表现的不一样,步骤如下:
database 10g:
SQL> create table tab10 (id char(1000),name char(1000)) storage (minextents 2) pctfree 50 pctused 20;
Table created.
SQL> select object_id,object_name from dba_objects where object_name='TAB10';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
52957 TAB10
SQL> select extent_id,file_id,relative_fno,block_id,blocks from dba_extents where segment_name='TAB10';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
-------------------- --------- -------- ----------
0 8 8 521 128
1 8 8 649 128
database 11g:
SQL> create table tab11 (id char(1000),name char(1000)) storage(minextents 2) pctfree 50 pctused 20;
Table created.
SQL> select object_id,object_name from dba_objects where object_name='TAB11';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
75815 TAB11
SQL> select extent_id,file_id, relative_fno, block_id, blocks from dba_extents where segment_name='TAB11';
no rows selected
向表中插入数据:
SQL> insert into tab11 values ('1','a');
1 row created.
SQL> commit;
Commit complete.
SQL> select extent_id,file_id, relative_fno, block_id, blocks from dba_extents where segment_name='TAB11';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 10 10 384 128
1 10 10 512 128
在11g中,即使指定了storage参数,如果没有数据插入也不会给它分配extent!此特性和参数:deferred_segment_creation有关。此参数默认是打开的。这种情况下,当用户使用exp导出时,是不会导出这些未分配段的空表的,并且没有任何警告提示。所以建议关闭次参数。
SQL> show parameter segment
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set deferred_segment_creation=FALSE scope=both;
System altered.
SQL> drop table tab11 purge;
Table dropped.
SQL> create table tab11 (id number) storage(minextents 2) pctfree 50 pctused 20;
Table created.
SQL> select extent_id,file_id, relative_fno, block_id, blocks from dba_extents where segment_name='TAB11';
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- -------- ------------ -------- ----------
0 10 10 384 128
1 10 10 512 128
希望使用11gr2的同学注意!!
----------------------------------EOF-----------------------------------------