TRUNCATETABLE后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 1409286144 TRANLOG_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 -------------------- 1344 SQL> 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 0 TRANLOG_201610 0 SQL>
进行这个操作之后，会让索引失效，记得要重建索引。所以生产环境上慎用！！！
案例测试：
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> COMMIT 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 VALID T1_Y ECITY T1 VALID SQL> 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 UNUSABLE T1_Y ECITY T1 UNUSABLE SQL> 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 VALID SQL> 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 UNUSABLE SQL> 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';
名称栏目：TRUNCATETABLE后SEGMENT_SIZE没变？
本文路径：http://azwzsj.com/article/jiooei.html
