oracle 12.2 重新运行在线重定义失败的进程

也就是在重定义表的过程中失败，如果通过查询视图DBA_REDEFINITION_STATUS的RESTARTABLE字段值为Y，那么根据ACTION处理这个问题后，可以直接继续执行这一步，也就是省掉了ABORT后重新start的步骤。
例如，将一个表改为分区表，在执行SYNC_INTERIM_TABLE之前，模拟业务插入了大量数据，执行SYNC_INTERIM_TABLE时由于新表空间不足就会报错，在表空间扩充之后，再次执行SYNC_INTERIM_TABLE即可。

创建测试表空间和表，并插入数据：
[code]SQL> create tablespace oradata1 datafile size 400m;

Tablespace created.

SQL> create tablespace oradata2 datafile size 1m;

Tablespace created.

SQL> create table obj_redef (OBJECT_ID number primary key,OBJECT_NAME VARCHAR2(128),OBJECT_TYPE VARCHAR2(23)) tablespace oradata1;

Table created.

SQL> insert into obj_redef select OBJECT_ID,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_id is not null;

76807 rows created.

SQL> commit;

Commit complete.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE SUM(BYTES)/1024/1024
---------- ------------------------------ --------------- ---------- --------------------
STEVEN OBJ_REDEF TABLE ORADATA1 5

SQL> create table int_obj_redef (OBJECT_ID number primary key,OBJECT_NAME VARCHAR2(128),OBJECT_TYPE VARCHAR2(23),CREATED date,STATUS VARCHAR2(7)) partition by hash (OBJECT_ID) partitions 10 tablespace oradata2;

Table created.[/code]
使用主键对OBJ_REDEF进行在线重定义，改成OBJECT_ID字段10个hash分区，添加created、status字段且默认值是sysdate和'UNKOWN'：
[code]SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE(
3 uname => 'steven',
4 tname =>'obj_redef',
5 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef',
6 col_mapping => 'OBJECT_ID OBJECT_ID, OBJECT_NAME OBJECT_NAME, OBJECT_TYPE OBJECT_TYPE, sysdate CREATED,''UNKOWN'' status',
7 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-01658: unable to create INITIAL extent for segment in tablespace ORADATA2
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 2[/code]
初始ORADATA2表空间仅1m的空间，在start时直接报错，此时的报错是无法restart的，必须ABORT后重新开始：
[code]SQL> SELECT BASE_OBJECT_NAME, OPERATION, STATUS, RESTARTABLE, ERR_TXT, ACTION
2 FROM DBA_REDEFINITION_STATUS
3 ORDER BY BASE_TABLE_NAME, BASE_OBJECT_NAME;

BASE_OBJECT OPERATION STATUS RESTARTABLE ERR_TXT ACTION
----------- ---------- ---------- ----------- --------------- ------------------
OBJ_REDEF START_REDE Failure N ORA-12008: erro Abort redefinition
F_TABLE r in materializ
ed view or zone
map refresh pat
h
ORA-01658: unab
le to create IN
ITIAL extent fo
r segment in ta
blespace ORADAT
A2

SQL> DROP MATERIALIZED VIEW LOG on OBJ_REDEF;

Materialized view log dropped.

SQL> BEGIN
2 DBMS_REDEFINITION.ABORT_REDEF_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> alter database datafile '/u02/app/oracle/oradata/ORASQL/42AA0C2D407C28E3E05312C0100A3D8E/datafile/o1_mf_oradata2_d49l0b87_.dbf' resize 100m;

Database altered.

SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef',
6 col_mapping => 'OBJECT_ID OBJECT_ID, OBJECT_NAME OBJECT_NAME, OBJECT_TYPE OBJECT_TYPE, sysdate CREATED,''UNKOWN'' status',
7 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
8 END;
9 /

PL/SQL procedure successfully completed.[/code]
如果需要，拷贝表的相关对象,也可以手工创建，跳过这一步
[code]SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => 'steven',
6 orig_table => 'obj_redef',
7 int_table => 'int_obj_redef',
8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
9 copy_triggers => TRUE,
10 copy_constraints => TRUE,
11 copy_privileges => TRUE,
12 ignore_errors => TRUE,
13 num_errors => num_errors);
14 END;
15 /

PL/SQL procedure successfully completed.

SQL> SET LONG 8000
SQL> SET PAGES 8000
SQL> COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
SQL> COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
SQL> COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
SQL>
SQL> SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM
2 DBA_REDEFINITION_ERRORS;

