online redefinition example
环境:Red Hat Linux4.5+ORACLE10GR2
创建测试用表
CREATE TABLE TEST
(
  ORDER_ID  NUMBER,
  DETAIL    CHAR(255),
  CONSTRAINT PK_TEST PRIMARY KEY (ORDER_ID) USING INDEX TABLESPACE NDX_OAK
)TABLESPACE TBS_OAK;

begin
for i in 1..5000000
loop
insert into test values(i,'This is a test');
end loop;
commit;
end;
删除数据
delete from test where order_id<4000000;
查看segment大小
SQL> select segment_name ,tablespace_name ,bytes/1024/1024 from user_segments;
SEGMENT_NAME                        TABLESPACE BYTES/1024/1024
----------------------------------- ---------- ---------------
TEST                                TBS_OAK               1452
PK_TEST                             NDX_OAK                 80

确认是否可以redefinition
SQL> EXECUTE DBMS_REDEFINITION.CAN_REDEF_TABLE('OAK','TEST');

PL/SQL procedure successfully completed.

创建interim表(这里不创建任何dependent)
CREATE TABLE TEST_STAGE
(
  ORDER_ID  NUMBER,
  DETAIL    CHAR(255),
)TABLESPACE TBS_OAK;

开始redefinition
SQL> EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE('OAK','TEST','TEST_STAGE');

PL/SQL procedure successfully completed.

Elapsed: 00:03:09.54
在开始redefinition的同时,向表中插入数据,确认在redefinition过程中表的可用必
begin
for i in 5000001..5000500
loop
insert into test values(i,'This is a test');
end loop;
commit;
end;
创建dependents on interim(只创建表结构)
SQL> DECLARE
  2  retval NUMBER(5);
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('OAK','TEST','TEST_STAGE',1,TRUE, TRUE, TRUE, TRUE, retval, TRUE);
  5  END;
  6  /

PL/SQL procedure successfully completed.
查询DBA_REDEFINITION_ERRORS视图确认是否有没有创建的dependents
SQL> select object_name ,base_table_name ,ddl_txt from dba_redefinition_errors;

no rows selected

更新在redefinition过程中的更新到interim表
SQL> EXECUTE DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OAK','TEST','TEST_STAGE');

PL/SQL procedure successfully completed.

在完成redefinition前先检查空间使用情况
SQL> select segment_name ,tablespace_name ,bytes/1024/1024 from user_segments;
SEGMENT_NAME                        TABLESPACE BYTES/1024/1024
----------------------------------- ---------- ---------------
TEST_STAGE                          TBS_OAK                304
MLOG$_TEST                          TBS_OAK                  2
TEST                                TBS_OAK               1452
TMP$$_PK_TEST0                      NDX_OAK                 18
PK_TEST                             NDX_OAK                 80
SQL> EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE('OAK','TEST','TEST_STAGE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.65

完成redefinition后检查空间使用情况
SQL> select segment_name ,tablespace_name ,bytes/1024/1024 from user_segments;
SEGMENT_NAME                        TABLESPACE BYTES/1024/1024
----------------------------------- ---------- ---------------
TEST                                TBS_OAK                304
TEST_STAGE                          TBS_OAK               1452
PK_TEST                             NDX_OAK                 18
TMP$$_PK_TEST0                      NDX_OAK                 80
删除interim table
再次查看空间使用情况
SQL>  select segment_name ,tablespace_name ,bytes/1024/1024 from user_segments;

SEGMENT_NAME                        TABLESPACE BYTES/1024/1024
----------------------------------- ---------- ---------------
TEST                                TBS_OAK                304
BIN$XLsyM3DguVXgQAB/AQAEmw==$0      TBS_OAK               1452
PK_TEST                             NDX_OAK                 18
BIN$XLsyM3DfuVXgQAB/AQAEmw==$0      NDX_OAK            80