Oracle Alter Table Exchange分区失败，> 1000子分区

我们有一个名为STREAMING_RESULTS的列表范围 (数字，时间戳) 分区表。该表存储来自遥测设备的非常大量的流数据。根据通道分辨率，一些分区有 ~ 3000个分区。对于大多数分区，我可以交换分区而没有任何错误，但是，当我尝试交换具有1000个子分区的分区时，它会失败，并出现如下ORA-01795错误。

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01795: maximum number of expressions in a list is 1000

除了Oracle可能在内部为exchange partition命令发出的查询之外，我找不到任何出现此错误的原因。我是否缺少任何内容，或者此命令是否有这样的限制？由于大小的原因，我无法在livesql中复制该问题，但下面包含了一个删节版本。

谢谢，

CREATE TABLE STREAMING_RESULTS 
    ( 
     CHANNEL_ID NUMBER (6)  NOT NULL , 
     TIMESTAMP  TIMESTAMP (3)  NOT NULL , 
     VALUE      BINARY_DOUBLE , 
     FILTER     BINARY_FLOAT 
    ) 
    PARTITION BY LIST ( CHANNEL_ID ) 
    SUBPARTITION BY RANGE ( TIMESTAMP ) 
    ( 
    PARTITION P_DEF VALUES ( 0 ) 
    ( 
        SUBPARTITION P_DEF_SUB VALUES LESS THAN ( TO_TIMESTAMP('2016-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') ) 
        NOCOMPRESS 
    ) 
    ) 
;

CREATE TABLE INTERIM (
    channel_id NUMBER(6) not null, 
    timestamp timestamp(3) not null, 
    value binary_double, 
    filter binary_float,
    constraint TEL_PK PRIMARY KEY (channel_id, timestamp)
)
PARTITION BY RANGE(TIMESTAMP)
(
  PARTITION P_102_03_22_12_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:00:00'),
  PARTITION P_102_03_22_13_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:30:00'),
  PARTITION P_102_03_22_13_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:00:00'),
  PARTITION P_102_03_22_14_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:30:00'),
  PARTITION P_102_03_22_14_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:00:00'),
  PARTITION P_102_03_22_15_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:30:00'),
  PARTITION P_102_03_22_15_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 16:00:00'),
......
  PARTITION P_102_06_01_14_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 14:30:00'),
  PARTITION P_102_06_01_14_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:00:00'),
  PARTITION P_102_06_01_15_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:30:00'),
  PARTITION P_102_06_01_15_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:00:00'),
  PARTITION P_102_06_01_16_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:30:00'),
  PARTITION P_102_06_01_16_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:00:00'),
  PARTITION P_102_06_01_17_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:30:00'),
  PARTITION P_102_06_01_17_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:00:00'),
  PARTITION P_102_06_01_18_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:30:00'),
  PARTITION P_102_06_01_18_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:00:00'),
  PARTITION P_102_06_01_19_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:30:00'),
  PARTITION P_102_06_01_19_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 20:00:00')
) NOLOGGING 
;

ALTER TABLE STREAMING_RESULTS ADD PARTITION P_102 VALUES (102)(
 SUBPARTITION P_102_03_22_12_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:00:00'),
 SUBPARTITION P_102_03_22_13_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 13:30:00'),
 SUBPARTITION P_102_03_22_13_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:00:00'),
 SUBPARTITION P_102_03_22_14_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 14:30:00'),
 SUBPARTITION P_102_03_22_14_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:00:00'),
 SUBPARTITION P_102_03_22_15_00 VALUES LESS THAN (TIMESTAMP' 2017-03-22 15:30:00'),
 SUBPARTITION P_102_03_22_15_30 VALUES LESS THAN (TIMESTAMP' 2017-03-22 16:00:00'),
...
 SUBPARTITION P_102_06_01_14_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:00:00'),
 SUBPARTITION P_102_06_01_15_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 15:30:00'),
 SUBPARTITION P_102_06_01_15_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:00:00'),
 SUBPARTITION P_102_06_01_16_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 16:30:00'),
 SUBPARTITION P_102_06_01_16_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:00:00'),
 SUBPARTITION P_102_06_01_17_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 17:30:00'),
 SUBPARTITION P_102_06_01_17_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:00:00'),
 SUBPARTITION P_102_06_01_18_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 18:30:00'),
 SUBPARTITION P_102_06_01_18_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:00:00'),
 SUBPARTITION P_102_06_01_19_00 VALUES LESS THAN (TIMESTAMP' 2017-06-01 19:30:00'),
 SUBPARTITION P_102_06_01_19_30 VALUES LESS THAN (TIMESTAMP' 2017-06-01 20:00:00')
);

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

