今天又学到了,哈哈
背景:
生产数据堆积过大,超10亿数据,需要将原数据表历史数据迁移到备份归档表中。生产执行脚本时出现异常:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE> PARTITION
过程
如源表A表
create A{
id vachar2(32),
CREATE_TIME DATE,
name vachar2(32),
age vachar2(32)
}partition by range (CREATE_TIME)
(
partition PART_T01 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace PCDBTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition ARR_BACK_202010 values less than (TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))......
建立back表的语句:
create A_back{
CREATE_TIME DATE,
name vachar2(32),
age vachar2(32),
id vachar2(32)
}partition by range (CREATE_TIME)
(
partition PART_T01 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace PCDBTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition ARR_BACK_202010 values less than (TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))......
进行交换分区:
--5、 SYS_P45 交换到 A_BACK_202010
--(1)、创建分区交换临时表
create table OSGPC.A_tmp as select * from OSGPC.A where 1=2;
--(2)、交换源表到临时表
ALTER TABLE OSGPC.A exchange PARTITION SYS_P45 with table OSGPC.A_tmp EXCLUDING INDEXES;
select * from OSGPC.A_tmp; --有数据
select * from OSGPC.A partition (SYS_P45); --没有数据
--(3)、临时表到目标表分区
alter table OSGPC.A_BACK exchange partition ARR_BACK_202010 with table OSGPC.A_tmp EXCLUDING INDEXES;
select * from OSGPC.A_tmp; --没有数据
select * from OSGPC.A_BACK partition (ARR_BACK_202010); --有数据
在执行临时表到目标表分区时,报错:
解决办法:
根据字面意思是字段不一致导致的异常,因此问DBA要了生产源表的字段,和我要建立的备份表字段进行比对,发现字段内容一样。但是建备份表的建表语句,字段顺序与源表的字段顺序不一致。导致的这个错误。
大坑
缘由:
因为一个表的一个分区就要四十分钟,今天又四个表要进行数据迁移,每个表要迁移四个分区,影响到了生产验证时间,因此在执行一个表的俩分区交换后就暂停了数据迁移。决定先完成生产业务验证,再进行数据迁移。
这里埋下了大坑!!!
分析
因为在数据表分区交换时,为了提高效率,sql中加了EXCLUDING INDEXES;使索引暂时移除。
因此生产环境进行这个表的查询时,查询效率大大降低,导致一个个回话堵在数据库。最终导致数据库连接占满。
解决方案:
我们赶紧联系dba执行 我们在分区交换方案的最后一步,恢复索引sql。
--9、失效索引创建:将前交换分区中EXCLUDING INDEXES移除索引的操作,恢复索引。
select 'alter index ' || index_owner || '.' ||index_name ||' rebuild partition ' || PARTITION_NAME || ' nologging parallel 16;'
from dba_ind_partitions where INDEX_NAME IN
(SELECT INDEX_NAME
FROM DBA_PART_INDEXES
WHERE TABLE_NAME IN ('A','A_BACK')) and status='UNUSABLE';
select 'alter index ' || index_owner || '.' ||index_name ||' rebuild partition ' || PARTITION_NAME || ' logging parallel 1;'
from dba_ind_partitions where INDEX_NAME IN
(SELECT INDEX_NAME
FROM DBA_PART_INDEXES
WHERE TABLE_NAME IN ('A','A_BACK')) and status='UNUSABLE';
--将第9步的查询结果都粘出来,然后作为sql执行
--10、检查
select A.STATUS, A.*
from dba_ind_partitions A
WHERE INDEX_NAME IN
(SELECT INDEX_NAME
FROM DBA_PART_INDEXES
WHERE TABLE_NAME IN ('A','A_BACK'));
索引恢复后,由DBA对数据库会话进行清理,服务恢复正常!