表的storage (MINEXTENTS 属性对truncate后表大小的影响

来源：互联网发布：淘宝店铺代销货源编辑：程序博客网时间：2024/06/05 05:17

TRUNCATE表后对表大小进行查询，表的空间没有释放完毕？
对此进行实验测试，结果如下：(测试环境LINUX+ORACLE11.2.0.3)
创建表时语句指定了storage (MINEXTENTS 5);时，TRUNCATE后还有5个EXTENT；
创建表时语句不指定storage (MINEXTENTS参数，即使用默认值时；TRUNCATE后只有1个EXTENT；
------------实验1：指定了storage (MINEXTENTS 5)

SQL> create tablespace test2 datafile '/u01/oracle/base/oracle/oradata/bys7/test2.dbf' size 10m uniform size 40k;Tablespace created.SQL> show userUSER is "TEST"SQL> create table test1(aa number,name varchar2(128)) tablespace test2 storage (MINEXTENTS 5);Table created.SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID from dba_extents where SEGMENT_NAME='TEST1';OWNER                          SEGMENT_NAME TABLESPACE_NAME  EXTENT_ID------------------------------ ------------ --------------- ----------TEST                           TEST1        TEST2                    0TEST                           TEST1        TEST2                    1TEST                           TEST1        TEST2                    2TEST                           TEST1        TEST2                    3TEST                           TEST1        TEST2                    4SQL> insert into test1 select object_id,object_name from dba_objects;13723 rows created.SQL> commit;Commit complete.SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID from dba_extents where SEGMENT_NAME='TEST1';OWNER                          SEGMENT_NAME TABLESPACE_NAME  EXTENT_ID------------------------------ ------------ --------------- ----------TEST                           TEST1        TEST2                    0TEST                           TEST1        TEST2                    1TEST                           TEST1        TEST2                    2TEST                           TEST1        TEST2                    3TEST                           TEST1        TEST2                    4TEST                           TEST1        TEST2                    5TEST                           TEST1        TEST2                    6TEST                           TEST1        TEST2                    7TEST                           TEST1        TEST2                    8TEST                           TEST1        TEST2                    9TEST                           TEST1        TEST2                   10TEST                           TEST1        TEST2                   1112 rows selected.SQL>truncate table test1;Table truncated.SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID from dba_extents where SEGMENT_NAME='TEST1';OWNER                          SEGMENT_NAME TABLESPACE_NAME  EXTENT_ID------------------------------ ------------ --------------- ----------TEST                           TEST1        TEST2                    0TEST                           TEST1        TEST2                    1TEST                           TEST1        TEST2                    2TEST                           TEST1        TEST2                    3TEST                           TEST1        TEST2                    4SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024 kb,EXTENTS,BLOCKS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_segments where SEGMENT_NAME='TEST1';SEGMENT_NAME TABLESPACE_NAME         KB    EXTENTS     BLOCKS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS------------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- -----------TEST1        TEST2                  200          5         25         204800       40960           1  2147483645

实验2：创建表时不指定storage (MINEXTENTS参数使用默认值

SQL> create table test2 tablespace test2 as select object_id,object_name from dba_objects;Table created.SQL> set pagesize 1000SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID from dba_extents where SEGMENT_NAME='TEST2';OWNER                          SEGMENT_NAME TABLESPACE_NAME  EXTENT_ID------------------------------ ------------ --------------- ----------TEST                           TEST2        TEST2                    0TEST                           TEST2        TEST2                    1TEST                           TEST2        TEST2                    2TEST                           TEST2        TEST2                    3TEST                           TEST2        TEST2                    4TEST                           TEST2        TEST2                    5TEST                           TEST2        TEST2                    6TEST                           TEST2        TEST2                    7TEST                           TEST2        TEST2                    8TEST                           TEST2        TEST2                    9TEST                           TEST2        TEST2                   10TEST                           TEST2        TEST2                   1112 rows selected.SQL> truncate table test2;Table truncated.SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID from dba_extents where SEGMENT_NAME='TEST2';OWNER                          SEGMENT_NAME TABLESPACE_NAME  EXTENT_ID------------------------------ ------------ --------------- ----------TEST                           TEST2        TEST2                    0

0 0