作者bij831 (睡貓)
看板Database
標題Re: [討論] 請問 Oracle SHRINK SPACE
時間Tue Apr 9 12:32:19 2019
※ 引述《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://webptt.com/m.aspx?n=bbs/Database/M.1554784341.A.5BA.html
1F：推 chings22: 謝謝您,我研究一下,有問題再請教 04/10 21:11
2F：推 wilsmart: 遇過的公司DBA都跟Inrfra同team而不是跟AP同team 04/22 11:51
3F：→ wilsmart: 往往都是AP已經建好了都insert的有問題才來問DBA 04/22 11:51
4F：→ wilsmart: 但已經為時以晚不然就是要再幫他們想辦法調整 04/22 11:52
5F：→ bij831: 有的公司在討論架構時會叫dba加入討論，但真的非常少見 04/23 20:57