--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那肯定会疯掉,数据量至少是千万级,要命的是还有索引)请高手给点意见吧,万分感谢!!
解决方案 »
- TOAD 连接 oracle10G 报错!
- oracle 访问FTP目录的文本,然后解析文件,更新到本地数据库中,怎么做
- 用什么工具Oracle 8/9i 可以导出数据库到excel表?
- 为什么在WINDOWS XP下安装了ORACLE8.16后,没有办法正常启动
- 求救!如何才能找到我的数据库?
- 哪里有oracle815的下载。谢谢
- 高手求助:ORACLE在NT中如何设置NT代理所在节点首选身份证明
- 请问Oracel数据库的本地服务名数量有限制吗?
- 请教:DELPHI中如何存取ORACLE的CLOB或BLOB字段?
- PL/SQL Developer V5.0.3.527注册机谁有啊!在线等待
- 请问总是提示not a group by expression
- 我又一个疯狂的想法!请各位ORACLE达人指点迷津,谢谢!
到其他表后,直接TTS过去.是最省事最快的方法.然后在prod端直接drop partition