oracle变更为大表分区表,叶摇 » Blog Archive » oracle把没有分区的普通大表改成分区表...

oracle把没有分区的普通大表改成分区表

1.根据要改成分区的表的结构创建一张一样结构的表：

create table PARA_CELL_W_HIS_EXCHANGE

(

START_TIME DATE,

NE_CELL_ID VARCHAR2(16),

NE_MSC_ID VARCHAR2(16),

NE_BSC_ID VARCHAR2(16),

NE_BTS_ID VARCHAR2(16),

LAC NUMBER(8),

CI NUMBER(8),

BSC_ID NUMBER(5),

BTS_ID NUMBER(5),

BSC_NAME VARCHAR2(128),

.

.

.

RAC NUMBER(8),

SITE_TYPE NUMBER(8),

UARFCN NUMBER(16,3),

SCRA NUMBER(16,3),

PROBEREPORTINTERVAL VARCHAR2(4000),

PROBEMAXUEPERTMU VARCHAR2(4000),

RACHCONSTANTVALUE VARCHAR2(4000),

AICHPWR VARCHAR2(4000),

AICHTRANTIME VARCHAR2(4000),

POWEROFFSETPPM VARCHAR2(4000),

MAXPREAMBLECYCLE VARCHAR2(4000),

.

.

.

.

POWERCONTRSTEPINLOOP_DL VARCHAR2(4000),

CODETHD VARCHAR2(4000)

) tablespace WCDMA

partition by range(start_time)

(

partition p1 values less than (to_date('2011-3-1','YYYY-MM-DD')) TABLESPACE WCDMA,

partition p2 values less than (to_date('2012-12-5','YYYY-MM-DD')) TABLESPACE WCDMA

);

DROP INDEX IDX_PARA_CELL_W_HIS_BSCID_1;

create index IDX_PARA_CELL_W_HIS_BSCID_1 on PARA_CELL_W_HIS_EXCHANGE (START_TIME, NE_BSC_ID) local

tablespace WCDMA;

create index IDX_PARA_CELL_W_HIS_NE_CITY_1 on PARA_CELL_W_HIS_EXCHANGE (START_TIME, NE_CELL_ID, CITY_ID) local

tablespace WCDMA;

索引要创建成本地的。

2.更改字典表个信息

ALTER TABLE PARA_CELL_W_HIS_EXCHANGE EXCHANGE PARTITION P2 WITH TABLE PARA_CELL_W_HIS INCLUDING INDEXES WITHOUT VALIDATION;

ALTER TABLE PARA_CELL_W_HIS RENAME TO BAK_PARA_CELL_W_HIS;

ALTER TABLE PARA_CELL_W_HIS_EXCHANGE RENAME TO PARA_CELL_W_HIS;

-- DROP TABLE BAK_PARA_CELL_W_HIS PURGE;

ALTER TABLE PARA_CELL_W_HIS SPLIT PARTITION P2 AT(TO_DATE('2012-7-1','YYYY-MM-DD')) INTO (PARTITION P2,PARTITION P3);

ALTER TABLE PARA_CELL_W_HIS DROP PARTITION P2;

ALTER TABLE PARA_CELL_W_HIS set interval (numtodsinterval(1,'day'));