Oracle普通表->分区表转换(9亿数据量)

create table T_PART
(
……
)
partition by range(time_stamp)(
  partition P20140601 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DBS_D_JINGYU
);

alter table t_part nologging;

rename T to T_old;

alter session enable parallel dml; 

insert /*+ append parallel(p,10) */ into t_part p select /*+ parallel(n,10) */ * from T_old n;

commit;

explain plan for insert /*+ append parallel(p,10) */ into t_part p select /*+ parallel(n,10) */ * from T_old n;

SQL> @/home/oracle/insert

~~~~~~~~~~~~~~~~~~~~~~~~~

已创建908792694行。

已用时间:  02: 09: 37.94

提交完成。

已用时间:  00: 08: 13.76

alter index PK_T rename to PK_T_bak;
alter table T_old rename constraint PK_T to PK_T_bak;
alter index IDX_T_2 rename to IDX_T_2_bak;
alter index IDX_T_3 rename to IDX_T_3_bak;

create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  04: 39: 53.10
alter table T_PART add constraint PK_T primary key (OID, TIME_STAMP, SERIAL_NO, CITY_ID);
表已更改。
已用时间:  00: 00: 00.43

create index IDX_T_2 on T_PART (TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 27: 49.92
create index IDX_T_3 on T_PART (TIME_STAMP, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 19: 06.74

alter index PK_T logging noparallel;
alter index IDX_T_2 logging noparallel;
alter index IDX_T_3 logging noparallel;
alter table T_PART logging;

SQL> create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_i_jingyu nologging parallel 32;

create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_i_jingyu nologging parallel 32

ORA-12801: 并行查询服务器 P000 中发出错误信号

ORA-01652: 无法通过 128 (在表空间 TMP 中) 扩展 temp 段

alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp02.dbf' size 30G;

alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp03.dbf' size 30G;

alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp04.dbf' size 30G;

rename T_PART to T;

drop table T_OLD purge;