默认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> 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博客

truncate table

最新文章