作者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/cn.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