Oracle使用在线重定义将普通表转为分区表
前言
使用这功能也很久了，总想着总结一下但是一拖就拖两三年。
然后每次要在线重定义的时候就找以前案例的脚本来改......
目前在线重定义这个功能用到现在自己有用到一下几个目的：
普通表转分区表，表迁移表空间，表回收碎片。
另外我看网上说还可以增加/删除列，这个我就没试过了。
仿伪链接：https://www.cnblogs.com/PiscesCanon/p/15173675.html
实验
本次实验是基于主键的方式来进行的，将普通表转为分区表。
创建实验表。
create table zkm.test
(
id int,
col1 varchar2(255) default 'zkm' not null,
create_time date,
constraint pk_id primary key (id)
);
15:25:52 SYS@zkm(497)> create table zkm.test
15:26:04 2 (
15:26:04 3 id int,
15:26:04 4 col1 varchar2(255) default 'zkm' not null,
15:26:04 5 create_time date,
15:26:04 6 constraint pk_id primary key (id)
15:26:04 7 );
Table created.
Elapsed: 00:00:00.00
插入数据。
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 min(create_time) from zkm.test;
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报错。
这里不使用CTAS方式，并且字段col1的NOT NULL属性去掉，由下边第6步骤(复制表的依赖对象)完善。
按天的：interval(numtodsinterval(1,'day'))
create table zkm.test_tmp
(
id int,
col1 varchar2(255) default 'zkm',
create_time date
)
PARTITION BY RANGE (create_time) INTERVAL (numtoyminterval(1, 'month'))
( partition p200001 values less than(to_date('2000-02', 'yyyy-mm')));
15:30:08 SYS@zkm(497)> create table zkm.test_tmp
15:30:08 2 (
15:30:08 3 id int,
15:30:08 4 col1 varchar2(255) default 'zkm',
15:30:08 5 create_time date
15:30:08 6 )
15:30:08 7 PARTITION BY RANGE (create_time) INTERVAL (numtoyminterval(1, 'month'))
15:30:08 8 ( partition p200001 values less than(to_date('2000-02', 'yyyy-mm')));
Table created.
Elapsed: 00:00:00.01
注：
由于间隔分区的分区字段不允许为空，因此会无法使用间隔分区特性导致需要手工创建大量分区。
若是不适用间隔分区，则手工创建分区。这里添加示例代码。
带子分区的示例：
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
另外：如果原表(为非普通表)为间隔分区类型且存在本地索引，会遇到Bug 28487821 : DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS FAILS WITH ORA-14024
详见：DBMS_REDEFINITION Does Not Work With Interval Partitions (Doc ID 1961587.1)
改BUG在19.1修复，或者打上补丁28487821修复BUG。
我采用copy_indexes => 0不复制索引避免(手工创建索引)。
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.NOT NULL依赖的问题处理
desc表会发现无法看到NOT NULL，如下：
15:35:18 SYS@zkm(497)> desc zkm.test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
COL1 VARCHAR2(255)
CREATE_TIME DATE
这是因为第6步复制依赖时，NOT NULL属性是NOVALIDATE的，也就是不对原来的数据做校验以加快速度，因为原来数据肯定都是NOT NULL的。
15:48:15 SYS@zkm(489)> select constraint_name,constraint_type,search_condition,status,validated from dba_constraints where table_name='TEST' and owner='ZKM';
CONSTRAINT_NAME CON SEARCH_CONDITION STATUS VALIDATED
------------------------- --- ----------------------- ------------------------ ---------------------------------------
PK_ID P ENABLED VALIDATED
SYS_C0016493 C "COL1" IS NOT NULL ENABLED NOT VALIDATED
Elapsed: 00:00:00.01
16:21:50 SYS@zkm(489)> desc zkm.test
Name Null? Type
----------------------- -------- ----------------
ID NUMBER(38)
COL1 VARCHAR2(255)
CREATE_TIME DATE
注意：这里的主键列ID虽然是VALIDATED，但是desc无法出现not null，应该是BUG。
修改相关约束为VALIDATED：
16:39:13 SYS@zkm(497)> ALTER TABLE ZKM.TEST MODIFY CONSTRAINT PK_ID ENABLE NOVALIDATE;
Table altered.
Elapsed: 00:00:00.01
16:39:35 SYS@zkm(497)> ALTER TABLE ZKM.TEST MODIFY CONSTRAINT PK_ID ENABLE VALIDATE;
Table altered.
Elapsed: 00:00:00.01
16:45:33 SYS@zkm(497)> ALTER TABLE ZKM.TEST MODIFY CONSTRAINT SYS_C0016493 ENABLE VALIDATE;
Table altered.
Elapsed: 00:00:00.01
16:46:01 SYS@zkm(497)> desc zkm.test
Name Null? Type
----------------------- -------- ----------------
ID NOT NULL NUMBER(38)
COL1 NOT NULL VARCHAR2(255)
CREATE_TIME DATE
11.检查其数据、结构、索引等依赖对象是否正确
表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
这里的索引名字也自动换过来。
12.回退操作(参考)
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回退，也为重新开始做准备。
13.重新收集统计信息(参考)
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收集。
14.重命名约束，索引(参考)
若不使用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;
15.允许行移动
若业务存在对分区字段的update操作，还需要开启行移动。
23:10:30 SYS@zkm(31)> alter table zkm.test enable row movement;
Table altered.
Elapsed: 00:00:00.08
16.删除中间表
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)
DESCRIBE Showing Nullable for Columns With NOT NULL Constraint (文档 ID 1273754.1)
Why are Not Null Constraints not Copied by Dbms_redefinition.copy_table_dependents (文档 ID 1089860.1)
Change Relation Of Constraint And Associated Index After DBMS_REDEFINITION Execution (文档 ID 2428791.1)
扫一扫
4713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
