- 最后登录
- 2014-12-9
- 在线时间
- 11 小时
- 威望
- 31
- 金钱
- 49
- 注册时间
- 2011-10-15
- 阅读权限
- 10
- 帖子
- 24
- 精华
- 0
- 积分
- 31
- UID
- 42
|
1#
发表于 2012-2-28 22:53:04
|
查看: 6495 |
回复: 2
db version : 10.0. 2. 1
表结构的ddl为 :- create table tb_partiexchange(
- x int,
- y date,
- z varchar2(20),
- constraint pk_partiex_x primary key(x) using index local
- )partition by range(x)(
- partition pt_1 values less than(5) tablespace test1,
- partition pt_2 values less than(10) tablespace test2
- );
- create index indx_partiexchange_y on tb_partiexchange(y);
- insert into tb_partiexchange values(1, sysdate, 'pt1');
- insert into tb_partiexchange values(7, sysdate, 'pt2');
- commit;
- create table dummy_partiexchange(
- x int,
- y date,
- z varchar2(20)
- )tablespace test1;
- --结构要一样
- alter table dummy_partiexchange add constraint pk_dummypartiexchange primary key(x);
- create index indx_partiy_y on dummy_partiexchange(y);
复制代码 执行分区交换操作:
SQL> alter table tb_partiexchange exchange partition pt_1 with table dummy_partiexchange including indexes;
alter table tb_partiexchange exchange partition pt_1 with table dummy_partiexchange including indexes
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
参考文章:http://www.oracledatabase12g.com ... 94%99%E8%AF%AF.html
给出执行部分sql得到的结果:- SQL> Select TABLE_NAME,INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH, CHAR_LENGTH, DESCEND
- 2 FROM SYS.DBA_IND_COLUMNS DICN
- 3 WHERE INDEX_OWNER = '&own'
- 4 and DICN.TABLE_NAME in ('&TABNAME1','&TABNAME2')
- 5 ORDER BY INDEX_NAME, COLUMN_POSITION
- 6 /
-
- TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
- ------------------------------ ------------------------------ ------------------ --------------- ------------- ----------- -------
- TB_PARTIEXCHANGE INDX_PARTIEXCHANGE_Y Y 1 7 0 ASC
- DUMMY_PARTIEXCHANGE INDX_PARTIY Y 1 7 0 ASC
- DUMMY_PARTIEXCHANGE PK_DUMMYPARTIEXCHANGE X 1 22 0 ASC
- TB_PARTIEXCHANGE PK_PARTIEX_X X 1 22 0 ASC
-
- SQL>
- SQL> select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, PARTITIONED
- 2 from dba_indexes
- 3 where owner='&OWNER'
- 4 and TABLE_NAME in ('&TABNAME1', '&TABNAME2')
- 5 order by index_name
- 6 /
-
- TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS PARTITIONED
- ------------------------------ ------------------------------ --------------- ---------- -----------
- TB_PARTIEXCHANGE INDX_PARTIEXCHANGE_Y NORMAL NONUNIQUE NO
- DUMMY_PARTIEXCHANGE INDX_PARTIY NORMAL NONUNIQUE NO
- DUMMY_PARTIEXCHANGE PK_DUMMYPARTIEXCHANGE NORMAL UNIQUE NO
- TB_PARTIEXCHANGE PK_PARTIEX_X NORMAL UNIQUE YES
-
- SQL>
复制代码 比对发现分区表tb_partiexchange和交换表dummy_partiexchange的索引基本是一致的。
根据文章的提示,附件是10046的trace文件 |
|