--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那肯定会疯掉,数据量至少是千万级,要命的是还有索引)请高手给点意见吧,万分感谢!!
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那肯定会疯掉,数据量至少是千万级,要命的是还有索引)请高手给点意见吧,万分感谢!!
解决方案 »
- 请教如何向PACKAGE BODIES里增加表啊?
- union 的问题??
- 如何用最简便的语句来实现序号无重复递增更新?(100分)
- 一个简单的问题,关于多表联接查询.
- ***********Oracle dblink的问题?特急,在线等待!!!***********
- oracle9i安装过程中的问题,请高手帮助
- 如何增大回滚段? 急!
- 请问在oracle8i中如何导出数据字典
- 为什么我的oracle 在 win 2000 server 不能导入*。dmp(从别系统中导出的。)
- oracle 同步 postgresql
- 请问总是提示not a group by expression
- 我又一个疯狂的想法!请各位ORACLE达人指点迷津,谢谢!
到其他表后,直接TTS过去.是最省事最快的方法.然后在prod端直接drop partition