Re: [討論] 請問 Oracle SHRINK SPACE

看板Database (資料庫)作者bij831 (睡貓)時間5年前 (2019/04/09 12:32)推噓2(2推 0噓 3→)

留言5則, 3人參與討論串2/2 (看更多)

※ 引述《chings22 (chings22)》之銘言： : 最近想對幾個大的log table先刪過期資料,再縮小空間 : google到的指令: : ALTER TABLE table_name ENABLE ROW MOVEMENT; : ALTER TABLE table_name SHRINK SPACE COMPACT; 不縮High water mark : ALTER TABLE table_name SHRINK SPACE; 有縮High water mark : ALTER TABLE table_name SHRINK SPACE CASCADE; 同上,會同時處理相關index 空間 : ALTER TABLE table_name DISABLE ROW MOVEMENT; : 請問 : 1.如果正在做 SHRINK SPACE, 這時有人insert資料到此table,這有沒關係? : 2.SHRINK SPACE CASCADE 會同時處理相關index 空間,那在做完SHRINK後,還需要 : rebuild index嗎? 純分享工作心得，如有誤請大家指點，先說重點 1.盡可能用local index，維護方便，隨時可以做add、dorp、truncate partition 2.global index在drop、truncate partition要記著加update global index，但沒支援o nline要選影響最小的時間做簡單範例，DB:11.2.0.1 >>>global index的維護問題和解法<<< --建立測試table CREATE TABLE LOCAL_PT ( ID_PK VARCHAR2(100 BYTE) , CREATE_TIME DATE NOT NULL ) PARTITION BY RANGE (CREATE_TIME) ( PARTITION P_TIME_2018 VALUES LESS THAN (TO_DATE('2019-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')), PARTITION P_TIME_2019 VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')), PARTITION P_TIME_2020 VALUES LESS THAN (TO_DATE('2021-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')) ); CREATE UNIQUE INDEX IDX_LOCAL_PT_CREATE_TIME ON LOCAL_PT (CREATE_TIME) LOCAL ; --建立global index CREATE UNIQUE INDEX IDX_PK_LOCAL_PT_ID_PK ON LOCAL_PT (ID_PK) GLOBAL PARTITION BY RANGE (ID_PK) ( PARTITION ID_PK_2018 VALUES LESS THAN (TO_DATE('2019-01-01 00:00:00','YYYY-M M-DD HH24:MI:SS')), PARTITION ID_PK_2019 VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00','YYYY-M M-DD HH24:MI:SS')), PARTITION ID_PK_2020 VALUES LESS THAN (TO_DATE('2021-01-01 00:00:00','YYYY-M M-DD HH24:MI:SS')), PARTITION ID_PK_max VALUES LESS THAN (MAXVALUE) ); ALTER TABLE LOCAL_PT ADD CONSTRAINT IDX_PK_LOCAL_PT_ID_PK PRIMARY KEY (ID_PK); --輸入測試資料 insert into LOCAL_PT values (1,to_date('2018-01-02','yyyy-mm-dd')); insert into LOCAL_PT values (2,to_date('2019-01-02','yyyy-mm-dd')); insert into LOCAL_PT values (3,to_date('2020-01-02','yyyy-mm-dd')); commit; select * from LOCAL_PT; execute dbms_stats.gather_table_stats(ownname => 'BINHU',tabname => 'LOCAL_PT' ,cascade => true); --查看index狀態 select index_owner,index_name,partition_name,status from DBA_IND_PARTITIONS wh ere index_owner='BINHU' and index_name ='IDX_PK_LOCAL_PT_ID_PK'; --進行刪除特定的parition ALTER TABLE LOCAL_PT DROP PARTITION P_TIME_2019; --查看index狀態已為不可使用 select index_owner,index_name,partition_name,status from DBA_IND_PARTITIONS wh ere index_owner='BINHU' and index_name ='IDX_PK_LOCAL_PT_ID_PK'; --解決方法為加入update global index，但index重建需要時間和會有lock情況 ALTER TABLE LOCAL_PT DROP PARTITION P_TIME_2019 update global indexes; >>>local index的使用問題<<< --建立測試table CREATE TABLE LOCAL_PT ( ID_PK VARCHAR2(100 BYTE) , CREATE_TIME DATE NOT NULL ) PARTITION BY RANGE (CREATE_TIME) ( PARTITION P_TIME_2018 VALUES LESS THAN (TO_DATE('2019-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')), PARTITION P_TIME_2019 VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')), PARTITION P_TIME_2020 VALUES LESS THAN (TO_DATE('2021-01-01 00:00:00','YYYY- MM-DD HH24:MI:SS')) ); CREATE UNIQUE INDEX IDX_LOCAL_PT_CREATE_TIME ON LOCAL_PT (CREATE_TIME) LOCAL ; --建立pk發生問題，因為不包含到分割partition的CREATE_TIME欄位 CREATE UNIQUE INDEX IDX_PK_LOCAL_PT_ID_PK ON LOCAL_PT (ID_PK) LOCAL; ORA-14039: 分割資料欄必須形成一個 UNIQUE 索引之索引鍵資料欄的子集 --那就把他包含進來建立pk index了，終於過關 CREATE UNIQUE INDEX IDX_PK_LOCAL_PT_ID_PK ON LOCAL_PT (CREATE_TIME,ID_PK) LOCA L; ALTER TABLE LOCAL_PT ADD CONSTRAINT IDX_PK_LOCAL_PT_ID_PK PRIMARY KEY (CREATE_ TIME,ID_PK); --輸入測試資料，有一個邏輯的bug，可以在不同的日期區段中輸入相同的ID_PK值 insert into LOCAL_PT values (1,to_date('2018-01-02','yyyy-mm-dd')); insert into LOCAL_PT values (1,to_date('2019-01-02','yyyy-mm-dd')); commit; 一般來說是由ap來解決pk的問題，並且如是存放log的話，那更沒有pk的問題才對，也就是沒有需要pk這種需求如是較重要的記錄，如金錢、訂單這類的交易記錄，那就要好好跟開發人員討論欄位該怎設計了 execute dbms_stats.gather_table_stats(ownname => 'BINHU',tabname => 'LOCAL_PT' ,cascade => true); --查看index狀態 select index_owner,index_name,partition_name,status from DBA_IND_PARTITIONS wh ere index_owner='BINHU' and index_name ='IDX_PK_LOCAL_PT_ID_PK'; --進行刪除特定的parition ALTER TABLE LOCAL_PT DROP PARTITION P_TIME_2019; --查看index狀態沒有被影響到 select index_owner,index_name,partition_name,status from DBA_IND_PARTITIONS wh ere index_owner='BINHU' and index_name ='IDX_PK_LOCAL_PT_ID_PK'; -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 42.72.5.0 ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1554784341.A.5BA.html