Oracle 从交换的分区中跨表的不同月度分区移动记录，拆分交换的分区似乎不起作用

如何在执行exchange分区后将记录移动到相应的分区，拆分已交换的分区似乎不起作用

After exchanging partition with a table, the partitioned table does not have the records distributed across the appropriate partitions but it just resides in a same partition in which it was swapped

--DROPPING EXISTING TABLE
DROP TABLE requests PURGE;

--CREATING NON-PARTITIONED TABLE
CREATE TABLE requests (
    reqsource  NUMBER,
    reqtime    TIMESTAMP(6),
    req        CLOB
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
    DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
--TABLESPACE "REQ_DATA"
    LOB ( "REQ" ) STORE AS BASICFILE (
  --      TABLESPACE "REQ_DATA"
        ENABLE STORAGE IN ROW
        CHUNK 8192
        RETENTION
        NOCACHE LOGGING
        STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
        DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
    );

--DROPPING EXISTING TABLE
DROP TABLE requests_part;

--CREATING PARTITIONED TABLE
CREATE TABLE requests_part (
    reqsource  NUMBER,
    reqtime    TIMESTAMP(6),
    req        CLOB
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
    DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
    --TABLESPACE "REQ_DATA"
        LOB ( "REQ" ) STORE AS BASICFILE (
        --TABLESPACE "REQ_DATA"
            ENABLE STORAGE IN ROW
            CHUNK 8192
            RETENTION
            NOCACHE LOGGING
            STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
            DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
        )
    -- MONTHLY PARTITIONED ON TIMESTAMP COLUMN
        PARTITION BY RANGE (
            "REQTIME"
        ) INTERVAL ( numtoyminterval(1, 'MONTH') ) ( PARTITION "OLD_DATA"
            VALUES LESS THAN ( TIMESTAMP ' 2013-01-01 00:00:00' )
        --TABLESPACE "REQ_DATA" 
        );

ALTER TABLE requests_part EXCHANGE PARTITION old_data WITH TABLE requests WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

SELECT
    *
FROM
    user_tab_partitions
WHERE
    table_name = 'REQUESTS_PART';
    
TABLE_NAME COMPOSITE PARTITION_NAME HIGH_VALUE                        HIGH_VALUE_LENGTH PARTITION_POSITION
REQUESTS_PART  NO          OLD_DATA TIMESTAMP' 2013-01-01 00:00:00'         31                  1

复制

所有行仅存在于分区OLD_DATA中，不会创建新分区。当我尝试分割该分区时，出现以下错误

  ALTER TABLE results_part
    SPLIT PARTITION 
    OLD_DATA
    FOR(
    TIMESTAMP' 2013-02-01 00:00:00'
--    TO_DATE('01-MAY-2007','dd-MON-yyyy')
    )
    AT (
--    TO_DATE('15-MAY-2007','dd-MON-yyyy')
TIMESTAMP' 2013-01-01 00:00:00'
    )
Error report -
ORA-14080: partition cannot be split along the specified high bound
14080. 00000 -  "partition cannot be split along the specified high bound"
*Cause:    User attempted to split a partition along a bound which
           either collates higher than that of the partition to be split or
           lower than that of a partition immediately preceding the one
           to be split
*Action:   Ensure that the bound along which a partition is to be split
           collates lower than that of the partition to be split and
           higher that that of a partition immediately preceding the one
           to be split
复制

如何将交换分区中的数据跨各个分区分发 ？？？

问题是你正在这样做:

without validation
复制

因此，数据库不会检查您正在交换的行是否属于目标分区; 它相信您已经事先验证了这一点。

如果不这样做，则可能会导致错误分区中的行:

create table t (
  c1 int, c2 int
);
create table tpart (
  c1 int, c2 int
) partition by range ( c1 ) 
  interval ( 10 ) (
    partition p0 values less than ( 11 )
  );
  
insert into t 
with rws as (
  select level x from dual
  connect by level <= 20
)
  select x, x 
  from   rws;
  
commit;
  
  
alter table tpart
  exchange partition p0 
  with table t;
  
ORA-14099: all rows in table do not qualify for specified partition
  
alter table tpart
  exchange partition p0 
  with table t
  without validation;
  
select count (*), max ( c1 ) 
from   tpart 
  partition ( p0 );
  
COUNT(*)      MAX(C1)   
      20           20 
复制

所以我们有p0中值为20的行，尽管它的上限是11!

您一次只能用一个分区交换表中的所有行。如果你有要加载到许多分区的行，你可以:

-创建一个具有足够高的上限以存储所有值的分区，然后在交换后拆分此分区
-多次运行exchange进程，仅将每个分区的行插入到exchange表中，运行exchange并重复下一个。