您好,登錄后才能下訂單哦!
測試環(huán)境中,歷史的交易表可以清空,僅需保留表結(jié)構(gòu)。
于是想通過TRUNCATE清理空間,結(jié)果發(fā)現(xiàn)TRUNCATE 之后,SEGMENT 的大小并沒有改變。
查詢發(fā)現(xiàn),是由于該表的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
那到底是為什么這個(gè)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"
可以看到,建表的時(shí)候,這個(gè)INITIAL 設(shè)置太大了
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>
進(jìn)行這個(gè)操作之后,會讓索引失效,記得要重建索引。所以生產(chǎn)環(huán)境上慎用!?。?/span>
案例測試:
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';
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。