ITPub博客

首页 > Linux操作系统 > Linux操作系统 > truncate table

truncate table

原创 Linux操作系统 作者:psufnxk2000 时间:2013-09-23 23:33:49 0 删除 编辑
默认truncate table时会删掉所有的行,并且保留 minextents的大小。
加上drop all storage之后,会删掉所有的空间

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> CREATE TABLE T (ID NUMBER)  STORAGE(INITIAL 65536);

Table created.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';    --11g中默认是不分配segment的

no rows selected

SQL> INSERT INTO T VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';

BYTES/1024/1024
---------------
          .0625

SQL> TRUNCATE TABLE T;                                                 ---truncate之后,保留了初始化的空间

Table truncated.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';

BYTES/1024/1024
---------------
          .0625

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert into t  select rownum from dual connect by rownum<=100000;

100000 rows created.

SQL> insert into t  select rownum from dual connect by rownum<=100000;

100000 rows created.

SQL> /

100000 rows created.

SQL> /

100000 rows created.

SQL> /

100000 rows created.

SQL> /

100000 rows created.

SQL> /

100000 rows created.

SQL> commit;

Commit complete.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';

BYTES/1024/1024
---------------
              9

SQL> truncate table t;

Table truncated.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';       ---truncate之后,保留了初始化的空间

BYTES/1024/1024
---------------
          .0625

SQL> 
SQL> truncate table t drop all;
truncate table t drop all
                        *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword


SQL> truncate table t drop all STORAGE ;                                      --truncate时加入drop all STORAGE也删掉了初始分配的空间

Table truncated.

SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T';

no rows selected

SQL> 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-773267/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    616188