抱歉，我不能复制这个。以下是一个示例，最多可启动3,000 (子) 分区:

CREATE TABLE STREAMING_RESULTS  ( 
   CHANNEL_ID NUMBER (6)  NOT NULL , 
   TIMESTAMP  TIMESTAMP  NOT NULL , 
   VALUE      BINARY_DOUBLE , 
   FILTER     BINARY_FLOAT 
) PARTITION BY LIST ( CHANNEL_ID ) 
  SUBPARTITION BY RANGE ( TIMESTAMP ) 
   (  PARTITION P_DEF VALUES ( 0 ) 
   ( 
      SUBPARTITION P_DEF_SUB VALUES LESS THAN ( timestamp'2017-01-01 00:00:00' ) 
   ) 
) ;
    
declare
  ct_stmt  clob;
  alt_stmt clob;
  ts       varchar2(30);
begin
  
  ct_stmt := 'CREATE TABLE INTERIM (
  channel_id NUMBER(6) not null, 
  timestamp TIMESTAMP not null, 
  value binary_double, 
  filter binary_float
)
PARTITION BY RANGE(TIMESTAMP)
(
  PARTITION P_102_0 VALUES LESS THAN (timestamp''2017-01-01 00:00:00'')';
  
  alt_stmt := 'ALTER TABLE STREAMING_RESULTS ADD PARTITION P_102 VALUES (102) (
 SUBPARTITION P_102_0 VALUES LESS THAN (timestamp''2017-01-01 00:00:00'')';
  
  for i in 1 .. 3000 loop
    ts := to_char((timestamp'2017-01-01 00:00:00' + numtodsinterval(i, 'second')), 'yyyy-mm-dd hh24:mi:ss');
    dbms_lob.append(ct_stmt, ', PARTITION P_102_' || i || ' VALUES LESS THAN (timestamp''' || ts || ''')');
    dbms_lob.append(alt_stmt, ', SUBPARTITION P_102_' || i || ' VALUES LESS THAN (timestamp''' || ts || ''')');
  end loop;
  
  ct_stmt := ct_stmt || ')';
  alt_stmt := alt_stmt || ')';
  
  execute immediate ct_stmt;
  execute immediate alt_stmt;
end;
/

insert into STREAMING_RESULTS
  select 102, timestamp'2017-01-01 00:00:00' + numtodsinterval(level-1, 'second'), 1, 1
  from   dual
  connect by level <= 3000;
  
commit;

exec dbms_stats.gather_table_stats(user, 'STREAMING_RESULTS');
exec dbms_stats.gather_table_stats(user, 'INTERIM');

select table_name , count(*), sum(num_rows) from user_tab_partitions
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name ;

TABLE_NAME         COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  2         3,000          
INTERIM            3,001     0   

select table_name , partition_name, count(*), sum(num_rows) from user_tab_subpartitions 
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name, partition_name ;

TABLE_NAME         PARTITION_NAME  COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  P_DEF           1         0              
STREAMING_RESULTS  P_102           3,001     3,000    

ALTER TABLE STREAMING_RESULTS EXCHANGE PARTITION P_102 WITH TABLE INTERIM;

exec dbms_stats.gather_table_stats(user, 'STREAMING_RESULTS');
exec dbms_stats.gather_table_stats(user, 'INTERIM');

select table_name , count(*), sum(num_rows) from user_tab_partitions
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name ;

TABLE_NAME         COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  2         0              
INTERIM            3,001     3,000   

select table_name , partition_name, count(*), sum(num_rows) from user_tab_subpartitions 
where  table_name in ('STREAMING_RESULTS', 'INTERIM')
group by table_name, partition_name ;

TABLE_NAME         PARTITION_NAME  COUNT(*)  SUM(NUM_ROWS)  
STREAMING_RESULTS  P_DEF           1         0              
STREAMING_RESULTS  P_102           3,001     0 

您能否分享一个显示您的问题的测试用例？使用动态SQL创建分区，就像我所做的那样，以保持代码的大小!