1.创建分区表空间
create tablespace T_T_REGIST_USER_TBS1 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS1_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS2 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS2_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS3 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS3_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS4 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS4_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS5 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS5_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS6 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS6_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS7 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS7_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS8 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS8_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace T_T_REGIST_USER_TBS9 datafile '+DB_DATA/dbpay/datafile/T_T_REGIST_USER_TBS9_01.dbf' size 1000m autoextend on maxsize 30g;
create tablespace PPALM_IDX datafile '+DB_DATA/dbpay/datafile/PPALM_IDX01.dbf' size 1000m autoextend on maxsize 30g;
PPALM_IDX
2.创建分区表
create table ppalm.T_T_REGIST_USER_NEW
(
channel_id VARCHAR2(4) not null,
phone_area VARCHAR2(4),
user_id VARCHAR2(32) not null,
user_type CHAR(1),
regist_date NUMBER(8) not null,
ver_code VARCHAR2(12) not null,
bind_date NUMBER(8),
unbind_date NUMBER(8),
lock_date CHAR(14),
ver_msg CHAR(64),
phone_no VARCHAR2(16),
email VARCHAR2(64),
id_type VARCHAR2(4),
id_no VARCHAR2(64),
before_lock_stat CHAR(1),
regist_stat CHAR(1),
regist_msg VARCHAR2(128),
mer_id VARCHAR2(16),
cancel_date NUMBER(8)
)partition by range(regist_date)
(
partition T_T_REGIST_USER_1 values less than ('20140101') tablespace T_T_REGIST_USER_TBS1,
partition T_T_REGIST_USER_2 values less than ('20140301') tablespace T_T_REGIST_USER_TBS2,
partition T_T_REGIST_USER_3 values less than ('20140501') tablespace T_T_REGIST_USER_TBS3,
partition T_T_REGIST_USER_4 values less than ('20140701') tablespace T_T_REGIST_USER_TBS4,
partition T_T_REGIST_USER_5 values less than ('20140901') tablespace T_T_REGIST_USER_TBS5,
partition T_T_REGIST_USER_6 values less than ('20141101') tablespace T_T_REGIST_USER_TBS6,
partition T_T_REGIST_USER_7 values less than ('20150101') tablespace T_T_REGIST_USER_TBS7,
partition T_T_REGIST_USER_8 values less than ('20150301') tablespace T_T_REGIST_USER_TBS8,
partition T_T_REGIST_USER_9 values less than (maxvalue) tablespace T_T_REGIST_USER_TBS9);
alter table ppalm.T_T_REGIST_USER_NEW nologging;
3.查看分区表
select a.owner,a.table_name,a.partitioning_type,a.partition_count,a.partitioning_key_count,a.def_logging,a.def_tablespace_name from DBA_PART_TABLES A where a.owner='PPALM';
4.查看分区数据文件
select b.table_owner,b.table_name,b.partition_name,b.tablespace_name,b.high_value,b.num_rows,b.logging from DBA_TAB_PARTITIONS b where b.table_owner='PPALM';
5.开始同步
1).在线重定义的表自行验证，看该表是否可以重定义，
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('PPALM','T_T_REGIST_USER');
2).执行表的在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PPALM', 'T_T_REGIST_USER', 'T_T_REGIST_USER_NEW');
3).执行把中间表的内容和数据源表进行同步。
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('PPALM', 'T_T_REGIST_USER', 'T_T_REGIST_USER_NEW');
4).执行结束在线定义过程
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('PPALM', 'T_T_REGIST_USER', 'T_T_REGIST_USER_NEW');
6.创建索引
alter table ppalm.T_T_REGIST_USER add primary key (USER_ID) using index tablespace PPALM_IDX;
create index ppalm.IDX_REGIST_USER_2 on ppalm.T_T_REGIST_USER (USER_ID, REGIST_STAT) local;
create index ppalm.IDX_REGIST_USER_3 on ppalm.T_T_REGIST_USER (REGIST_STAT, CANCEL_DATE) local;
create index ppalm.IDX_REGIST_USER_IDNO on ppalm.T_T_REGIST_USER (ID_NO) local;
alter table ppalm.T_T_REGIST_USER logging;
7.删除分区
ALTER TABLE pp_user.T_U_E_USER DROP PARTITION T_U_E_USER_10;
8.添加分区
ALTER TABLE pp_user.T_U_E_USER ADD PARTITION T_U_E_USER_10 VALUES LESS THAN(to_date('2014-07-01','yyyy-mm-dd')) tablespace T_U_E_USER1407;
