Oracle 将子分区移动到另一个分区

askTom 2018-04-12

420

问题描述

嗨。
今天我发现了我继承的数据库上的另一个问题。
我在错误的分区中发现了这个范围子分区。

让我试着解释一下。

场景是:

具有月份值的分区按周细分，所以我希望有一个这样的层次结构:

P_17_01 January 2017
  SP_17_01_01 1st Jan Week
  SP_17_01_02 2nd Jan Week
  SP_17_01_03 3rd Jan Week
  SP_17_01_04 4th Jan Week

P_17_02 February 2017
  SP_17_02_01 1st Feb Week
  SP_17_02_02 2nd Feb Week
  SP_17_02_03 3rd Feb Week
  SP_17_02_04 4th Feb Week

看着我找到的DDL

P_17_01 January 2017
  SP_17_01_01 1st Jan Week

P_17_02 February 2017
  SP_17_01_02 2nd Jan Week
  SP_17_01_03 3rd Jan Week
  SP_17_01_04 4th Jan Week
  SP_17_02_01 1st Feb Week
  SP_17_02_02 2nd Feb Week
  SP_17_02_03 3rd Feb Week
  SP_17_02_04 4th Feb Week

找不到如何在不丢失数据的情况下将1月子分区从2月分区移动到正确的分区

谢谢

专家解答

您不应该需要移动它们，因为数据无法输入到它们中。例如

SQL> create table t ( d date )
  2  partition by range ( d )
  3  subpartition by range ( d )
  4  (
  5    partition p1 values less than (date '2000-02-01' )
  6     (
  7       subpartition p1s1 values less than (date '2000-01-07' ),
  8       subpartition p1s2 values less than (date '2000-01-14' )
  9     ),
 10    partition p2 values less than (date '2000-03-01' )
 11     (
 12       subpartition p1s3 values less than (date '2000-01-21' ),
 13       subpartition p1s4 values less than (date '2000-01-31' ),
 14       subpartition p2s1 values less than (date '2000-02-07' ),
 15       subpartition p2s2 values less than (date '2000-02-14' ),
 16       subpartition p2s3 values less than (date '2000-02-21' ),
 17       subpartition p2s4 values less than (date '2000-02-29' )
 18     )
 19  )  ;

Table created.

SQL>
SQL> begin
  2   for i in 0 .. 200 loop
  3     begin
  4       insert into t values ( date '2000-01-01'+i ) ;
  5       commit;
  6     exception
  7       when others then null;
  8     end;
  9   end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t order by 1;

D
---------
01-JAN-00
02-JAN-00
03-JAN-00
04-JAN-00
05-JAN-00
06-JAN-00
07-JAN-00
08-JAN-00
09-JAN-00
10-JAN-00
11-JAN-00
12-JAN-00
13-JAN-00
01-FEB-00
02-FEB-00
03-FEB-00
04-FEB-00
05-FEB-00
06-FEB-00
07-FEB-00
08-FEB-00
09-FEB-00
10-FEB-00
11-FEB-00
12-FEB-00
13-FEB-00
14-FEB-00
15-FEB-00
16-FEB-00
17-FEB-00
18-FEB-00
19-FEB-00
20-FEB-00
21-FEB-00
22-FEB-00
23-FEB-00
24-FEB-00
25-FEB-00
26-FEB-00
27-FEB-00
28-FEB-00

41 rows selected.

SQL>

请注意，在14 1月和31 1月之间没有任何内容进入表，因为仅在应用分区级别范围标准之后才考虑子分区。

检查每个都是空的 (应该是)，然后将它们删除，然后将新的添加到现有的1月分区中

SQL> alter table t drop subpartition p1s3;

Table altered.

SQL> alter table t modify partition p1 add subpartition p1s3 values less than (date '2000-01-21' );

Table altered.

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 将子分区移动到另一个分区

问题描述

专家解答

评论