个人简介：
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
- 中断在线重定义
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
- 重新开始在线重定义
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
悲催，又报错了
- 中断在线重定义
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次开始在线重定义
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表空间亦报错了
- 中断在线重定义
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
- 不知次数开始在线重义
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.
- 烦烦开始在线重定义
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报错
- 中断在线重定义
PL/SQL procedure successfully completed.
- 扩展表空间tbs_p1
SQL> alter database datafile 10 resize 10m;
Database altered.
- 与妖魔一战，开始在线重定义
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报错
- 一劳永逸，把上述未报过错的表空间全部加大
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.
- 一线希望开始重线重定义
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.
- 完成在线重定义
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1250752/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
