ORACLE-分割分区时，我还能读取其中的数据吗

Question

语句

• 假设我有一个按日期范围划分的架构(但没有子分区(
• 假设这个模式中的分区P1包含很多行
• 在某个时候，比方说我想分开
• 由于分区中充满了行，因此拆分时间需要1到3秒

在分割期间，我可以：

• 是否仍读取引用分区(例如P1(中包含的数据
• 仍在读取正在移动的数据或已移动到创建的新分区中

根据文档：

我没有发现官方文件中有任何说明：https://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_3001.htm#sthref3944
https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1008028
https://docs.oracle.com/database/121/VLDBG/GUID-65E169AC-3224-405E-AD1D-9FBF4D5231BA.htm
https://docs.oracle.com/database/121/VLDBG/GUID-6BB84952-7021-4CBA-91ED-180E0656E02B.htm#VLDBG00303

然而，他们有时会提到一把锁。但是这"；锁定"；避免只写或为了阅读/写作？

在你要求自己测试之前：

通常的分割时间仍然不到一秒钟，但有时我们会遇到分割引用分区的问题。此外，我们的分手时间是不可预测的。最后，我们并不总是读取这个分区中的数据，因此我们需要插入新的数据，从而填充尽可能多的分区，然后尝试拆分和读取，这非常复杂。

因此，如果有人知道答案，会更好

Answer 1

如果您处理的是范围和日期，为什么要担心拆分PARTITIONS？您可以使用间隔，每次添加新的日期(天、周、月、季度、年(时，PARTITION都会自动添加，从而减少了拆分分区的需要。

以下是每日PARTITION的设置和一些工具，如果您希望在某段时间后删除旧的PARTITION，可以重命名PARTITION并删除它们。

CREATE OR REPLACE PROCEDURE ddl(p_cmd varchar2) 
authid current_user
is
t1 pls_integer;
BEGIN 
t1 := dbms_utility.get_time; 
dbms_output.put_line(p_cmd);
execute immediate p_cmd;
dbms_output.put_line((dbms_utility.get_time - t1)/100 || ' seconds');
END;
/
CREATE TABLE PARTITION_RETENTION (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
TABLE_NAME VARCHAR2(30),
RETENTION INTERVAL DAY(3) TO SECOND(0),
CONSTRAINT
partition_retention_pk primary key (table_name),
CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
RETENTION > INTERVAL '0' DAY
),
CONSTRAINT CHK_WHOLE_DAYS CHECK (
EXTRACT(HOUR FROM RETENTION) = 0
AND EXTRACT(MINUTE FROM RETENTION) = 0
AND EXTRACT(SECOND FROM RETENTION) = 0
)
);
insert into PARTITION_RETENTION (TABLE_NAME, RETENTION) 
select 'T1', interval '10' day from dual union all
select 'T3', interval '15' day from dual union all
select 'T4', 15 * interval '1' day from dual union all
select 'T5', 5 * interval '1 00:00:00' day to second from dual;
CREATE TABLE t1 (     
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/
INSERT into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-02-01')
select dt from dt;
/
create index t1_global_ix on t1 (dt);
/

CREATE OR REPLACE PROCEDURE MaintainPartitions IS  EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);
CURSOR PartTables IS
SELECT TABLE_NAME, INTERVAL
FROM USER_PART_TABLES 
WHERE PARTITIONING_TYPE = 'RANGE' 
ORDER BY TABLE_NAME;
CURSOR TabParts(aTableName VARCHAR2) IS 
SELECT PARTITION_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE regexp_like(partition_name,'^SYS_P[[:digit:]]{1,10}')  AND
TABLE_NAME = aTableName AND
table_name not like 'BIN$%'
and    interval is not null
ORDER BY PARTITION_POSITION;
ym INTERVAL YEAR TO MONTH;
ds INTERVAL DAY TO SECOND;
newPartName VARCHAR2(30);
PERIOD TIMESTAMP;
BEGIN
FOR aTab IN PartTables LOOP 
BEGIN       
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ds;
ym := NULL; 
EXCEPTION 
WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ym;
ds := NULL;         
END;            
FOR aPart IN TabParts(aTab.TABLE_NAME) LOOP         
EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT PERIOD;
IF ds IS NOT NULL THEN
IF ds >= INTERVAL '7' DAY THEN
-- Weekly partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"IYYY"W"IW';
ELSE
-- Daily partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMMDD';
END IF;
ELSE
IF ym = INTERVAL '3' MONTH THEN
-- Quarterly partition 
EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYY"Q"Q';
ELSE
-- Monthly partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMM';
END IF;
END IF;
IF newPartName <> aPart.PARTITION_NAME THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newPartName;
END IF;             
END LOOP;
END LOOP;
END MaintainPartitions;
/
CREATE OR REPLACE PROCEDURE rebuild_index
authid current_user
is
BEGIN 
for i in (
select index_owner, index_name, partition_name, 'partition' ddl_type
from all_ind_partitions
where status = 'UNUSABLE'
union all
select owner, index_name, null, null
from all_indexes
where status = 'UNUSABLE'
)
loop
if i.ddl_type is null then
ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild parallel 4 online');
else
ddl('alter index '||i.index_owner||'.'||i.index_name||' modify '||i.ddl_type||' '||i.partition_name||' rebuild parallel 4 online');
end if;
end loop;
END;
/

EXEC MaintainPartitions;

DECLARE
CANNOT_DROP_LAST_PARTITION EXCEPTION;
PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);
CANNOT_DROP_ONLY_ONE_PARTITION EXCEPTION;
PRAGMA EXCEPTION_INIT(CANNOT_DROP_ONLY_ONE_PARTITION, -14083);
ts TIMESTAMP;

CURSOR TablePartitions IS
SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
FROM USER_PART_TABLES t
JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
JOIN PARTITION_RETENTION r USING (TABLE_NAME)
WHERE        pk.object_type     = 'TABLE' AND
t.partitioning_type = 'RANGE' AND 
REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*'); 
BEGIN
FOR aPart IN TablePartitions LOOP
EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
IF ts < SYSTIMESTAMP - aPart.RETENTION THEN
BEGIN
ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);

EXCEPTION
WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);

ddl('ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME);
WHEN CANNOT_DROP_LAST_PARTITION THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Drop last partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';

ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';            
EXCEPTION
WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN 
-- Depending on the order the "last" partition can be also the "only" partition at the same time

EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';    
DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);

ddl('ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME);                
END;
END;
END IF;
END LOOP;
rebuild_index();
END;