oracle在线重定义，实质就是从源表复制了一份数据出来到中间的建立的分区表，然后表名交换，源表成为中间表删除。
重定义前：
select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where owner='SF' and segment_name='G_USER_ACTIVES'
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ -------------------- ---------- ---------- ---------- ----------
SF G_USER_ACTIVES 0 26 1049 8
SF G_USER_ACTIVES 1 26 641 8
SF G_USER_ACTIVES 2 26 2465 8
SF G_USER_ACTIVES 3 26 2489 8
SF G_USER_ACTIVES 4 26 2513 8
SF G_USER_ACTIVES 5 26 2545 8
SF G_USER_ACTIVES 6 26 2753 8
SF G_USER_ACTIVES 7 26 2761 8
SF G_USER_ACTIVES 8 26 2785 8
SF G_USER_ACTIVES 9 26 2817 8
SF G_USER_ACTIVES 10 26 2825 8
建议中间分区表：
CREATE TABLE sf.G_USER_ACTIVES_NEW
(
ID NUMBER(10) NOT NULL,
IMSI VARCHAR2(30 BYTE),
IMEI VARCHAR2(30 BYTE),
USER_ID VARCHAR2(20 BYTE),
MOBI_FACTORY VARCHAR2(20 BYTE),
MOBI_MODE VARCHAR2(20 BYTE),
CATALOG_ID NUMBER(10),
SORT_DIR_ID NUMBER(10),
SORT_DIR_NAME VARCHAR2(50 BYTE),
SORT_ID NUMBER(10),
SORT_NAME VARCHAR2(50 BYTE),
BOOK_ID VARCHAR2(20 BYTE),
ACTIVE_TYPE NUMBER,
APPROACH VARCHAR2(30 BYTE),
APP_VER VARCHAR2(50 BYTE),
LOCAL_IP VARCHAR2(50 BYTE),
DEMO VARCHAR2(100 BYTE),
ACTIVE_TIME DATE DEFAULT SYSDATE
)
partition by range(active_time)
( PARTITION DOWN_201209 VALUES LESS THAN (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
COMPRESS ,
PARTITION DOWN_201210 VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
COMPRESS )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('sf', 'G_USER_ACTIVES', 'G_USER_ACTIVES_NEW',null);
注意start开始copy数据，如果表较大，那么需要花些时间。
SQL> select count(*) from sf.g_user_actives;
COUNT(*)
----------
992786
SQL> select count(*) from sf.g_user_actives_NEW;
COUNT(*)
----------
992786
由于表是静态的，所以copy和sync都忽略了。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('sf', 'G_USER_ACTIVES', 'G_USER_ACTIVES_NEW');
SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where owner='SF' and segment_name='G_USER_ACTIVES';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ -------------------- ---------- ---------- ---------- ----------
SF G_USER_ACTIVES 0 26 51529 8
SF G_USER_ACTIVES 0 26 51521 8
SF G_USER_ACTIVES 1 26 51545 8
SF G_USER_ACTIVES 2 26 51553 8
SF G_USER_ACTIVES 3 26 51561 8
SF G_USER_ACTIVES 4 26 51569 8
SF G_USER_ACTIVES 5 26 51577 8
SF G_USER_ACTIVES 6 26 51585 8
SF G_USER_ACTIVES 7 26 62473 8
SF G_USER_ACTIVES 8 26 62481 8
SF G_USER_ACTIVES 9 26 62489 8
SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where owner='SF' and segment_name='G_USER_ACTIVES_NEW';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ -------------------- ---------- ---------- ---------- ----------
SF G_USER_ACTIVES_NEW 0 26 1049 8
SF G_USER_ACTIVES_NEW 1 26 641 8
SF G_USER_ACTIVES_NEW 2 26 2465 8
SF G_USER_ACTIVES_NEW 3 26 2489 8
SF G_USER_ACTIVES_NEW 4 26 2513 8
SF G_USER_ACTIVES_NEW 5 26 2545 8
SF G_USER_ACTIVES_NEW 6 26 2753 8
SF G_USER_ACTIVES_NEW 7 26 2761 8
SF G_USER_ACTIVES_NEW 8 26 2785 8
SF G_USER_ACTIVES_NEW 9 26 2817 8
SF G_USER_ACTIVES_NEW 10 26 2825 8
这里显而易见，通过比较file_id和block_id，就是表名换了一下。
