为了更加清晰的了解每一步的变化, 每一步都查看中间表和原始表的extents信息, 来看看.
SQL> create table test.tbl_redefine_test(id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate) tablespace kefu;
SQL> select index_name from user_indexes where table_name='TBL_REDEFINE_TEST';
INDEX_NAME
------------------------------
SYS_C0028991
SQL> alter index SYS_C0028991 nologging;
Index altered.
SQL> insert into tbl_redefine_test nologging (id) select rownum from dual connect by level < 2000000;
1999999 rows created.
SQL> set timing on
SQL> set pagesize 50000 linesize 190
SQL> column owner format a16
SQL> column SEGMENT_NAME format a18
SQL> column partition_name format a16
SQL> conn / as sysdba
SQL> select segment_name,partition_name,extent_id,file_id,block_id,block_id+blocks from dba_extents where owner='TEST' and segment_name IN ('TBL_REDEFINE_TEST','PART_TEST') ORDER BY OWNER,SEGMENT_NAME,PARTITION_NAME,EXTENT_ID,BLOCK_ID;
SEGMENT_NAME PARTITION_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCK_ID+BLOCKS
------------------ ---------------- ---------- ---------- ---------- ---------------
TBL_REDEFINE_TEST 0 5 1929 1937
TBL_REDEFINE_TEST 1 5 1945 1953
TBL_REDEFINE_TEST 2 5 1953 1961
TBL_REDEFINE_TEST 3 5 1969 1977
TBL_REDEFINE_TEST 4 5 1977 1985
TBL_REDEFINE_TEST 5 5 1985 1993
TBL_REDEFINE_TEST 6 5 2017 2025
TBL_REDEFINE_TEST 7 5 2025 2033
TBL_REDEFINE_TEST 8 5 2033 2041
TBL_REDEFINE_TEST 9 5 2049 2057
TBL_REDEFINE_TEST 10 5 2313 2321
TBL_REDEFINE_TEST 11 5 2321 2329
TBL_REDEFINE_TEST 12 5 24473 24481
TBL_REDEFINE_TEST 13 5 24489 24497
TBL_REDEFINE_TEST 14 5 24497 24505
TBL_REDEFINE_TEST 15 5 24505 24513
TBL_REDEFINE_TEST 16 5 46089 46217
TBL_REDEFINE_TEST 17 5 46217 46345
TBL_REDEFINE_TEST 18 5 46345 46473
TBL_REDEFINE_TEST 19 5 47241 47369
TBL_REDEFINE_TEST 20 5 47369 47497
TBL_REDEFINE_TEST 21 5 47497 47625
TBL_REDEFINE_TEST 22 5 47753 47881
TBL_REDEFINE_TEST 23 5 47881 48009
TBL_REDEFINE_TEST 24 5 48009 48137
TBL_REDEFINE_TEST 25 5 48265 48393
TBL_REDEFINE_TEST 26 5 48393 48521
TBL_REDEFINE_TEST 27 5 48521 48649
TBL_REDEFINE_TEST 28 5 48777 48905
TBL_REDEFINE_TEST 29 5 48905 49033
TBL_REDEFINE_TEST 30 5 49033 49161
TBL_REDEFINE_TEST 31 5 49289 49417
TBL_REDEFINE_TEST 32 5 49417 49545
TBL_REDEFINE_TEST 33 5 49545 49673
TBL_REDEFINE_TEST 34 5 49801 49929
TBL_REDEFINE_TEST 35 5 49929 50057
TBL_REDEFINE_TEST 36 5 50057 50185
TBL_REDEFINE_TEST 37 5 50185 50313
TBL_REDEFINE_TEST 38 5 50441 50569
TBL_REDEFINE_TEST 39 5 50569 50697
TBL_REDEFINE_TEST 40 5 50697 50825
TBL_REDEFINE_TEST 41 5 50953 51081
TBL_REDEFINE_TEST 42 5 51081 51209
TBL_REDEFINE_TEST 43 5 51209 51337
TBL_REDEFINE_TEST 44 5 51465 51593
TBL_REDEFINE_TEST 45 5 51593 51721
TBL_REDEFINE_TEST 46 5 51721 51849
TBL_REDEFINE_TEST 47 5 51977 52105
TBL_REDEFINE_TEST 48 5 52105 52233
TBL_REDEFINE_TEST 49 5 52233 52361
TBL_REDEFINE_TEST 50 5 52489 52617
TBL_REDEFINE_TEST 51 5 52617 52745
TBL_REDEFINE_TEST 52 5 52745 52873
TBL_REDEFINE_TEST 53 5 53001 53129
TBL_REDEFINE_TEST 54 5 53129 53257
TBL_REDEFINE_TEST 55 5 53257 53385
TBL_REDEFINE_TEST 56 5 53385 53513
TBL_REDEFINE_TEST 57 5 53769 53897
TBL_REDEFINE_TEST 58 5 53897 54025
TBL_REDEFINE_TEST 59 5 54025 54153
TBL_REDEFINE_TEST 60 5 54281 54409
TBL_REDEFINE_TEST 61 5 54409 54537
TBL_REDEFINE_TEST 62 5 54537 54665
TBL_REDEFINE_TEST 63 5 54793 54921
TBL_REDEFINE_TEST 64 5 54921 55049
TBL_REDEFINE_TEST 65 5 55049 55177
TBL_REDEFINE_TEST 66 5 55305 55433
TBL_REDEFINE_TEST 67 5 55433 55561
TBL_REDEFINE_TEST 68 5 55561 55689
TBL_REDEFINE_TEST 69 5 55817 55945
TBL_REDEFINE_TEST 70 5 55945 56073
TBL_REDEFINE_TEST 71 5 56073 56201
TBL_REDEFINE_TEST 72 5 56329 56457
TBL_REDEFINE_TEST 73 5 56457 56585
TBL_REDEFINE_TEST 74 5 56585 56713
TBL_REDEFINE_TEST 75 5 56841 56969
TBL_REDEFINE_TEST 76 5 56969 57097
TBL_REDEFINE_TEST 77 5 57097 57225
TBL_REDEFINE_TEST 78 5 24713 24841
TBL_REDEFINE_TEST 79 5 37129 37769
TBL_REDEFINE_TEST 80 5 40329 40969
TBL_REDEFINE_TEST 81 5 38793 39305
TBL_REDEFINE_TEST 82 5 41993 42505
TBL_REDEFINE_TEST 83 5 43529 43913
TBL_REDEFINE_TEST 84 5 28041 28169
TBL_REDEFINE_TEST 85 5 28425 28553
TBL_REDEFINE_TEST 86 5 29321 29449
TBL_REDEFINE_TEST 87 5 29705 29833
TBL_REDEFINE_TEST 88 5 29961 30089
TBL_REDEFINE_TEST 89 5 30601 30729
TBL_REDEFINE_TEST 90 5 30985 31113
TBL_REDEFINE_TEST 91 5 31369 31497
TBL_REDEFINE_TEST 92 5 32265 32393
TBL_REDEFINE_TEST 93 5 32649 32777
TBL_REDEFINE_TEST 94 5 32905 33033
95 rows selected.
Elapsed: 00:00:00.03
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TBL_REDEFINE_TEST',
3 DBMS_REDEFINITION.CONS_USE_PK);
4 END;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> CREATE TABLE test.part_test
2 (id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate)
3 PARTITION BY RANGE(id)
4 (PARTITION p1 VALUES LESS THAN (300000) TABLESPACE kefu,
5 PARTITION p2 VALUES LESS THAN (600000) TABLESPACE kefu,
6 PARTITION p3 VALUES LESS THAN (900000) TABLESPACE kefu,
7 PARTITION p4 VALUES LESS THAN (1200000) TABLESPACE kefu,
8 PARTITION p5 VALUES LESS THAN (1500000) TABLESPACE kefu,
9 PARTITION p6 VALUES LESS THAN (1800000) TABLESPACE kefu,
10 PARTITION p7 VALUES LESS THAN (2100000) TABLESPACE kefu
11 );
Table created.
Elapsed: 00:00:00.03
SQL> select segment_name,partition_name,extent_id,file_id,block_id,block_id+blocks from dba_extents where owner='TEST' and segment_name IN ('TBL_REDEFINE_TEST','PART_TEST') ORDER BY OWNER,SEGMENT_NAME,PARTITION_NAME,EXTENT_ID,BLOCK_ID;
SEGMENT_NAME PARTITION_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCK_ID+BLOCKS
------------------ ---------------- ---------- ---------- ---------- ---------------
PART_TEST P1 0 5 15777 15785
PART_TEST P2 0 5 15801 15809
PART_TEST P3 0 5 15833 15841
PART_TEST P4 0 5 15865 15873
PART_TEST P5 0 5 24097 24105
PART_TEST P6 0 5 24129 24137
PART_TEST P7 0 5 24137 24145
TBL_REDEFINE_TEST 0 5 1929 1937
TBL_REDEFINE_TEST 1 5 1945 1953
TBL_REDEFINE_TEST 2 5 1953 1961
TBL_REDEFINE_TEST 3 5 1969 1977
TBL_REDEFINE_TEST 4 5 1977 1985
TBL_REDEFINE_TEST 5 5 1985 1993
TBL_REDEFINE_TEST 6 5 2017 2025
TBL_REDEFINE_TEST 7 5 2025 2033
TBL_REDEFINE_TEST 8 5 2033 2041
TBL_REDEFINE_TEST 9 5 2049 2057
TBL_REDEFINE_TEST 10 5 2313 2321
TBL_REDEFINE_TEST 11 5 2321 2329
TBL_REDEFINE_TEST 12 5 24473 24481
TBL_REDEFINE_TEST 13 5 24489 24497
TBL_REDEFINE_TEST 14 5 24497 24505
TBL_REDEFINE_TEST 15 5 24505 24513
TBL_REDEFINE_TEST 16 5 46089 46217
TBL_REDEFINE_TEST 17 5 46217 46345
TBL_REDEFINE_TEST 18 5 46345 46473
TBL_REDEFINE_TEST 19 5 47241 47369
TBL_REDEFINE_TEST 20 5 47369 47497
TBL_REDEFINE_TEST 21 5 47497 47625
TBL_REDEFINE_TEST 22 5 47753 47881
TBL_REDEFINE_TEST 23 5 47881 48009
TBL_REDEFINE_TEST 24 5 48009 48137
TBL_REDEFINE_TEST 25 5 48265 48393
TBL_REDEFINE_TEST 26 5 48393 48521
TBL_REDEFINE_TEST 27 5 48521 48649
TBL_REDEFINE_TEST 28 5 48777 48905
TBL_REDEFINE_TEST 29 5 48905 49033
TBL_REDEFINE_TEST 30 5 49033 49161
TBL_REDEFINE_TEST 31 5 49289 49417
TBL_REDEFINE_TEST 32 5 49417 49545
TBL_REDEFINE_TEST 33 5 49545 49673
TBL_REDEFINE_TEST 34 5 49801 49929
TBL_REDEFINE_TEST 35 5 49929 50057
TBL_REDEFINE_TEST 36 5 50057 50185
TBL_REDEFINE_TEST 37 5 50185 50313
TBL_REDEFINE_TEST 38 5 50441 50569
TBL_REDEFINE_TEST 39 5 50569 50697
TBL_REDEFINE_TEST 40 5 50697 50825
TBL_REDEFINE_TEST 41 5 50953 51081
TBL_REDEFINE_TEST 42 5 51081 51209
TBL_REDEFINE_TEST 43 5 51209 51337
TBL_REDEFINE_TEST 44 5 51465 51593
TBL_REDEFINE_TEST 45 5 51593 51721
TBL_REDEFINE_TEST 46 5 51721 51849
TBL_REDEFINE_TEST 47 5 51977 52105
TBL_REDEFINE_TEST 48 5 52105 52233
TBL_REDEFINE_TEST 49 5 52233 52361
TBL_REDEFINE_TEST 50 5 52489 52617
TBL_REDEFINE_TEST 51 5 52617 52745
TBL_REDEFINE_TEST 52 5 52745 52873
TBL_REDEFINE_TEST 53 5 53001 53129
TBL_REDEFINE_TEST 54 5 53129 53257
TBL_REDEFINE_TEST 55 5 53257 53385
TBL_REDEFINE_TEST 56 5 53385 53513
TBL_REDEFINE_TEST 57 5 53769 53897
TBL_REDEFINE_TEST 58 5 53897 54025
TBL_REDEFINE_TEST 59 5 54025 54153
TBL_REDEFINE_TEST 60 5 54281 54409
TBL_REDEFINE_TEST 61 5 54409 54537
TBL_REDEFINE_TEST 62 5 54537 54665
TBL_REDEFINE_TEST 63 5 54793 54921
TBL_REDEFINE_TEST 64 5 54921 55049
TBL_REDEFINE_TEST 65 5 55049 55177
TBL_REDEFINE_TEST 66 5 55305 55433
TBL_REDEFINE_TEST 67 5 55433 55561
TBL_REDEFINE_TEST 68 5 55561 55689
TBL_REDEFINE_TEST 69 5 55817 55945
TBL_REDEFINE_TEST 70 5 55945 56073
TBL_REDEFINE_TEST 71 5 56073 56201
TBL_REDEFINE_TEST 72 5 56329 56457
TBL_REDEFINE_TEST 73 5 56457 56585
TBL_REDEFINE_TEST 74 5 56585 56713
TBL_REDEFINE_TEST 75 5 56841 56969
TBL_REDEFINE_TEST 76 5 56969 57097
TBL_REDEFINE_TEST 77 5 57097 57225
TBL_REDEFINE_TEST 78 5 24713 24841
TBL_REDEFINE_TEST 79 5 37129 37769
TBL_REDEFINE_TEST 80 5 40329 40969
TBL_REDEFINE_TEST 81 5 38793 39305
TBL_REDEFINE_TEST 82 5 41993 42505
TBL_REDEFINE_TEST 83 5 43529 43913
TBL_REDEFINE_TEST 84 5 28041 28169
TBL_REDEFINE_TEST 85 5 28425 28553
TBL_REDEFINE_TEST 86 5 29321 29449
TBL_REDEFINE_TEST 87 5 29705 29833
TBL_REDEFINE_TEST 88 5 29961 30089
TBL_REDEFINE_TEST 89 5 30601 30729
TBL_REDEFINE_TEST 90 5 30985 31113
TBL_REDEFINE_TEST 91 5 31369 31497
TBL_REDEFINE_TEST 92 5 32265 32393
TBL_REDEFINE_TEST 93 5 32649 32777
TBL_REDEFINE_TEST 94 5 32905 33033
102 rows selected.
Elapsed: 00:00:00.03