Oracle普通表—>分區錶轉換(9億資料量)

Oracle普通表—>分區錶轉換(9億資料量)

背景介紹：

環境：Linux 5.5 + Oracle 10.2.0.4

某普通表T，由於前期設計不當沒有分區，如今幾年來的資料量已達9億+, 空間佔用大約350G，線上重定義為分區表不現實，故採取申請時間視窗停此表應用，改造為分區表。

若T表資料量適當，可選用線上重定義操作時，可參考：

1.建立分區表

-- Create table 建立分區表T_PART,分區從14年6月開始。

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

);

使用分區添加工具添加到15年6月份。

2.設定建立分區表為nologging， 重新命名原表T為T_OLD

alter table t_part nologging;

rename T to T_old;

3.並行直接路徑插入

alter session enable parallel dml; 

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

commit;

查看下insert的執行計畫，確定都能用到並行度。

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

4.為分區表建立索引

4.1 重新命名曆史表的索引名

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;

4.2 給新分區表T_PART建立主鍵及索引

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

4.3 修改索引和表為logging，noparallel

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;

4.4 遇到的問題

建立唯一性索引時報錯：

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;

5.rename表，恢複T表的相關應用

rename T_PART為T，恢複T表應用。

rename T_PART to T;

根據實際情況決定是否徹底drop掉T_OLD，釋放空間。

drop table T_OLD purge;