使用Oracle数据库。我目前正在尝试采用一个现有的间隔范围分区表(按天分区)，该表以前只在单个表空间中存储分区，并将新分区扩展到多个表空间。
我执行了以下命令来修改表:
alter table TABLE_NAME set STORE IN(TABLESPACE_1, TABLESPACE_2, TABLESPACE_3)
这工作得很好，直到我重新启用我们的清理脚本，删除超过N天的分区。一旦我开始删除分区，Round Robing发行版就会停止;新的分区将继续在与前一个相同的表空间中创建。
我能够通过创建一种锚只读分区来解决这个问题，这种分区永远不会从表中最老的分区中删除。我正在试图弄清楚为什么需要这个"锚"分区，以及我如何才能在引擎盖下找到这个原因?
这是我看到的一个例子，我无法在19语言中得到它的演示，但在18语言中运行它，看到了相同的行为。
create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6)) tablespace ROUND_ROBIN_TEST1
partition by range (UP) interval (numtodsinterval(1, 'DAY'))
subpartition by LIST (stringCol) subpartition template
(SUBPARTITION "STR01" VALUES ('01'), SUBPARTITION "STR02" values ('02')) (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-22 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-23 00:00:00');
alter table rr_test set store in (ROUND_ROBIN_TEST1, ROUND_ROBIN_TEST2, ROUND_ROBIN_TEST3);
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-25 00:00:00');
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
alter table rr_test drop partition P1;
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-28 00:00:00');
alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
select * from user_tab_partitions where table_name = 'RR_TEST';
从结果中，您可以看到，一旦我们开始删除分区，最后创建的分区会导致轮询分发停止。
RR_TEST SYS_P9280 TIMESTAMP' 2021-09-24 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9283 TIMESTAMP' 2021-09-25 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9286 TIMESTAMP' 2021-09-26 00:00:00' ROUND_ROBIN_TEST2
RR_TEST SYS_P9289 TIMESTAMP' 2021-09-27 00:00:00' ROUND_ROBIN_TEST3
RR_TEST SYS_P9292 TIMESTAMP' 2021-09-28 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9295 TIMESTAMP' 2021-09-29 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9298 TIMESTAMP' 2021-09-30 00:00:00' ROUND_ROBIN_TEST1
您可以构建一个测试用例来显示您正在观察的内容吗?这是我的，看起来不错。
SQL> create table t
2 partition by range( dte )
3 interval( numtodsinterval(1,'DAY') )
4 store in ( users,asktom,largets)
5 ( partition p1 values less than ( date '2020-01-01' ) )
6 as
7 select
8 rownum pk,
9 'name'||rownum data,
10 date '2020-01-01' + rownum/1000 dte
11 from dual
12 connect by level <= 1000*10;
Table created.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
10000
1 row selected.
SQL>
SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
P1 USERS
SYS_P68092 ASKTOM
SYS_P68093 LARGETS
SYS_P68094 USERS
SYS_P68095 ASKTOM
SYS_P68096 LARGETS
SYS_P68097 USERS
SYS_P68098 ASKTOM
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
12 rows selected.
SQL> alter table t drop partition P1;
Table altered.
SQL> alter table t drop partition SYS_P68092;
Table altered.
SQL> alter table t drop partition SYS_P68093;
Table altered.
SQL> alter table t drop partition SYS_P68094;
Table altered.
SQL> alter table t drop partition SYS_P68095;
Table altered.
SQL> alter table t drop partition SYS_P68096;
Table altered.
SQL> alter table t drop partition SYS_P68097;
Table altered.
SQL> alter table t drop partition SYS_P68098;
Table altered.
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
4 rows selected.
SQL> select max(dte) from t;
MAX(DTE)
---------
11-JAN-20
1 row selected.
SQL> insert into t
2 select
3 rownum pk,
4 'name'||rownum data,
5 date '2020-01-12' + rownum/1000 dte
6 from dual
7 connect by level <= 1000*10;
10000 rows created.
SQL>
SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
SYS_P68103 ASKTOM
SYS_P68104 LARGETS
SYS_P68105 USERS
SYS_P68106 ASKTOM
SYS_P68107 LARGETS
SYS_P68108 USERS
SYS_P68109 ASKTOM
SYS_P68110 LARGETS
SYS_P68111 USERS
SYS_P68112 ASKTOM
SYS_P68113 LARGETS
15 rows selected.
附录:
你可以进入"not round-robin "如果你遵守严格的"添加一个"，"删除一个"，这是一个问题。模型，因为一旦我们删除了一个分区(并且您已经删除了锚)，我们就会进行"重置"，即，下面的分区从STORE in子句中列出的第一个表空间开始。因为没有锚点，任何给定的分区都可能是任何表空间，所以我们不能真正依靠它来指示哪个应该是"下一个"。
例如，注意这里的STORE IN是TS3, TS1, TS2
SQL> create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6))
2 tablespace TS1
3 partition by range (UP) interval (numtodsinterval(1, 'DAY'))
4 store in (TS3, TS1, TS2 )
5 (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
Table created.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-22 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-23 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-25 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
1 row created.
SQL>
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 P1 TS1
2 SYS_P68275 TS3
3 SYS_P68276 TS1
4 SYS_P68277 TS2
5 SYS_P68278 TS3
6 SYS_P68279 TS1
7 SYS_P68280 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition P1;
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68275 TS3
2 SYS_P68276 TS1
3 SYS_P68277 TS2
4 SYS_P68278 TS3
5 SYS_P68279 TS1
6 SYS_P68280 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68275 TS3
2 SYS_P68276 TS1
3 SYS_P68277 TS2
4 SYS_P68278 TS3
5 SYS_P68279 TS1
6 SYS_P68280 TS2
7 SYS_P68281 TS3
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68276 TS1
2 SYS_P68277 TS2
3 SYS_P68278 TS3
4 SYS_P68279 TS1
5 SYS_P68280 TS2
6 SYS_P68281 TS3
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-28 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68276 TS1
2 SYS_P68277 TS2
3 SYS_P68278 TS3
4 SYS_P68279 TS1
5 SYS_P68280 TS2
6 SYS_P68281 TS3
7 SYS_P68282 TS3
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68277 TS2
2 SYS_P68278 TS3
3 SYS_P68279 TS1
4 SYS_P68280 TS2
5 SYS_P68281 TS3
6 SYS_P68282 TS3
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68277 TS2
2 SYS_P68278 TS3
3 SYS_P68279 TS1
4 SYS_P68280 TS2
5 SYS_P68281 TS3
6 SYS_P68282 TS3
7 SYS_P68283 TS3
7 rows selected.
SQL>
SQL>
SQL>
每次删除一个分区时，我都重置到STORE IN的开头并拾取TS3。现在，相同的TS2被列为第一个分区。
SQL>
SQL> create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6))
2 tablespace TS1
3 partition by range (UP) interval (numtodsinterval(1, 'DAY'))
4 store in (TS2, TS3, TS1 )
5 (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
Table created.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-22 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-23 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-25 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
1 row created.
SQL>
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 P1 TS1
2 SYS_P68284 TS2
3 SYS_P68285 TS3
4 SYS_P68286 TS1
5 SYS_P68287 TS2
6 SYS_P68288 TS3
7 SYS_P68289 TS1
7 rows selected.
SQL>
SQL> alter table rr_test drop partition P1;
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68284 TS2
2 SYS_P68285 TS3
3 SYS_P68286 TS1
4 SYS_P68287 TS2
5 SYS_P68288 TS3
6 SYS_P68289 TS1
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68284 TS2
2 SYS_P68285 TS3
3 SYS_P68286 TS1
4 SYS_P68287 TS2
5 SYS_P68288 TS3
6 SYS_P68289 TS1
7 SYS_P68290 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68285 TS3
2 SYS_P68286 TS1
3 SYS_P68287 TS2
4 SYS_P68288 TS3
5 SYS_P68289 TS1
6 SYS_P68290 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-28 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68285 TS3
2 SYS_P68286 TS1
3 SYS_P68287 TS2
4 SYS_P68288 TS3
5 SYS_P68289 TS1
6 SYS_P68290 TS2
7 SYS_P68291 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68286 TS1
2 SYS_P68287 TS2
3 SYS_P68288 TS3
4 SYS_P68289 TS1
5 SYS_P68290 TS2
6 SYS_P68291 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68286 TS1
2 SYS_P68287 TS2
3 SYS_P68288 TS3
4 SYS_P68289 TS1
5 SYS_P68290 TS2
6 SYS_P68291 TS2
7 SYS_P68292 TS2
7 rows selected.
SQL>
SQL>
然后我们恢复正常的轮询，有更多的数据
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-30 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-01 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-02 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-03 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-04 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68295 USERS
2 SYS_P68296 ASKTOM
3 SYS_P68297 LARGETS
4 SYS_P68298 USERS
5 SYS_P68299 ASKTOM
6 SYS_P68300 ASKTOM
7 SYS_P68301 ASKTOM
8 SYS_P68302 LARGETS
9 SYS_P68303 USERS
10 SYS_P68304 ASKTOM
11 SYS_P68305 LARGETS
12 SYS_P68306 USERS
12 rows selected.
无论如何我都会记录一个bug，因为我怀疑"drop-one-add-one";是一个常见的用例。