SEGMENT_SIZE hasn't changed since TRUNCATE TABLE?
In the test environment, the historical transaction table can be emptied, only the table structure needs to be preserved.
So I wanted to clean up the space through TRUNCATE, but I found that after TRUNCATE, the size of SEGMENT did not change.
Query found that because the table's INITIAL_EXTENT is inherently large
SQL> select TABLE_NAME,INITIAL_EXTENT from dba_tables where TABLE_NAME='TRANLOG_201610';TABLE_NAME INITIAL_EXTENT------------------------------ --------------TRANLOG_201610 1409286144TRANLOG_201610 65536
Why is this Initial_Extent so big?
I looked up the table creation statement for this table.
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"
As you can see, when creating the table, this INITIAL setting is too large.
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>
After doing this, the index will be invalidated, remember to rebuild the index. Be careful in the production environment!!
Case tests:
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';
