oracle 10g online rededination--dbms_redefinition使用小記_part1

wisdomone1發表於2014-08-13
Oracle 10g

個人簡介：
   8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
   
   服務過的客戶：
          中國電信
          中國移動
          中國聯通
          中國電通
          國家電網
          四川達州商業銀行
          湖南老百姓大藥房
          山西省公安廳
          中國郵政
          北京302醫院     
          河北廊坊新奧集團公司
  
 專案經驗:
           中國電信3G專案AAA系統資料庫部署及最佳化
           中國聯通CRM資料庫效能最佳化
           中國移動10086電商平臺資料庫部署及最佳化
           湖南老百姓大藥房ERR資料庫sql最佳化專案
           四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
           四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
           北京高鐵訊號監控系統RAC資料庫部署及最佳化
           河南宇通客車資料庫效能最佳化
           中國電信電商平臺核心採購模組表模型設計及最佳化
           中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
           北京302醫院資料庫遷移實施
           河北廊坊新奧data guard部署及最佳化
           山西公安廳身份證審計資料庫系統故障評估
           
  
 聯絡方式：
          手機：18201115468
           qq   :   305076427
           qq微博: wisdomone1
           
           新浪微博：wisdomone9
          
           qq群：275813900    
          
           itpub部落格名稱：wisdomone1    http://blog.itpub.net/9240380/




測試目標：

本文主要測試如果某個分割槽指定表空間大小，導致線上重定義失敗，後續如何處理的情況


測試環境：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production


測試步驟：

  • 建立測試表
SQL> create table t_non_partition(card_id int,prov_code int,card_name int);

Table created.

  • 插入資料
SQL> insert into t_non_partition select level,mod(level,12),level from dual connect by level<=2000000;

2000000 rows created.

SQL> commit;

Commit complete.

SQL> insert into t_non_partition select level,mod(level,12),level from dual connect by level<=2000000;

2000000 rows created.

SQL> commit;

Commit complete.

  • 檢視測試表的大小
SQL> col owner for a15
SQL> col segment_name for a30
SQL> set linesize 300
SQL> select owner,segment_name,bytes/1024/1024 from dba_segments where lower(segment_name)='t_non_partition';

OWNER           SEGMENT_NAME                   BYTES/1024/1024
--------------- ------------------------------ ---------------
TBS_11204       T_NON_PARTITION                             88

SQL> 


  • 檢視基於列prov_code的資料分佈情況
SQL> select prov_code,count(*) from tbs_11204.t_non_partition group by prov_code order by 1;

 PROV_CODE   COUNT(*)
---------- ----------
         0     333332
         1     333334
         2     333334
         3     333334
         4     333334
         5     333334
         6     333334
         7     333334
         8     333334
         9     333332
        10     333332

 PROV_CODE   COUNT(*)
---------- ----------
        11     333332

12 rows selected.

  • 測試表經線上重定義為分割槽表每個分割槽的大小（這裡表的資料分佈很均勻）
SQL> select prov_code,count(*)/4000000*88 as partition_mb from tbs_11204.t_non_partition group by prov_code order by 1;

 PROV_CODE PARTITION_MB
---------- ------------
         0     7.333304
         1     7.333348
         2     7.333348
         3     7.333348
         4     7.333348
         5     7.333348
         6     7.333348
         7     7.333348
         8     7.333348
         9     7.333304
        10     7.333304

 PROV_CODE PARTITION_MB
---------- ------------
        11     7.333304

12 rows selected.


  • 建立線上重定義所需的12個表空間，僅其中一個表空間要小於上述值
create tablespace tbs_p0  datafile '/home/ora10g/tbs_p0.dbf' size 6m autoextend off;
create tablespace tbs_p1  datafile '/home/ora10g/tbs_p1.dbf' size 8m autoextend off;
create tablespace tbs_p2  datafile '/home/ora10g/tbs_p2.dbf' size 8m autoextend off;
create tablespace tbs_p3  datafile '/home/ora10g/tbs_p3.dbf' size 8m autoextend off;
create tablespace tbs_p4  datafile '/home/ora10g/tbs_p4.dbf' size 8m autoextend off;
create tablespace tbs_p5  datafile '/home/ora10g/tbs_p5.dbf' size 8m autoextend off;
create tablespace tbs_p6  datafile '/home/ora10g/tbs_p6.dbf' size 8m autoextend off;
create tablespace tbs_p7  datafile '/home/ora10g/tbs_p7.dbf' size 8m autoextend off;
create tablespace tbs_p8  datafile '/home/ora10g/tbs_p8.dbf' size 8m autoextend off;
create tablespace tbs_p9  datafile '/home/ora10g/tbs_p9.dbf' size 8m autoextend off;
create tablespace tbs_p10  datafile '/home/ora10g/tbs_p10.dbf' size 8m autoextend off;
create tablespace tbs_p11  datafile '/home/ora10g/tbs_p11.dbf' size 8m autoextend off;


  • 確認上述12個表空間建立成功

