TRUNCATE TABLE后 SEGMENT_SIZE没变 ？

测试环境中，历史的交易表可以清空，仅需保留表结构。

于是想通过TRUNCATE清理空间，结果发现TRUNCATE 之后，SEGMENT 的大小并没有改变。

查询发现，是由于该表的INITIAL_EXTENT本来就很大

SQL> select TABLE_NAME,INITIAL_EXTENT from dba_tables where TABLE_NAME='TRANLOG_201610';TABLE_NAME                     INITIAL_EXTENT------------------------------ --------------TRANLOG_201610                     1409286144TRANLOG_201610                          65536

那到底是为什么这个INITIAL_EXTENT这么大呢？

查询了一下该表的建表语句

select dbms_metadata.get_ddl('TABLE','TRANLOG_201610','ECITY') mb  from dual;  CREATE TABLE "ECITY"."TRANLOG_201610"    (  "MERID" CHAR(3) NOT NULL ENABLE,   "ORDERNO" VARCHAR2(64) NOT NULL ENABLE,   .......  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 226492416 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "TS_ECITYDB"  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 1409286144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "TS_ECITYDB"

可以看到，建表的时候，这个INITIAL 设置太大了

SQL> select 1409286144/1024/1024 from dual;1409286144/1024/1024--------------------                1344SQL> ALTER TABLE ECITY.TRANLOG_201610 MOVE STORAGE(INITIAL 65536 NEXT 65536);Table altered.SQL> select TABLE_NAME,INITIAL_EXTENT/1024/1024 MB from dba_tables where TABLE_NAME='TRANLOG_201610';TABLE_NAME                            MB------------------------------ ---------------TRANLOG_201610                         0TRANLOG_201610                         0SQL>

进行这个操作之后，会让索引失效，记得要重建索引。所以生产环境上慎用！！！

案例测试：

create table t1 (x int CONSTRAINT pk_t  primary key,y number)  STORAGE(INITIAL 1409286144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_ECITYDB";create table t1 (x int CONSTRAINT pk_t  primary key,y number)  STORAGE(INITIAL 1409286144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_ECITYDB"SQL> insert into t1 values (1,1);SQL> insert into t1 values (2,1);SQL> insert into t1 values (3,2);SQL> COMMITSQL> SELECT B.index_name,B.table_owner,B.table_name,B.status FROM DBA_INDEXES B where table_owner='ECITY' and table_name='T1'; INDEX_NAME                               TABLE_OWNER          TABLE_NAME STATUS---------------------------------------- -------------------- ---------- ------------------------PK_T                                     ECITY                T1         VALIDT1_Y                                     ECITY                T1         VALIDSQL> ALTER TABLE ECITY.T1 MOVE STORAGE(INITIAL 65536 NEXT 65536);Table altered.SQL> SELECT B.index_name,B.table_owner,B.table_name,B.status FROM DBA_INDEXES B where table_owner='ECITY' and table_name='T1'; INDEX_NAME                               TABLE_OWNER          TABLE_NAME STATUS---------------------------------------- -------------------- ---------- ------------------------PK_T                                     ECITY                T1         UNUSABLET1_Y                                     ECITY                T1         UNUSABLESQL> SQL> SELECT B.index_name,B.table_owner,B.table_name,B.status FROM DBA_INDEXES B where table_owner='ECITY' and table_name='T1';INDEX_NAME                               TABLE_OWNER          TABLE_NAME STATUS---------------------------------------- -------------------- ---------- ------------------------PK_T                                     ECITY                T1         VALIDSQL> ALTER TABLE ECITY.T1 MOVE STORAGE(INITIAL 65536 NEXT 65536);Table altered.SQL> SELECT B.index_name,B.table_owner,B.table_name,B.status FROM DBA_INDEXES B where table_owner='ECITY' and table_name='T1';INDEX_NAME                               TABLE_OWNER          TABLE_NAME STATUS---------------------------------------- -------------------- ---------- ------------------------PK_T                                     ECITY                T1         UNUSABLESQL>SELECT B.index_name,B.table_owner,B.table_name,B.status FROM DBA_INDEXES B where status='UNUSABLE' and table_owner='ECITY';SELECT DISTINCT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM DBA_INDEXES B where status='UNUSABLE' and table_owner='ECITY';