1.创建测试源表
conn scott/oracle
create table part_test20250208 (id number,name varchar(20),date_n date)
PARTITION BY RANGE (date_n)
(PARTITION P202401 VALUES LESS THAN (TO_DATE('2024-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202402 VALUES LESS THAN (TO_DATE('2024-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202403 VALUES LESS THAN (TO_DATE('2024-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202404 VALUES LESS THAN (TO_DATE('2024-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202405 VALUES LESS THAN (TO_DATE('2024-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202406 VALUES LESS THAN (TO_DATE('2024-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202407 VALUES LESS THAN (TO_DATE('2024-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202408 VALUES LESS THAN (TO_DATE('2024-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202409 VALUES LESS THAN (TO_DATE('2024-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202410 VALUES LESS THAN (TO_DATE('2024-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202411 VALUES LESS THAN (TO_DATE('2024-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202412 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202501 VALUES LESS THAN (TO_DATE('2025-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION MAXPART VALUES LESS THAN (MAXVALUE));
2.源表创建索引
CREATE INDEX idx1 ON part_test20250208 (date_n) local ;
CREATE INDEX idx2 ON part_test20250208 (id,date_n) global ;
3. 插入测试数据
insert into part_test20250208 values (01,'张一',to_date('2024-01-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (02,'张二',to_date('2024-02-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (03,'张三',to_date('2024-03-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (04,'张四',to_date('2024-04-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (05,'张五',to_date('2024-05-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (06,'张六',to_date('2024-06-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (07,'张七',to_date('2024-07-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (08,'张八',to_date('2024-08-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (09,'张九',to_date('2024-09-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (10,'张十',to_date('2024-10-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (11,'李一',to_date('2024-11-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (12,'李二',to_date('2024-12-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into part_test20250208 values (13,'李三',to_date('2025-01-02 01:01:01','yyyy-mm-dd hh24:mi:ss'));
commit;
4.收集表的统计信息
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SCOTT',
tabname => 'part_test20250208',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 1,
cascade => TRUE);
END;
/
5.检查分区是否满足在线重定义的条件
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'SCOTT',
tname => 'part_test20250208',
options_flag => DBMS_REDEFINITION.cons_use_rowid,
part_name => null);
END;
/
----备注：
----options_flag 选项一： dbms_redefinition.cons_use_pk 通过主键的方式，需要表存在主键，否则会报错(默认值)
----options_flag 选项二： dbms_redefinition.cons_use_rowid 通过rowid的方式（推荐这个）
----part_name =null 代表所有分区，还可以指定单个分区
6.查询分区表的数据
select count(1) FROM part_test20250208 PARTITION (P202401);
select count(1) FROM part_test20250208 PARTITION (P202412);
select count(1) FROM part_test20250208 PARTITION (MAXPART);
7.查询分区信息
set linesize 1000
col index_name for a20
col index_type for a20
col TABLE_OWNER for a20
select index_name,index_type,table_owner,status from dba_indexes where table_name=upper('part_test20250208');
INDEX_NAME INDEX_TYPE TABLE_OWNER STATUS
-------------------- -------------------- -------------------- --------
IDX1 NORMAL SCOTT N/A
IDX2 NORMAL SCOTT VALID
select INDEX_NAME,PARTITION_NAME,status from dba_ind_partitions where INDEX_NAME=upper('idx1');
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------------------------------------------------------------------------------------------------------------------- --------
IDX1 MAXPART USABLE
IDX1 P202401 USABLE
IDX1 P202402 USABLE
IDX1 P202403 USABLE
IDX1 P202404 USABLE
IDX1 P202405 USABLE
IDX1 P202406 USABLE
IDX1 P202407 USABLE
IDX1 P202408 USABLE
IDX1 P202409 USABLE
IDX1 P202410 USABLE
IDX1 P202411 USABLE
IDX1 P202412 USABLE
IDX1 P202501 USABLE
select partition_name, tablespace_name from dba_tab_partitions where table_name = upper('part_test20250208');
PARTITION_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------------------------------------
MAXPART USERS
P202401 USERS
P202402 USERS
P202403 USERS
P202404 USERS
P202405 USERS
P202406 USERS
P202407 USERS
P202408 USERS
P202409 USERS
P202410 USERS
P202411 USERS
P202412 USERS
P202501 USERS
TABLESPACE
8.创建测试表；
create table tmp_20250208 (id number,name varchar(20),date_n date)
PARTITION BY RANGE (date_n)
(PARTITION P202401 VALUES LESS THAN (TO_DATE('2024-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202402 VALUES LESS THAN (TO_DATE('2024-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202403 VALUES LESS THAN (TO_DATE('2024-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202404 VALUES LESS THAN (TO_DATE('2024-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202405 VALUES LESS THAN (TO_DATE('2024-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202406 VALUES LESS THAN (TO_DATE('2024-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202407 VALUES LESS THAN (TO_DATE('2024-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202408 VALUES LESS THAN (TO_DATE('2024-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202409 VALUES LESS THAN (TO_DATE('2024-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202410 VALUES LESS THAN (TO_DATE('2024-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202411 VALUES LESS THAN (TO_DATE('2024-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202412 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION P202501 VALUES LESS THAN (TO_DATE('2025-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION MAXPART VALUES LESS THAN (MAXVALUE));
9.开始在线重定义
---定义：
DBMS_REDEFINITION.START_REDEF_TABLE (
uname IN VARCHAR2, --用户
orig_table IN VARCHAR2, --原表
int_table IN VARCHAR2, --目标表
col_mapping IN VARCHAR2 := NULL, --列映射，就是原表列和目标表列的映射关系，如果为NULL，则表示原始表中的所有列，并在重新定义后具有相同的名称
options_flag IN BINARY_INTEGER := 1, --重定义方式，是选择主键还是rowid
orderby_cols IN VARCHAR2 := NULL, --这个可选参数接受列列表（以及可选关键字升序/降序）
part_name IN VARCHAR2 := NULL, --null代表所有分区
continue_after_errors IN BOOLEAN := FALSE, --当重新定义多个分区时，允许在下一个分区上继续执行操作（仅适用于批处理分区重新定义）
copy_vpd_opt IN BINARY_INTEGER := CONS_VPD_NONE, --指定在线重定义时VPD策略的处理方式
refresh_dep_mviews IN VARCHAR2 := 'N', --当设置为‘Y’时，当START_REDEF_TABLE过程运行时，每次SYNC_INTERIM_TABLE 和FINISH_REDEF_TABLE 运行时，将执行依赖的物化视图的快速刷新。
enable_rollback IN BOOLEAN := FALSE); --When set to TRUE, enables the rollback option.
--这一步会把表数据全量复制到临时表
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'part_test20250208',
int_table => 'tmp_20250208',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => NULL );
END;
/
10、复制源表的属性到临时表
---主要就是索引，也可以不做，后面自己单独创建索引
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'part_test20250208', 'tmp_20250208', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
11.在线同步表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'SCOTT',
orig_table => 'part_test20250208',
int_table => 'tmp_20250208',
part_name => NULL);
END;
/
12.目标端创建索引
--如果做了第10步可以不做这个
CREATE INDEX idx1 ON part_test20250208 (date_n) local ;
CREATE INDEX idx2 ON part_test20250208 (id,date_n) global ;
13.完成在线重定义--会短暂锁表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'part_test20250208',
int_table => 'tmp_20250208',
part_name => null);
END;
/
--如果这一步过程中有未提交的事务，会等待：
[oracle@node2:/home/oracle]$ ora curr
INST_ID SID SERIAL# USERNAME SQL_ID EVENT SEQ# SQL_EXEC_ID SECONDS_IN_WAIT BLOCKING_INSTANCE BLOCKING_SESSION
------- -------- ---------- ------------------------------ ------------- ------------------------------ ---------- ----------- --------------- ----------------- ----------------
1 784 12092 SYS c1t6zwrfz6puz SQL*Net message to client 19 16777233 0
1 1168 2882 SCOTT 72g01ak9514wr Wait for Table Lock 2416 16777216 1
[oracle@node2:/home/oracle]$ ora curr
INST_ID SID SERIAL# USERNAME SQL_ID EVENT SEQ# SQL_EXEC_ID SECONDS_IN_WAIT BLOCKING_INSTANCE BLOCKING_SESSION
------- -------- ---------- ------------------------------ ------------- ------------------------------ ---------- ----------- --------------- ----------------- ----------------
1 784 12909 SYS c1t6zwrfz6puz SQL*Net message to client 19 16777234 0
1 1168 2882 SCOTT 72g01ak9514wr enq: TM - contention 2417 16777216 1 1 407
14、收集表的统计信息，检查索引名、并行度等，检查无效对象
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SCOTT',
tabname => 'part_test20250208',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 1,
cascade => TRUE);
END;
/
select index_name,degree,status from dba_indexes where table_name=UPPER('part_test20250208') and owner='SCOTT';
INDEX_NAME DEGREE STATUS
-------------------- ---------------------------------------- --------
IDX1 1 N/A
IDX2 1 VALID
select distinct INDEX_NAME,status from dba_ind_partitions where INDEX_NAME=upper('idx1');
INDEX_NAME STATUS
-------------------- --------
IDX1 USABLE
15、删除临时表
drop table tmp_20250208 purge;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