SQL>  select tablespace_name,file_name,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%';

TABLESPACE_NAME      FILE_NAME                                                            MB AUT
-------------------- ------------------------------------------------------------ ---------- ---
TBS_P0               /home/ora10g/tbs_p0.dbf                                               6 NO
TBS_P1               /home/ora10g/tbs_p1.dbf                                               8 NO
TBS_P2               /home/ora10g/tbs_p2.dbf                                               8 NO
TBS_P3               /home/ora10g/tbs_p3.dbf                                               8 NO
TBS_P4               /home/ora10g/tbs_p4.dbf                                               8 NO
TBS_P5               /home/ora10g/tbs_p5.dbf                                               8 NO
TBS_P6               /home/ora10g/tbs_p6.dbf                                               8 NO
TBS_P7               /home/ora10g/tbs_p7.dbf                                               8 NO
TBS_P8               /home/ora10g/tbs_p8.dbf                                               8 NO
TBS_P9               /home/ora10g/tbs_p9.dbf                                               8 NO
TBS_P10              /home/ora10g/tbs_p10.dbf                                              8 NO

TABLESPACE_NAME      FILE_NAME                                                            MB AUT
-------------------- ------------------------------------------------------------ ---------- ---
TBS_P11              /home/ora10g/tbs_p11.dbf                                              8 NO

12 rows selected.

  • 驗證測試是否具備線上重定義條件
SQL> show user
USER is "SYS"
SQL> set serveroutput on
begin
dbms_redefinition.can_redef_table('tbs_11204','t_non_partition',dbms_redefinition.cons_use_rowid);
end;
SQL>   2    3    4  /

PL/SQL procedure successfully completed.

  • 建立和測試相同結構的臨時表
SQL> create table t_non_partition_temp(card_id int,prov_code int,card_name int)
partition by list(prov_code)
  3  (
  4  partition p0   values(0)   tablespace tbs_p0,
  5  partition p1   values(1)   tablespace tbs_p1,
partition p2   values(2)   tablespace tbs_p2,
  7  partition p3   values(3)   tablespace tbs_p3,
  8  partition p4   values(4)   tablespace tbs_p4,
partition p5   values(5)   tablespace tbs_p5,
 10  partition p6   values(6)   tablespace tbs_p6,
 11  partition p7   values(7)   tablespace tbs_p7,
partition p8   values(8)   tablespace tbs_p8,
 13  partition p9   values(9)   tablespace tbs_p9,
 14  partition p10  values(10)  tablespace tbs_p10,
partition p11  values(11)   tablespace tbs_p11
 16  );

Table created.

  • 實施線上重定義
手工中斷線上重定義
SQL> conn /as sysdba
Connected.
SQL> begin
  2  dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /

begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

  • 中止線上重定義
注：這個用時要比正常線上重定義報錯，然後中斷線上重定義時間要消耗更多一些
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.


重施線上重定義
SQL> conn /as sysdba
Connected.
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P0 by 128 in tablespace TBS_P0
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2


  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.

  • 增加上述表空間tbs_p0的大小
SQL> alter database datafile 9 resize 8m;

Database altered.

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P0';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P0               /home/ora10g/tbs_p0.dbf                                               9          8 NO

  • 重新開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P9 by 128 in tablespace TBS_P9
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2


  • tbs_p9表空間也報空間不能擴充套件錯誤
查詢表空間tbs_p9的大小
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P9';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P9               /home/ora10g/tbs_p9.dbf                                              18          8 NO

擴充套件表空間tbs_p9到10m
SQL> alter database datafile 18 resize 10m;

Database altered.

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P9';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P9               /home/ora10g/tbs_p9.dbf                                              18         10 NO


  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.


第3次開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
3  end;
4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P10 by 128 in tablespace TBS_P10
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

悲催，又報錯了

  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.


  • 擴充套件表空間tbs_p10
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P10';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P10              /home/ora10g/tbs_p10.dbf                                             19          8 NO

SQL> alter database datafile 19 resize 10m;

Database altered.

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P10';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P10              /home/ora10g/tbs_p10.dbf                                             19         10 NO

SQL> 

  • 第4次開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P11 by 128 in tablespace TBS_P11
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

哈哈，tbs_p11表空間亦報錯了


  • 中斷線上重定義
