分区表中大数据量转移方案

[NewJacket]

--1.建表脚本
create table VV_SRD_STAT_COUNT_HOUR
(
MONWORKSTATID NUMBER(30) not null,
STATFREQUENCY NUMBER(13,7) not null,
HIERARCHY_YEAR NUMBER(4) not null,
HIERARCHY_MONTH NUMBER(2) not null,
HIERARCHY_DAY NUMBER(2) not null,
HIERARCHY_HOUR NUMBER(2) not null,
NLEVEL NUMBER(5,2),
NC NUMBER,
BN NUMBER(5,2)
)
partition by range (HIERARCHY_MONTH)
(
partition CN_JAN values less than (2)
tablespace RXSTATC_JAN_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_FEB values less than (3)
tablespace RXSTATC_FEB_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_MAR values less than (4)
tablespace RXSTATC_MAR_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_APR values less than (5)
tablespace RXSTATC_APR_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_MAY values less than (6)
tablespace RXSTATC_MAY_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_JUN values less than (7)
tablespace RXSTATC_JUN_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_JUL values less than (8)
tablespace RXSTATC_JUL_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_AUG values less than (9)
tablespace RXSTATC_AUG_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_SEP values less than (10)
tablespace RXSTATC_SEP_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_OCT values less than (11)
tablespace RXSTATC_OCT_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_NOV values less than (12)
tablespace RXSTATC_NOV_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition CN_DEC values less than (13)
tablespace RXSTATC_DEC_BSRD
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
)
);
-- Create/Recreate indexes
create bitmap index IDX_CN_HOUR on VV_SRD_STAT_COUNT_HOUR (MONWORKSTATID, STATFREQUENCY, HIERARCHY_YEAR, HIERARCHY_MONTH, HIERARCHY_DAY, HIERARCHY_HOUR);
create index IDX_CN_HOUR_MONTH on VV_SRD_STAT_COUNT_HOUR (HIERARCHY_MONTH)
tablespace RXSTATC_BNDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
)
compress;

--2.因为数据量大硬盘空间又不够，客户要求根据MONWORKSTATID导出数据（还有其他一些相关表的记录）并释放表空间

--3.初步方案

--(1).复制整表的数据(排除客户要导出的)到临时表
--(2).truncate table VV_SRD_STAT_COUNT_HOUR
--(3).复制回临时表中的数据(问题就出在这里了，用什么方法复制呢？如果用insert那肯定会疯掉，数据量至少是千万级，要命的是还有索引)

请高手给点意见吧，万分感谢！！

[NewJacket]

[Quote=引用 2 楼 sleepzzzzz 的回复:]
exp compress
[/Quote]

忽悠

[NewJacket]

[Quote=引用 5 楼 liuyi8903 的回复:]
exchange
到其他表后,直接TTS过去.是最省事最快的方法.

然后在prod端直接drop partition
[/Quote]

能否详细点，谢谢！

[suncrafted]

先删掉索引，再bulk insert，再创建索引

[sleepzzzzz]

exp compress

[csuxp2008]

学习

[jojoandy]

真接EXP出来再IMP进去

[liuyi8903]

exchange
到其他表后,直接TTS过去.是最省事最快的方法.

然后在prod端直接drop partition

[jdsnhan]

exp query

drop table or truncate table

( create table partition )

imp ignore=y

[inthirties]

[Quote=引用 1 楼 suncrafted 的回复:]
先删掉索引，再bulk insert，再创建索引
[/Quote]


索引是要重建的，数据是要insert转移的。时间肯定是很长的，方法是没有其他的。