TRUNCATE TABLE後 SEGMENT_SIZE沒有改變

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                    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';

https://www.bkjia.com/topicnews.aspx?tid=12

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151512.htm