--1、源表的定义
-- Create table
create table OSGIC.IC_GIC_LIMIT
(
);
--2、目标表定义
-- Create table
create table OSGIC.IC_GIC_LIMIT_BACK
(
);
-- Create/Recreate indexes
create index OSGIC.GIE_BUSS_ID on OSGIC.IC_GIC_LIMIT_BACK (BUSS_ID);
create index OSGIC.GIE_CREATED_TIME on OSGIC.IC_GIC_LIMIT_BACK (CREATED_TIME);
create index OSGIC.GIE_ID on OSGIC.IC_GIC_LIMIT_BACK (ID);
--3、迁移条件
CREATED_TIME >=to_date('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND CREATED_TIME <to_date('2021-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
--4、确定迁移的目标分区为:
-- 根据分区表定义和迁移条件,需要迁移分区源表: SYS_P417 、 SYS_P420 、 SYS_P485,这一步骤非常重要,务必确认源端和目标端的分区。
-- 源端 目标端
-- SYS_P485 202101 SYS_P642
-- SYS_P420 202012 SYS_P648
-- SYS_P417 202011 SYS_P645
如目标分区不存在,可手动增加或者插入1条数据自动增加。
--5、 SYS_P417 交换到 SYS_P645
--(1)、创建分区交换临时表
create table OSGIC.IC_GIC_LIMIT_tmp as select * from OSGIC.IC_GIC_LIMIT where 1=2;
--(2)、交换源表到临时表
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P417); --有数据
ALTER TABLE OSGIC.IC_GIC_LIMIT exchange PARTITION SYS_P417 with table OSGIC.IC_GIC_LIMIT_tmp EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp; --有数据
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P417); --没有数据
--(3)、临时表到目标表分区
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P645); --没有数据
alter table OSGIC.IC_GIC_LIMIT_BACK exchange partition SYS_P645 with table OSGIC.IC_GIC_LIMIT_tmp EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp; --没有数据
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P645); --有数据
--6、 SYS_P420 交换到 SYS_P648
--(1)、创建分区交换临时表
create table OSGIC.IC_GIC_LIMIT_tmp2 as select * from OSGIC.IC_GIC_LIMIT where 1=2;
--(2)、交换源表到临时表
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P420); --有数据
ALTER TABLE OSGIC.IC_GIC_LIMIT exchange PARTITION SYS_P420 with table OSGIC.IC_GIC_LIMIT_tmp2 EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp2; --有数据
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P420); --没有数据
--(3)、临时表到目标表分区
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P648); --没有数据
alter table OSGIC.IC_GIC_LIMIT_BACK exchange partition SYS_P648 with table OSGIC.IC_GIC_LIMIT_tmp2 EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp2; --没有数据
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P648); --有数据
--7、 SYS_P485 交换到 SYS_P642
--(1)、创建分区交换临时表
create table OSGIC.IC_GIC_LIMIT_tmp3 as select * from OSGIC.IC_GIC_LIMIT where 1=2;
--(2)、交换源表到临时表
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P485); --有数据
ALTER TABLE OSGIC.IC_GIC_LIMIT exchange PARTITION SYS_P485 with table OSGIC.IC_GIC_LIMIT_tmp3 EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp3; --有数据
select * from OSGIC.IC_GIC_LIMIT partition (SYS_P485); --没有数据
--(3)、临时表到目标表分区
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P642); --没有数据
alter table OSGIC.IC_GIC_LIMIT_BACK exchange partition SYS_P642 with table OSGIC.IC_GIC_LIMIT_tmp3 EXCLUDING INDEXES without validation;
select * from OSGIC.IC_GIC_LIMIT_tmp3; --没有数据
select * from OSGIC.IC_GIC_LIMIT_BACK partition (SYS_P642); --有数据
--8、失效索引创建
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 ('IC_GIC_LIMIT','IC_GIC_LIMIT_BACK')) and status='UNUSABLE';
---创建索引,索引创建sql使用上一步查询的结果
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 ('IC_GIC_LIMIT','IC_GIC_LIMIT_BACK')) and status='UNUSABLE';
--9、检查
select A.STATUS, A.*
from dba_ind_partitions A
WHERE INDEX_NAME IN
(SELECT INDEX_NAME
FROM DBA_PART_INDEXES
WHERE TABLE_NAME IN ('IC_GIC_LIMIT','IC_GIC_LIMIT_BACK'));