SQL>  exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.

  • 擴充套件表空間tbs_p11
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P11';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P11              /home/ora10g/tbs_p11.dbf                                             20          8 NO

SQL> alter database datafile 20 resize 10m;

Database altered.

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and tablespace_name='TBS_P11';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P11              /home/ora10g/tbs_p11.dbf                                             20         10 NO

  • 不知次數開始線上重義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P7 by 128 in tablespace TBS_P7
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

惡夢來襲，tbs_p7報錯

  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.


擴充套件表空間tbs_p7
SQL> alter database datafile 16 resize 10m;

Database altered.

  • 煩煩開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P1 by 128 in tablespace TBS_P1
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

吼吼，tbs_p1報錯

  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.

  • 擴充套件表空間tbs_p1
SQL> alter database datafile 10 resize 10m;

Database altered.


  • 與妖魔一戰，開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P2 by 128 in tablespace TBS_P2
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

小日本的喲，太可憎了，表空間tbs_p2報錯

  • 一勞永逸，把上述未報過錯的表空間全部加大
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%' and lower(tablespace_name) in ('tbs_p2','tbs_p3','tbs_p4','tbs_p5','tbs_p6','tbs_p8');

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P2               /home/ora10g/tbs_p2.dbf                                              11          8 NO
TBS_P3               /home/ora10g/tbs_p3.dbf                                              12          8 NO
TBS_P4               /home/ora10g/tbs_p4.dbf                                              13          8 NO
TBS_P5               /home/ora10g/tbs_p5.dbf                                              14          8 NO
TBS_P6               /home/ora10g/tbs_p6.dbf                                              15          8 NO
TBS_P8               /home/ora10g/tbs_p8.dbf                                              17          8 NO
SQL> alter database datafile 11 resize 10m;

Database altered.

SQL> alter database datafile 12 resize 10m;

Database altered.

SQL> alter database datafile 13 resize 10m;

Database altered.

SQL> alter database datafile 14 resize 10m;

Database altered.

SQL> alter database datafile 15 resize 10m;

Database altered.


SQL> alter database datafile 17 resize 10m;

Database altered.



  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.


  • 一線希望開始重線重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TBS_11204.T_NON_PARTITION_TEMP partition P2 by 128 in tablespace TBS_P2
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

  • 星點的希望也破滅了，tbs_p2報錯,殺一儆百，所有表空間加大1倍空間

SQL> alter database datafile 11 resize 20m;

Database altered.



SQL> alter database datafile 12 resize 20m;

Database altered.

SQL> alter database datafile 13 resize 20m;

Database altered.

SQL> alter database datafile 14 resize 20m;

Database altered.

SQL> alter database datafile 15 resize 20m;

Database altered.

SQL> alter database datafile 17 resize 20m;

Database altered.


SQL> alter database datafile 9 resize 20m;

Database altered.

SQL> alter database datafile 10 resize 24m;

Database altered.

SQL> alter database datafile 16 resize 24m;

Database altered.

SQL> alter database datafile 18 resize 24m;

Database altered.

SQL> alter database datafile 19 resize 24m;

Database altered.

SQL> alter database datafile 20 resize 24m;

Database altered. 

查詢所有表空間的現在大小，全是線上重定義表各個分割槽資料量的2倍
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 mb,autoextensible from dba_data_files where lower(tablespace_name) like 'tbs_p%';

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P0               /home/ora10g/tbs_p0.dbf                                               9         20 NO
TBS_P1               /home/ora10g/tbs_p1.dbf                                              10         24 NO
TBS_P2               /home/ora10g/tbs_p2.dbf                                              11         20 NO
TBS_P3               /home/ora10g/tbs_p3.dbf                                              12         20 NO
TBS_P4               /home/ora10g/tbs_p4.dbf                                              13         20 NO
TBS_P5               /home/ora10g/tbs_p5.dbf                                              14         20 NO
TBS_P6               /home/ora10g/tbs_p6.dbf                                              15         20 NO
TBS_P7               /home/ora10g/tbs_p7.dbf                                              16         24 NO
TBS_P8               /home/ora10g/tbs_p8.dbf                                              17         20 NO
TBS_P9               /home/ora10g/tbs_p9.dbf                                              18         24 NO
TBS_P10              /home/ora10g/tbs_p10.dbf                                             19         24 NO

TABLESPACE_NAME      FILE_NAME                                                       FILE_ID         MB AUT
-------------------- ------------------------------------------------------------ ---------- ---------- ---
TBS_P11              /home/ora10g/tbs_p11.dbf                                             20         24 NO

