两个结构相同的range-hash复合分区之间做交换，报错ora 14292

qq69033472

oracle 10.2.0.5 on linux 5

table 1：
partition by range (AGGTIME)
subpartition by hash (ID, IID, NCELL, SITEURL, COUNTRY, DISTRICT, CITY, TELECORP, TYPE)
(
  partition PART_PROV_20120501 values less than (TO_DATE(' 2012-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_STATDATA
    pctfree 0
    initrans 1
    maxtrans 255
  (
    subpartition SYS_SUBP24020 tablespace TS_STATDATA,
    subpartition SYS_SUBP24021 tablespace TS_STATDATA,
    subpartition SYS_SUBP24022 tablespace TS_STATDATA,
    subpartition SYS_SUBP24023 tablespace TS_STATDATA,
    subpartition SYS_SUBP24024 tablespace TS_STATDATA,
    subpartition SYS_SUBP24025 tablespace TS_STATDATA,
    subpartition SYS_SUBP24026 tablespace TS_STATDATA,
    subpartition SYS_SUBP24027 tablespace TS_STATDATA）

table 2：
partition by range (AGGTIME)
subpartition by hash (ID, IID, NCELL, SITEURL, COUNTRY, DISTRICT, CITY, TELECORP, TYPE)
(
  partition hist_day_20120501 values less than (TO_DATE(' 2012-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_STATDATA
    pctfree 0
    initrans 1
    maxtrans 255
  (
    subpartition SYS_DAY_00000 tablespace TS_STATDATA,
    subpartition SYS_DAY_00001 tablespace TS_STATDATA,
    subpartition SYS_DAY_00002 tablespace TS_STATDATA,
    subpartition SYS_DAY_00003 tablespace TS_STATDATA,
    subpartition SYS_DAY_00004 tablespace TS_STATDATA,
    subpartition SYS_DAY_00005 tablespace TS_STATDATA,
    subpartition SYS_DAY_00006 tablespace TS_STATDATA,
    subpartition SYS_DAY_00007 tablespace TS_STATDATA
  )

结构都一样，但是alter table table1 exchange partition PART_PROV_20120501 with table table2;
就会报错ora-14292 partitioning type of table must match subpartitioning type of composit partition
请大神帮忙~~谢谢

Maclean Liu(刘相兵

[oracle@mlab2 NewFolder]$ oerr ora 14292
14292, 00000, "Partitioning type of table must match subpartitioning type of composite partition"
// *Cause:  When exchanging a partitioned table with a composite partition
//          the partitioning type of the table must match the subpartitioning
//          type of the composite partition.
// *Action: Ensure that the partitioning type of partitioned table is the
//          same as the subpartitioning type of the composite partition.


你先建2张临时表

然后 exchange  分区A  with 临时表1
        exchange  分区B  with 临时表2
再    exchange  分区B  with 临时表1
再    exchange  分区A  with 临时表2

qq69033472

Maclean Liu(刘相兵 发表于 2014-2-19 11:15
[oracle@mlab2 NewFolder]$ oerr ora 14292
14292, 00000, "Partitioning type of table must match subpa ...

解决了，谢谢刘大，那个报错信息理解错了，这个range-hash复合索引，要建子分区类型的分区表做临时表，也就是要建个hash分区表作为临时表。。。谢谢拉~~