Oracle使用在线重定义将普通表转为分区表
前言
使用这功能也很久了，总想着总结一下但是一拖就拖两三年。
然后每次要在线重定义的时候就找以前案例的脚本来改......
目前在线重定义这个功能用到现在自己有用到一下几个目的：
普通表转分区表，表迁移表空间，表回收碎片。
另外我看网上说还可以增加/删除列，这个我就没试过了。
实验
本次实验是基于主键的方式来进行的，将普通表转为分区表。
创建实验表。
create table zkm.test ( id int, col1 varchar2(255), create_time date, constraint pk_id primary key (id) ); 19:11:09 SYS@zkm(31)> create table zkm.test 19:11:09 2 ( 19:11:09 3 id int, 19:11:09 4 col1 varchar2(255), 19:11:09 5 create_time date, 19:11:09 6 constraint pk_id primary key (id) 19:11:09 7 ); Table created. Elapsed: 00:00:00.01
插入数据。
insert into zkm.test values (1,'a',to_date('2000-01-01','yyyy-mm-dd')); insert into zkm.test values (2,'a',to_date('2001-01-01','yyyy-mm-dd')); insert into zkm.test values (3,'a',to_date('2002-01-01','yyyy-mm-dd')); insert into zkm.test values (4,'a',to_date('2003-01-01','yyyy-mm-dd')); insert into zkm.test values (5,'a',to_date('2004-01-01','yyyy-mm-dd')); insert into zkm.test values (6,'a',to_date('2005-01-01','yyyy-mm-dd')); insert into zkm.test values (7,'a',to_date('2006-01-01','yyyy-mm-dd')); insert into zkm.test values (8,'a',to_date('2007-01-01','yyyy-mm-dd')); insert into zkm.test values (9,'a',to_date('2008-01-01','yyyy-mm-dd')); insert into zkm.test values (10,'a',to_date('2009-01-01','yyyy-mm-dd')); insert into zkm.test values (11,'a',to_date('2010-01-01','yyyy-mm-dd')); insert into zkm.test values (12,'a',to_date('2011-01-01','yyyy-mm-dd')); insert into zkm.test values (13,'a',to_date('2012-01-01','yyyy-mm-dd')); insert into zkm.test values (14,'a',to_date('2013-01-01','yyyy-mm-dd')); insert into zkm.test values (15,'a',to_date('2014-01-01','yyyy-mm-dd')); insert into zkm.test values (16,'a',to_date('2015-01-01','yyyy-mm-dd')); insert into zkm.test values (17,'a',to_date('2016-01-01','yyyy-mm-dd')); insert into zkm.test values (18,'a',to_date('2017-01-01','yyyy-mm-dd')); insert into zkm.test values (19,'a',to_date('2018-01-01','yyyy-mm-dd')); insert into zkm.test values (20,'a',to_date('2019-01-01','yyyy-mm-dd')); insert into zkm.test values (21,'a',to_date('2020-01-01','yyyy-mm-dd')); commit; 19:11:09 SYS@zkm(31)> insert into zkm.test values (1,'a',to_date('2000-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (2,'a',to_date('2001-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.01 19:11:09 SYS@zkm(31)> insert into zkm.test values (3,'a',to_date('2002-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (4,'a',to_date('2003-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (5,'a',to_date('2004-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (6,'a',to_date('2005-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (7,'a',to_date('2006-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (8,'a',to_date('2007-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (9,'a',to_date('2008-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (10,'a',to_date('2009-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (11,'a',to_date('2010-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (12,'a',to_date('2011-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (13,'a',to_date('2012-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (14,'a',to_date('2013-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (15,'a',to_date('2014-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (16,'a',to_date('2015-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (17,'a',to_date('2016-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (18,'a',to_date('2017-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (19,'a',to_date('2018-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (20,'a',to_date('2019-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:09 SYS@zkm(31)> insert into zkm.test values (21,'a',to_date('2020-01-01','yyyy-mm-dd')); 1 row created. Elapsed: 00:00:00.00 19:11:10 SYS@zkm(31)> commit; Commit complete. Elapsed: 00:00:00.00
授权，创建索引，收集统计信息。
grant select on zkm.test to scott; create index zkm.idx_col1 on zkm.test(col1) online; exec dbms_stats.gather_table_stats(ownname => 'ZKM',tabname => 'TEST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE REPEAT',degree => 2,no_invalidate => false); 19:15:27 SYS@zkm(31)> grant select on zkm.test to scott; Grant succeeded. Elapsed: 00:00:00.01 19:17:21 SYS@zkm(31)> create index zkm.idx_col1 on zkm.test(col1) online; Index created. Elapsed: 00:00:00.26 19:20:56 SYS@zkm(31)> exec dbms_stats.gather_table_stats(ownname => 'ZKM',tabname => 'TEST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE REPEAT',degree => 2,no_invalidate => false); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
开始转换
目标是转换成以分区字段为create_time的范围分区表，并使用间隔分区（interval partitioning）特性，这样可以不需要自己手工创建大量的分区。
开始前先查询表test的占用的段大小，并且查询中间表（本例为test_tmp，见下边）所在表空间是否充足。
并且注意归档空间是否充足。
select sum(bytes)/1024/1024 mb from dba_segments where owner='ZKM' and segment_name='TEST'; set linesize 500 pagesize 9999 long 9999 SELECT a.tablespace_name, ROUND (a.bytes_alloc / 1024 / 1024, 2) megs_alloc, ROUND (NVL (b.bytes_free, 0) / 1024 / 1024, 2) megs_free, ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024, 2 ) megs_used, ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_free, 100 - ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_used, ROUND (maxbytes / 1048576, 2) MAX FROM (SELECT f.tablespace_name, SUM (f.BYTES) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes FROM dba_data_files f GROUP BY tablespace_name ) a, (SELECT f.tablespace_name, SUM (f.BYTES) bytes_free FROM dba_free_space f GROUP BY tablespace_name ) b WHERE a.tablespace_name = b.tablespace_name(+) UNION ALL SELECT h.tablespace_name, ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc, ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0)) / 1048576, 2 ) megs_free, ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576, 2) megs_used, ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_free, 100 - ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_used, ROUND (SUM (f.maxbytes) / 1048576, 2) MAX FROM SYS.v_$temp_space_header h, SYS.v_$temp_extent_pool p, dba_temp_files f WHERE p.file_id(+) = h.file_id AND p.tablespace_name(+) = h.tablespace_name AND f.file_id = h.file_id AND f.tablespace_name = h.tablespace_name GROUP BY h.tablespace_name ORDER BY 1 ;
需要在会话级开启并行以加快速度，参考：Managing Tables
1.查询create_time的最小值。
select * from (select to_char(create_time,'yyyy-mm') from zkm.test order by 1) where rownum=1; 19:13:23 SYS@zkm(31)> select * from (select to_char(create_time,'yyyy-mm') from zkm.test order by 1) where rownum=1; TO_CHAR ------- 2000-01 Elapsed: 00:00:00.00
2.检查是否有字段存在默认值
select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual; or select column_name, data_type, data_default from dba_tab_columns where owner='ZKM' and table_name = 'TEST' and default_length > 0;
注：如果有字段是存在默认值的，那么下边第3步骤的“创建中间表”不建议使用CTAS方式创建表，因为需要对表字段修改为某个默认值。
建议使用dbms_metadata.get_ddl获取的sql语句做更改。
3.创建中间表
值得注意的是，CTAS会同步字段的NULL或NOT NULL属性，会导致copy_table_dependents报错。
create table zkm.test_tmp PARTITION BY RANGE (create_time) INTERVAL (numtoyminterval(1, 'month')) ( partition p200001 values less than(to_date('2000-02', 'yyyy-mm'))) as select * from zkm.test where 1=2; 19:13:48 SYS@zkm(31)> create table zkm.test_tmp 19:15:27 2 PARTITION BY RANGE (create_time) INTERVAL (numtoyminterval(1, 'month')) 19:15:27 3 ( partition p200001 values less than(to_date('2000-02', 'yyyy-mm'))) 19:15:27 4 as select * from zkm.test 19:15:27 5 where 1=2; Table created. Elapsed: 00:00:00.13
注：
由于间隔分区的分区字段不允许为空，因此会无法使用间隔分区特性导致需要手工创建大量分区。
若是不适用间隔分区，则手工创建分区。这里添加示例代码。
带子分区的示例：
create table zkm.test_tmp PARTITION BY RANGE (create_time) subpartition by list(sex) subpartition template( subpartition subp0 values ('0'), subpartition subp1 values ('1'), subpartition subother values (default)) ( partition p201801 values less than(to_date('2018-02', 'yyyy-mm')), partition p201802 values less than(to_date('2018-03', 'yyyy-mm')), partition p201803 values less than(to_date('2018-04', 'yyyy-mm')), partition p201804 values less than(to_date('2018-05', 'yyyy-mm')), partition p201805 values less than(to_date('2018-06', 'yyyy-mm')), partition p201806 values less than(to_date('2018-07', 'yyyy-mm')), partition p201807 values less than(to_date('2018-08', 'yyyy-mm')), partition p201808 values less than(to_date('2018-09', 'yyyy-mm')), partition p201809 values less than(to_date('2018-10', 'yyyy-mm')), partition p201810 values less than(to_date('2018-11', 'yyyy-mm')), partition p201811 values less than(to_date('2018-12', 'yyyy-mm')), partition p201812 values less than(to_date('2019-01', 'yyyy-mm')), partition p201901 values less than(to_date('2019-02', 'yyyy-mm')), partition p201902 values less than(to_date('2019-03', 'yyyy-mm')), partition p201903 values less than(to_date('2019-04', 'yyyy-mm')), partition p201904 values less than(to_date('2019-05', 'yyyy-mm')), partition p201905 values less than(to_date('2019-06', 'yyyy-mm')), partition p201906 values less than(to_date('2019-07', 'yyyy-mm')), partition p201907 values less than(to_date('2019-08', 'yyyy-mm')), partition p201908 values less than(to_date('2019-09', 'yyyy-mm')), partition p201909 values less than(to_date('2019-10', 'yyyy-mm')), partition p201910 values less than(to_date('2019-11', 'yyyy-mm')), partition p201911 values less than(to_date('2019-12', 'yyyy-mm')), partition p201912 values less than(to_date('2020-01', 'yyyy-mm')), partition p202001 values less than(to_date('2020-02', 'yyyy-mm')), partition p202002 values less than(to_date('2020-03', 'yyyy-mm')), partition p202003 values less than(to_date('2020-04', 'yyyy-mm')), partition p202004 values less than(to_date('2020-05', 'yyyy-mm')), partition p202005 values less than(to_date('2020-06', 'yyyy-mm')), partition p202006 values less than(to_date('2020-07', 'yyyy-mm')), partition p202007 values less than(to_date('2020-08', 'yyyy-mm')), partition p202008 values less than(to_date('2020-09', 'yyyy-mm')), partition p202009 values less than(to_date('2020-10', 'yyyy-mm')), partition p202010 values less than(to_date('2020-11', 'yyyy-mm')), partition p202011 values less than(to_date('2020-12', 'yyyy-mm')), partition p202012 values less than(to_date('2021-01', 'yyyy-mm')), partition p202101 values less than(to_date('2021-02', 'yyyy-mm')), partition p202102 values less than(to_date('2021-03', 'yyyy-mm')), partition p202103 values less than(to_date('2021-04', 'yyyy-mm')), partition p202104 values less than(to_date('2021-05', 'yyyy-mm')), partition p202105 values less than(to_date('2021-06', 'yyyy-mm')), partition p202106 values less than(to_date('2021-07', 'yyyy-mm')), partition p202107 values less than(to_date('2021-08', 'yyyy-mm')), partition p202108 values less than(to_date('2021-09', 'yyyy-mm')), partition p202109 values less than(to_date('2021-10', 'yyyy-mm')), partition p202110 values less than(to_date('2021-11', 'yyyy-mm')), partition p202111 values less than(to_date('2021-12', 'yyyy-mm')), partition p202112 values less than(to_date('2022-01', 'yyyy-mm')), partition p202201 values less than(to_date('2022-02', 'yyyy-mm')), partition p202202 values less than(to_date('2022-03', 'yyyy-mm')), partition p202203 values less than(to_date('2022-04', 'yyyy-mm')), partition p202204 values less than(to_date('2022-05', 'yyyy-mm')), partition p202205 values less than(to_date('2022-06', 'yyyy-mm')), partition p202206 values less than(to_date('2022-07', 'yyyy-mm')), partition p202207 values less than(to_date('2022-08', 'yyyy-mm')), partition p202208 values less than(to_date('2022-09', 'yyyy-mm')), partition p202209 values less than(to_date('2022-10', 'yyyy-mm')), partition p202210 values less than(to_date('2022-11', 'yyyy-mm')), partition p202211 values less than(to_date('2022-12', 'yyyy-mm')), partition p202212 values less than(to_date('2023-01', 'yyyy-mm')), partition p202301 values less than(to_date('2023-02', 'yyyy-mm')), partition p202302 values less than(to_date('2023-03', 'yyyy-mm')), partition p202303 values less than(to_date('2023-04', 'yyyy-mm')), partition p202304 values less than(to_date('2023-05', 'yyyy-mm')), partition p202305 values less than(to_date('2023-06', 'yyyy-mm')), partition p202306 values less than(to_date('2023-07', 'yyyy-mm')), partition p202307 values less than(to_date('2023-08', 'yyyy-mm')), partition p202308 values less than(to_date('2023-09', 'yyyy-mm')), partition p202309 values less than(to_date('2023-10', 'yyyy-mm')), partition p202310 values less than(to_date('2023-11', 'yyyy-mm')), partition p202311 values less than(to_date('2023-12', 'yyyy-mm')), partition p202312 values less than(to_date('2024-01', 'yyyy-mm')), partition p202401 values less than(to_date('2024-02', 'yyyy-mm')), partition p202402 values less than(to_date('2024-03', 'yyyy-mm')), partition p202403 values less than(to_date('2024-04', 'yyyy-mm')), partition p202404 values less than(to_date('2024-05', 'yyyy-mm')), partition p202405 values less than(to_date('2024-06', 'yyyy-mm')), partition p202406 values less than(to_date('2024-07', 'yyyy-mm')), partition p202407 values less than(to_date('2024-08', 'yyyy-mm')), partition p202408 values less than(to_date('2024-09', 'yyyy-mm')), partition p202409 values less than(to_date('2024-10', 'yyyy-mm')), partition p202410 values less than(to_date('2024-11', 'yyyy-mm')), partition p202411 values less than(to_date('2024-12', 'yyyy-mm')), partition p202412 values less than(to_date('2025-01', 'yyyy-mm')), partition p202501 values less than(to_date('2025-02', 'yyyy-mm')), partition p202502 values less than(to_date('2025-03', 'yyyy-mm')), partition p202503 values less than(to_date('2025-04', 'yyyy-mm')), partition p202504 values less than(to_date('2025-05', 'yyyy-mm')), partition p202505 values less than(to_date('2025-06', 'yyyy-mm')), partition p202506 values less than(to_date('2025-07', 'yyyy-mm')), partition p202507 values less than(to_date('2025-08', 'yyyy-mm')), partition p202508 values less than(to_date('2025-09', 'yyyy-mm')), partition p202509 values less than(to_date('2025-10', 'yyyy-mm')), partition p202510 values less than(to_date('2025-11', 'yyyy-mm')), partition p202511 values less than(to_date('2025-12', 'yyyy-mm')), partition p202512 values less than(to_date('2026-01', 'yyyy-mm')), partition p202601 values less than(to_date('2026-02', 'yyyy-mm')), partition p202602 values less than(to_date('2026-03', 'yyyy-mm')), partition p202603 values less than(to_date('2026-04', 'yyyy-mm')), partition p202604 values less than(to_date('2026-05', 'yyyy-mm')), partition p202605 values less than(to_date('2026-06', 'yyyy-mm')), partition p202606 values less than(to_date('2026-07', 'yyyy-mm')), partition p202607 values less than(to_date('2026-08', 'yyyy-mm')), partition p202608 values less than(to_date('2026-09', 'yyyy-mm')), partition p202609 values less than(to_date('2026-10', 'yyyy-mm')), partition p202610 values less than(to_date('2026-11', 'yyyy-mm')), partition p202611 values less than(to_date('2026-12', 'yyyy-mm')), partition p202612 values less than(to_date('2027-01', 'yyyy-mm')), partition p202701 values less than(to_date('2027-02', 'yyyy-mm')), partition p202702 values less than(to_date('2027-03', 'yyyy-mm')), partition p202703 values less than(to_date('2027-04', 'yyyy-mm')), partition p202704 values less than(to_date('2027-05', 'yyyy-mm')), partition p202705 values less than(to_date('2027-06', 'yyyy-mm')), partition p202706 values less than(to_date('2027-07', 'yyyy-mm')), partition p202707 values less than(to_date('2027-08', 'yyyy-mm')), partition p202708 values less than(to_date('2027-09', 'yyyy-mm')), partition p202709 values less than(to_date('2027-10', 'yyyy-mm')), partition p202710 values less than(to_date('2027-11', 'yyyy-mm')), partition p202711 values less than(to_date('2027-12', 'yyyy-mm')), partition p202712 values less than(to_date('2028-01', 'yyyy-mm')), partition p202801 values less than(to_date('2028-02', 'yyyy-mm')), partition p202802 values less than(to_date('2028-03', 'yyyy-mm')), partition p202803 values less than(to_date('2028-04', 'yyyy-mm')), partition p202804 values less than(to_date('2028-05', 'yyyy-mm')), partition p202805 values less than(to_date('2028-06', 'yyyy-mm')), partition p202806 values less than(to_date('2028-07', 'yyyy-mm')), partition p202807 values less than(to_date('2028-08', 'yyyy-mm')), partition p202808 values less than(to_date('2028-09', 'yyyy-mm')), partition p202809 values less than(to_date('2028-10', 'yyyy-mm')), partition p202810 values less than(to_date('2028-11', 'yyyy-mm')), partition p202811 values less than(to_date('2028-12', 'yyyy-mm')), partition p202812 values less than(to_date('2029-01', 'yyyy-mm')), partition p202901 values less than(to_date('2029-02', 'yyyy-mm')), partition p202902 values less than(to_date('2029-03', 'yyyy-mm')), partition p202903 values less than(to_date('2029-04', 'yyyy-mm')), partition p202904 values less than(to_date('2029-05', 'yyyy-mm')), partition p202905 values less than(to_date('2029-06', 'yyyy-mm')), partition p202906 values less than(to_date('2029-07', 'yyyy-mm')), partition p202907 values less than(to_date('2029-08', 'yyyy-mm')), partition p202908 values less than(to_date('2029-09', 'yyyy-mm')), partition p202909 values less than(to_date('2029-10', 'yyyy-mm')), partition p202910 values less than(to_date('2029-11', 'yyyy-mm')), partition p202911 values less than(to_date('2029-12', 'yyyy-mm')), partition p202912 values less than(to_date('2030-01', 'yyyy-mm')), partition p203001 values less than(to_date('2030-02', 'yyyy-mm')), partition p203002 values less than(to_date('2030-03', 'yyyy-mm')), partition p203003 values less than(to_date('2030-04', 'yyyy-mm')), partition p203004 values less than(to_date('2030-05', 'yyyy-mm')), partition p203005 values less than(to_date('2030-06', 'yyyy-mm')), partition p203006 values less than(to_date('2030-07', 'yyyy-mm')), partition p203007 values less than(to_date('2030-08', 'yyyy-mm')), partition p203008 values less than(to_date('2030-09', 'yyyy-mm')), partition p203009 values less than(to_date('2030-10', 'yyyy-mm')), partition p203010 values less than(to_date('2030-11', 'yyyy-mm')), partition p203011 values less than(to_date('2030-12', 'yyyy-mm')), partition p203012 values less than(to_date('2031-01', 'yyyy-mm')), partition pmax values less than(maxvalue)) as select * from zkm.test where 1=2;
不带子分区的示例：
create table zkm.test_tmp PARTITION BY RANGE (create_time) ( partition p201801 values less than(to_date('2018-02', 'yyyy-mm')), partition p201802 values less than(to_date('2018-03', 'yyyy-mm')), partition p201803 values less than(to_date('2018-04', 'yyyy-mm')), partition p201804 values less than(to_date('2018-05', 'yyyy-mm')), partition p201805 values less than(to_date('2018-06', 'yyyy-mm')), partition p201806 values less than(to_date('2018-07', 'yyyy-mm')), partition p201807 values less than(to_date('2018-08', 'yyyy-mm')), partition pmax values less than(maxvalue)) as select * from zkm.test where 1=2;
4.检查是否能够进行重定义
EXEC Dbms_Redefinition.can_redef_table('ZKM','TEST'); 19:25:24 SYS@zkm(31)> EXEC Dbms_Redefinition.can_redef_table('ZKM','TEST'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
注：语句默认是基于主键进行判断，即等价于：EXEC Dbms_Redefinition.can_redef_table('ZKM','TEST',DBMS_REDEFINITION.CONS_USE_PK);
若使用rowid，则使用：
EXEC Dbms_Redefinition.can_redef_table('ZKM','TEST',DBMS_REDEFINITION.CONS_USE_ROWID);
5.执行表的在线重定义
BEGIN DBMS_REDEFINITION.start_redef_table(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP'); END; / 19:29:58 SYS@zkm(31)> BEGIN 19:29:58 2 DBMS_REDEFINITION.start_redef_table(uname => 'ZKM', 19:29:58 3 orig_table => 'TEST', 19:29:58 4 int_table => 'TEST_TMP'); 19:29:58 5 END; 19:29:58 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.13
注：若使用基于rowid模式，则：
BEGIN DBMS_REDEFINITION.start_redef_table(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP', OPTIONS_FLAG => dbms_redefinition.cons_use_rowid); END; / 23:22:52 SYS@zkm(31)> BEGIN 23:23:11 2 DBMS_REDEFINITION.start_redef_table(uname => 'ZKM', 23:23:11 3 orig_table => 'TEST', 23:23:11 4 int_table => 'TEST_TMP', 23:23:11 5 OPTIONS_FLAG => dbms_redefinition.cons_use_rowid); 23:23:11 6 END; 23:23:11 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.82
6.复制表的依赖对象
值得注意的是，复制索引会因为无法使用并行导致非常慢。
复制约束的话，如果创建中间表的时候字段已经同步更改为NOT NULL的话，会导致复制约束过程中报错ORA-01442，报错参考：
ERROR at line 1: ORA-01442: column to be modified to NOT NULL is already NOT NULL ORA-06512: at "SYS.DBMS_REDEFINITION", line 1015 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1907 ORA-06512: at line 4 18:35:24 SYS@xxxxxx1(2177)> col BASE_TABLE_NAME for a25 18:35:42 SYS@xxxxxx1(2177)> col DDL_TXT for a100 18:35:48 SYS@xxxxxx1(2177)> select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS; OBJECT_NAME BASE_TABLE_NAME DDL_TXT ------------------------- ------------------------- ---------------------------------------------------------------------------------------------------- SYS_C0012493 XXXX_XXXXXXXXX ALTER TABLE "XXXXXXXXX"."XXXX_XXXXXXXXX_ZKM" MODIFY ("FILE_SIZE" CONSTRAINT "TMP$$_SYS_C00124930" NOT NULL ENABLE NOVALIDATE) Elapsed: 00:00:00.00
set serveroutput on DECLARE l_errors NUMBER; BEGIN DBMS_REDEFINITION.copy_table_dependents(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP', copy_indexes => dbms_redefinition.cons_orig_params, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => FALSE, num_errors => l_errors, copy_statistics => TRUE, copy_mvlog => FALSE); DBMS_OUTPUT.put_line('Errors=' || l_errors); END; / 19:56:47 SYS@zkm(31)> set serveroutput on 19:56:57 SYS@zkm(31)> DECLARE 19:56:57 2 l_errors NUMBER; 19:56:57 3 BEGIN 19:56:57 4 DBMS_REDEFINITION.copy_table_dependents(uname => 'ZKM', 19:56:57 5 orig_table => 'TEST', 19:56:57 6 int_table => 'TEST_TMP', 19:56:57 7 copy_indexes => dbms_redefinition.cons_orig_params, 19:56:57 8 copy_triggers => TRUE, 19:56:57 9 copy_constraints => TRUE, 19:56:57 10 copy_privileges => TRUE, 19:56:57 11 ignore_errors => FALSE, 19:56:57 12 num_errors => l_errors, 19:56:58 13 copy_statistics => TRUE, 19:56:58 14 copy_mvlog => FALSE); 19:56:58 15 19:56:58 16 DBMS_OUTPUT.put_line('Errors=' || l_errors); 19:56:58 17 END; 19:56:58 18 / Errors=0 PL/SQL procedure successfully completed. Elapsed: 00:00:02.58
注：注意检查Errors的值。
其他说明：
DECLARE l_errors NUMBER; BEGIN DBMS_REDEFINITION.copy_table_dependents(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP', copy_indexes => dbms_redefinition.cons_orig_params, --是否复制索引 copy_triggers => TRUE, --是否复制触发器 copy_constraints => TRUE, --是否复制约束 copy_privileges => TRUE, --是否复制权限 ignore_errors => FALSE, --是否跳出某一项复制出错 num_errors => l_errors, --复制过程中的出错数目 copy_statistics => TRUE, --是否复制统计信息 copy_mvlog => FALSE); --是否复制物化视图日志 DBMS_OUTPUT.put_line('Errors=' || l_errors); END; /
查看test_tmp表的索引信息和权限信息：
set linesize 500 col column_names for a50 col INDEX_NAME for a30 col value for a8 select index_name,uniqueness,wm_concat(column_name) column_names,status from (select distinct a.INDEX_NAME,a.COLUMN_NAME,b.uniqueness,b.status,a.COLUMN_POSITION from dba_ind_columns a,dba_indexes b where a.TABLE_OWNER=upper('zkm') and a.TABLE_NAME=upper('test_tmp') and a.index_name=b.index_name and a.INDEX_OWNER=b.owner and a.TABLE_OWNER=b.TABLE_OWNER and a.TABLE_NAME=b.TABLE_NAME order by 1,4) t group by index_name,uniqueness,status order by uniqueness desc; 20:35:40 SYS@zkm(27)> select * from dba_tab_privs where grantee='SCOTT'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------------------- ------------------------- ------------------------------ ------------------------- ------------------------------ --- --- SCOTT ZKM TEST ZKM SELECT NO NO SCOTT ZKM TEST_TMP ZKM SELECT NO NO Elapsed: 00:00:00.02 20:57:41 SYS@zkm(27)> set linesize 500 20:57:55 SYS@zkm(27)> col column_names for a50 20:57:55 SYS@zkm(27)> col INDEX_NAME for a30 20:57:55 SYS@zkm(27)> col value for a8 20:57:59 SYS@zkm(27)> select index_name,uniqueness,wm_concat(column_name) column_names,status from (select distinct a.INDEX_NAME,a.COLUMN_NAME,b.uniqueness,b.status,a.COLUMN_POSITION from dba_ind_columns a,dba_indexes b where a.TABLE_OWNER=upper('zkm') and a.TABLE_NAME=upper('test_tmp') and a.index_name=b.index_name and a.INDEX_OWNER=b.owner and a.TABLE_OWNER=b.TABLE_OWNER and a.TABLE_NAME=b.TABLE_NAME order by 1,4) t group by index_name,uniqueness,status order by uniqueness desc; INDEX_NAME UNIQUENESS COLUMN_NAMES STATUS ------------------------------ ---------- -------------------------------------------------- -------- TMP$$_PK_ID0 UNIQUE ID VALID TMP$$_IDX_COL10 NONUNIQUE COL1 VALID Elapsed: 00:00:00.06
7.随时可以查看报错信息
select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS;
8.同步数据
BEGIN dbms_redefinition.sync_interim_table(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP'); END; / 20:40:39 SYS@zkm(31)> BEGIN 20:40:40 2 dbms_redefinition.sync_interim_table(uname => 'ZKM', 20:40:40 3 orig_table => 'TEST', 20:40:40 4 int_table => 'TEST_TMP'); 20:40:40 5 END; 20:40:40 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.10
9.结束在线重定义过程
BEGIN dbms_redefinition.finish_redef_table(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP'); END; / 20:58:56 SYS@zkm(31)> BEGIN 20:59:59 2 dbms_redefinition.finish_redef_table(uname => 'ZKM', 20:59:59 3 orig_table => 'TEST', 20:59:59 4 int_table => 'TEST_TMP'); 20:59:59 5 END; 20:59:59 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.18
10.检查其数据、结构、索引等依赖对象是否正确
表test是否成分区表：
22:56:45 SYS@zkm(31)> select partitioned from dba_tables where table_name='TEST' and owner='ZKM'; PAR --- YES Elapsed: 00:00:00.02
表test的索引信息：
set linesize 500 col column_names for a50 col INDEX_NAME for a30 col value for a8 col uniqueness for a10 select index_name,uniqueness,wm_concat(column_name) column_names,status from (select distinct a.INDEX_NAME,a.COLUMN_NAME,b.uniqueness,b.status,a.COLUMN_POSITION from dba_ind_columns a,dba_indexes b where a.TABLE_OWNER=upper('zkm') and a.TABLE_NAME=upper('test') and a.index_name=b.index_name and a.INDEX_OWNER=b.owner and a.TABLE_OWNER=b.TABLE_OWNER and a.TABLE_NAME=b.TABLE_NAME order by 1,4) t group by index_name,uniqueness,status order by uniqueness desc; 22:48:36 SYS@zkm(27)> set linesize 500 22:49:16 SYS@zkm(27)> col column_names for a50 22:49:16 SYS@zkm(27)> col INDEX_NAME for a30 22:49:16 SYS@zkm(27)> col value for a8 22:49:16 SYS@zkm(27)> col uniqueness for a10 22:49:16 SYS@zkm(27)> select index_name,uniqueness,wm_concat(column_name) column_names,status from (select distinct a.INDEX_NAME,a.COLUMN_NAME,b.uniqueness,b.status,a.COLUMN_POSITION from dba_ind_columns a,dba_indexes b where a.TABLE_OWNER=upper('zkm') and a.TABLE_NAME=upper('test') and a.index_name=b.index_name and a.INDEX_OWNER=b.owner and a.TABLE_OWNER=b.TABLE_OWNER and a.TABLE_NAME=b.TABLE_NAME order by 1,4) t group by index_name,uniqueness,status order by uniqueness desc; INDEX_NAME UNIQUENESS COLUMN_NAMES STATUS ------------------------------ ---------- -------------------------------------------------- -------- PK_ID UNIQUE ID VALID IDX_COL1 NONUNIQUE COL1 VALID Elapsed: 00:00:00.03
这里的索引名字也自动换过来。
11.回退操作（参考）
begin dbms_redefinition.abort_redef_table(uname => 'ZKM', orig_table => 'TEST', int_table => 'TEST_TMP'); end; /
注：start_redef_table（包括）开始之后到finish_redef_table（包括）结束之间任何一步若报错失败，可通过abort_redef_table回退，也为重新开始做准备。
12.重新收集统计信息（参考）
exec dbms_stats.gather_table_stats(ownname => 'ZKM',tabname => 'TEST_TMP',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE REPEAT',degree => 2,no_invalidate => false);
注：若无复制统计信息或者想收集新的统计信息，参考语句如上。
如果是结束重定义后，则对表TEST收集。
13.重命名约束，索引（参考）
若不使用copy_table_dependents复制依赖对象，则中途手工创建的约束和索引由于名字不同，需要结束重定义后重命名，参考语句如下：
col CONSTRAINT_NAME for a25 col R_CONSTRAINT_NAME for a15 select CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME from dba_constraints where OWNER='ZKM' and TABLE_NAME='TEST'; conn zkm/oracle alter index index_name rename to new_index_name; alter table test rename constraint pk_id_tmp to pk_id;
14.允许行移动
若业务存在对分区字段的update操作，还需要开启行移动。
23:10:30 SYS@zkm(31)> alter table zkm.test enable row movement; Table altered. Elapsed: 00:00:00.08
15.删除中间表
23:10:42 SYS@zkm(31)> drop table zkm.test_tmp purge; Table dropped. Elapsed: 00:00:00.11
注意：
对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$，结束重定义后变为SYS_CXXXXXXXXXXXX。该列为unused状态，推荐使用下列语句删除。
插一点，这里很类似我之前遇到过的一个问题：DBMS_METADATA.GET_DDL查出不存在的列SYS_C00014_20070116:47:09$
23:30:41 SYS@zkm(27)> select COLUMN_NAME,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' AND TABLE_NAME='TEST'; COLUMN_NAME HID ------------------------------ --- ID NO COL1 NO CREATE_TIME NO SYS_C00004_21082223:24:59$ YES Elapsed: 00:00:00.00
get ddl无法查看到隐藏列：
23:53:28 SYS@zkm(31)> select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST','ZKM') -------------------------------------------------------------------------------- CREATE TABLE "ZKM"."TEST" ( "ID" NUMBER(*,0), "COL1" VARCHAR2(255), "CREATE_TIME" DATE, CONSTRAINT "PK_ID" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY RANGE ("CREATE_TIME") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION "P200001" VALUES LESS THAN (TO_DATE(' 2000-02-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ) Elapsed: 00:00:01.87
我对dbms_redefinition.finish_redef_table过程做10046能够发现，抓取到如下语句（sqlid='dbcjnkpkvgy5w'）：
1 update col$ 2 set name = :3, 3 segcol# = :4, 4 type# = :5, 5 length = :6, 6 precision# = decode(:5, 7 182 /*DTYIYM*/, 8 :7, 9 183 /*DTYIDS*/, 10 :7, 11 decode(:7, 0, null, :7)), 12 scale = decode(:5, 13 2, 14 decode(:8, -127 /*MAXSB1MINAL*/, null, :8), 15 178, 16 :8, 17 179, 18 :8, 19 180, 20 :8, 21 181, 22 :8, 23 182, 24 :8, 25 183, 26 :8, 27 231, 28 :8, 29 null), 30 null$ = :9, 31 fixedstorage = :10, 32 segcollength = :11, 33 col# = :12, 34 property = :13, 35 charsetid = :14, 36 charsetform = :15, 37 spare1 = :16, 38 spare2 = :17, 39 spare3 = :18, 40 deflength = decode(:19, 0, null, :19), 41 default$ = :20 42 where obj# = :1 43 and intcol# = :2
其中，第2行的name='SYS_C00004_21082223:24:59$'，最后的第42行obj#=181852。
查询object id为181852的对象：
08:57:39 SYS@test(1061)> select owner,object_name from dba_objects where object_id=181852; OWNER OBJECT_NAME ------------------------- ------------------------------ ZKM TEST Elapsed: 00:00:00.00
也就是M_ROW$$被改名为SYS_C00004_21082223:24:59$。
删除：
23:49:13 SYS@zkm(31)> alter table zkm.test drop unused columns; Table altered. Elapsed: 00:00:00.08 23:49:23 SYS@zkm(31)> select COLUMN_NAME,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' AND TABLE_NAME='TEST'; COLUMN_NAME HID ------------------------------ --- ID NO COL1 NO CREATE_TIME NO Elapsed: 00:00:00.04
至此。
关于REGISTER_DEPENDENT_OBJECT
定义发生变化的情况下(比如修改了主键)，则使用register_dependent_object。
比如最终想修改原表test的主键为pk_id(id,col1)，并且名字不变的时候，需要在test_tmp创建主键如：pk_id_tmp(id,col1)。
然后使用如下语句进行注册：
begin DBMS_REDEFINITION.register_dependent_object(uname => 'ZKM', orig_table => 'TEST', ini_table => 'TEST_TMP', dep_type => 3, dep_owner => 'ZKM', dep_orig_name => 'pk_id', dep_int_name => 'pk_id_tmp'); end; /
* 2 - index
* 3 - constraint
* 4 - trigger
* 10 - Materialized View Log
这样最后结束重定义后，test的主键名字仍然为pk_id，但是主键就变为(id,col1)了，即名字不变，变更定义。
类似于copy_table_dependents，只不过copy_table_dependents是定义不变，这货也是不变更对象名字。
如果是手工在test_tmp创建的索引等对象，索引的名字会更交换到test表上。
参考链接
How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)
HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (文档 ID 1304838.1)
ORA-1442 Error During Online Redefinition - DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (文档 ID 1116785.1)