12 rows selected.
  • 中斷線上重定義
SQL> exec dbms_redefinition.abort_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

PL/SQL procedure successfully completed.

破斧沉舟開始線上重定義
SQL> begin
dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_rowid);
  3  end;
  4 
  5  /

PL/SQL procedure successfully completed.
哈哈，終於拔雲見日了


  • 同步線上重定義表，可選項
SQL> begin
dbms_redefinition.sync_interim_table('tbs_11204','t_non_partition','t_non_partition_temp');
  3  end;
  4  /

PL/SQL procedure successfully completed.

  • 完成線上重定義
SQL> begin
dbms_redefinition.finish_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');
  3  end;
  4  /

PL/SQL procedure successfully completed.

  • 確認線上重定義錶轉化成功
SQL> select object_name,base_table_name,ddl_txt from dba_redefinition_errors;

no rows selected

驗證轉化後表各分割槽儲存到對應的表空間
SQL> select table_owner,table_name,partition_name,tablespace_name,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tab_partitions where lower(table_name)='t_non_partition' order by 3;

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME        NUM_ROWS TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ ------------------------------ -------------------- ---------- -------------------
TBS_11204                      T_NON_PARTITION                P0                             TBS_P0
TBS_11204                      T_NON_PARTITION                P1                             TBS_P1
TBS_11204                      T_NON_PARTITION                P10                            TBS_P10
TBS_11204                      T_NON_PARTITION                P11                            TBS_P11
TBS_11204                      T_NON_PARTITION                P2                             TBS_P2
TBS_11204                      T_NON_PARTITION                P3                             TBS_P3
TBS_11204                      T_NON_PARTITION                P4                             TBS_P4
TBS_11204                      T_NON_PARTITION                P5                             TBS_P5
TBS_11204                      T_NON_PARTITION                P6                             TBS_P6
TBS_11204                      T_NON_PARTITION                P7                             TBS_P7
TBS_11204                      T_NON_PARTITION                P8                             TBS_P8

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME        NUM_ROWS TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ ------------------------------ -------------------- ---------- -------------------
TBS_11204                      T_NON_PARTITION                P9                             TBS_P9

12 rows selected.

各分割槽大小為14M，而在之前未進行線上重定義前，列prov_code對應的資料大小為7m,可見增加了1倍左右
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where lower(segment_name)='t_non_partition' order by 3;

OWNER           SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME      BYTES/1024/1024
--------------- ------------------------------ ------------------------------ ------------------ -------------------- ---------------
TBS_11204       T_NON_PARTITION                P0                             TABLE PARTITION    TBS_P0                            14
TBS_11204       T_NON_PARTITION                P1                             TABLE PARTITION    TBS_P1                            14
TBS_11204       T_NON_PARTITION                P10                            TABLE PARTITION    TBS_P10                           14
TBS_11204       T_NON_PARTITION                P11                            TABLE PARTITION    TBS_P11                           14
TBS_11204       T_NON_PARTITION                P2                             TABLE PARTITION    TBS_P2                            14
TBS_11204       T_NON_PARTITION                P3                             TABLE PARTITION    TBS_P3                            14
TBS_11204       T_NON_PARTITION                P4                             TABLE PARTITION    TBS_P4                            14
TBS_11204       T_NON_PARTITION                P5                             TABLE PARTITION    TBS_P5                            14
TBS_11204       T_NON_PARTITION                P6                             TABLE PARTITION    TBS_P6                            14
TBS_11204       T_NON_PARTITION                P7                             TABLE PARTITION    TBS_P7                            14
TBS_11204       T_NON_PARTITION                P8                             TABLE PARTITION    TBS_P8                            14

OWNER           SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME      BYTES/1024/1024
--------------- ------------------------------ ------------------------------ ------------------ -------------------- ---------------
TBS_11204       T_NON_PARTITION                P9                             TABLE PARTITION    TBS_P9                            14

12 rows selected.

各分割槽累計大小為168M
SQL> select sum(bytes/1024/1024) mb from dba_segments where lower(segment_name)='t_non_partition';

        MB
----------
       168



小結：
1，線上重定義如果中途失敗，可以透過dbms_redefinition.abort_redef_table來中斷線上重定義過程
這裡分2種情況，各為線上重定義出現異常中止執行和手工中斷線上重定義操作

2，線上重定義，至少需要2倍於源表的可用空間，不然會報表空間不能擴充套件的錯誤

3，線上重定義之後，表的統計資訊沒有收集，所以要馬上手工採集表的統計資訊，以防SQL執行計劃走偏 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1250752/，如需轉載，請註明出處，否則將追究法律責任。

相關文章