Object Name Base Table DDL That Caused Error
-------------------- ---------- ----------------------------------------
SYS_C0012210 OBJ_REDEF ALTER TABLE "STEVEN"."INT_OBJ_REDEF" ADD
CONSTRAINT "TMP$$_SYS_C00122100" PRIMAR
Y KEY ("OBJECT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXT
RANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GRO
UPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAUL
T CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ORADATA1" ENABLE NOVALIDA
TE[/code]
由于主键在表创建的时候已经创建，错误可以忽略。
此时在切换之前模拟业务向表中插入大量数据：
[code]SQL> begin
2 for i in 1 .. 50
3 loop
4 insert into steven.obj_redef select 86100*i+object_id,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_id is not null;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.[/code]
执行SYNC_INTERIM_TABLE同步临时表的数据，直接报错：
[code]SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef');
6 END;
7 /

BEGIN
*
ERROR at line 1:
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view or zonemap refresh path
ORA-01654: unable to extend index STEVEN.SYS_C0012214 by 128 in tablespace ORADATA2
ORA-06512: at "SYS.DBMS_REDEFINITION", line 219
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5392
ORA-06512: at line 2

SQL> select BASE_TABLE_NAME,INTERIM_OBJECT_NAME,OPERATION,STATUS,RESTARTABLE,ACTION from DBA_REDEFINITION_STATUS;
Base Table INTERIM_OBJECT_NAME OPERATION STATUS RESTARTABL ACTION
---------- -------------------- ------------------------------ ---------- ---------- --------------------
OBJ_REDEF INT_OBJ_REDEF SYNC_REDEF_TABLE Failure Y Fix 1654 error

TABLESPACENAME Totle_size(G) Free_space(G) Used_space(G) Used_percent%
------------------------------ ------------- ------------- ------------- -------------
UNDOTBS1 3.47 0 3.47 99.94
ORADATA2 .1 0 .1 99.06
SYSTEM .27 0 .27 99.04
USERS .27 .01 .26 95.07
SYSAUX .64 .04 .6 93.86
ORADATA1 .39 .09 .3 76.25[/code]
可以看到RESTARTABL为Y，只需要解决ORA-01654错误后可以继续直接执行：
[code]SQL> alter database datafile '/u02/app/oracle/oradata/ORASQL/42AA0C2D407C28E3E05312C0100A3D8E/datafile/o1_mf_oradata2_d49l0b87_.dbf' resize 500m;

Database altered.

SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef');
6 END;
7 /

PL/SQL procedure successfully completed.[/code]
完成重定义过程，清理表INT_OBJ_REDEF
[code]SQL> SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'steven',
4 orig_table => 'obj_redef',
5 int_table => 'int_obj_redef');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> /

TABLESPACENAME Totle_size(G) Free_space(G) Used_space(G) Used_percent%
------------------------------ ------------- ------------- ------------- -------------
UNDOTBS1 4.25 0 4.25 99.95
SYSTEM .27 0 .27 99.04
ORADATA2 .49 .02 .47 96.01
USERS .27 .01 .26 95.07
SYSAUX .64 .04 .6 93.86
ORADATA1 .39 .09 .3 76.25

SQL> desc obj_redef
Name Null? Type
------------------------------------ -------- -----------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
STATUS VARCHAR2(7)

SQL> select object_name,object_type,status from dba_objects where owner='STEVEN';

Object Name OBJECT_TYPE STATUS
-------------------- ----------------------- ----------
SYS_C0012210 INDEX VALID
SYS_C0012214 INDEX VALID
OBJ_REDEF TABLE VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
OBJ_REDEF TABLE PARTITION VALID
INT_OBJ_REDEF TABLE VALID

14 rows selected.

SQL> drop table INT_OBJ_REDEF purge;

Table dropped.

TABLESPACENAME Totle_size(G) Free_space(G) Used_space(G) Used_percent%
------------------------------ ------------- ------------- ------------- -------------
SYSTEM .27 0 .27 99.08
ORADATA2 .49 .02 .47 96.01
SYSAUX .64 .04 .6 94.48
UNDOTBS1 4.25 2.92 1.33 31.39
USERS .27 .27 0 .36
ORADATA1 .39 .39 0 .25[/code]
详细请参考官方文档：
https://docs.oracle.com/database/122/ADMIN/managing-tables.htm#ADMIN-GUID-58F4E092-A86D-4E00-96F6-56A596C91A53