[转自OCM 白大师]表的storage (MINEXTENTS)属性对truncate后表大小的影响

最新推荐文章于 2024-05-10 10:45:09 发布

msdnchina

最新推荐文章于 2024-05-10 10:45:09 发布

阅读量626

点赞数

分类专栏： Oracle DB

Oracle DB 专栏收录该内容

773 篇文章 3 订阅

订阅专栏

本文是转帖,原文网址:http://blog.csdn.net/haibusuanyun/article/details/50724747

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 user  
USER 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                    0  
TEST                           TEST1        TEST2                    1  
TEST                           TEST1        TEST2                    2  
TEST                           TEST1        TEST2                    3  
TEST                           TEST1        TEST2                    4  
  
SQL> 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                    0  
TEST                           TEST1        TEST2                    1  
TEST                           TEST1        TEST2                    2  
TEST                           TEST1        TEST2                    3  
TEST                           TEST1        TEST2                    4  
TEST                           TEST1        TEST2                    5  
TEST                           TEST1        TEST2                    6  
TEST                           TEST1        TEST2                    7  
TEST                           TEST1        TEST2                    8  
TEST                           TEST1        TEST2                    9  
TEST                           TEST1        TEST2                   10  
TEST                           TEST1        TEST2                   11  
12 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                    0  
TEST                           TEST1        TEST2                    1  
TEST                           TEST1        TEST2                    2  
TEST                           TEST1        TEST2                    3  
TEST                           TEST1        TEST2                    4  
  
SQL> 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 1000  
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  
TEST                           TEST2        TEST2                    1  
TEST                           TEST2        TEST2                    2  
TEST                           TEST2        TEST2                    3  
TEST                           TEST2        TEST2                    4  
TEST                           TEST2        TEST2                    5  
TEST                           TEST2        TEST2                    6  
TEST                           TEST2        TEST2                    7  
TEST                           TEST2        TEST2                    8  
TEST                           TEST2        TEST2                    9  
TEST                           TEST2        TEST2                   10  
TEST                           TEST2        TEST2                   11  
12 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