一、使用交换分区的场景
将数据归档至历史表，有以下需求
t1分区表中有p1,p2,p3分区，现在要将p1分区放到t1_bak分区表中的p1分区，可以通过交换分区的方式
1.将t1:p1分区和临时表t1_tmp进行交换
2.将t1_bak:p1分区和临时表t1_tmp进行交换
二、构建测试数据
create user zhang identified by zhang;
grant connect,resource,unlimited tablespace to zhang;
conn zhang/zhang
-- 创建t1分区表
create table t1 (
id number,
name varchar2(100),
created_date date
)
partition by range (created_date) (
partition p1 values less than (to_date('2025-02-01', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2025-03-01', 'yyyy-mm-dd')),
partition p3 values less than (maxvalue)
);
-- 创建t1_bak分区表
create table t1_bak(
id number,
name varchar2(100),
created_date date
)
partition by range (created_date) (
partition p1 values less than (to_date('2025-02-01', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2025-03-01', 'yyyy-mm-dd')),
partition p3 values less than (maxvalue)
);
-- 创建t1_tmp临时表
create table t1_tmp (
id number,
name varchar2(100),
created_date date
);
-- 插入 1 万行数据到t1的分区 p1
begin
for i in 1..10000 loop
insert into t1 values (i, 'name' || i, to_date('2025-01-15', 'yyyy-mm-dd'));
end loop;
commit;
end;
/
-- 插入 1 万行数据到t1的分区 p2
begin
for i in 10001..20000 loop
insert into t1 values (i, 'name' || i, to_date('2025-02-15', 'yyyy-mm-dd'));
end loop;
commit;
end;
/
--创建索引
create index idx_t1_1 on t1(id);
create index idx_t1_2 on t1(name) local;
create index idx_t1_bak_1 on t1_bak(id);
create index dix_t1_bak_2 on t1_bak(name) local;
create index idx_t1_tmp on t1_tmp(name);
--收集统计信息
exec dbms_stats.gather_table_stats('ZHANG','T1',method_opt=>'for all columns size auto',cascade=>true,granularity=>'all',no_invalidate=>false);
三、测试
交换其中一个分区
--检查交换前的全局索引和分区索引
SQL> @dtp
Enter value for table_name: T1
old 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='&table_name'
new 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1'
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE INTE TABLESPACE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
--------------- ---------------------- -------------------- -------------------------------------------------------------------------------- ---- ------------------ ------------ ----------- ----------------------
ZHANG T1 1:P1 TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS 10,000 1,006 20250212 11:07:33
ZHANG T1 2:P2 TO_DATE(' 2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS 10,000 1,006 20250212 11:07:33
ZHANG T1 3:P3 MAXVALUE NO USERS 0 0 20250212 11:07:33
SQL> @dind
Session altered.
Enter value for table_name: T1
old 22: where table_name='&table_name'
new 22: where table_name='T1'
OWNER(IDX|TAB) TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS PRAT BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS STATUS CF LOG PARA ORP BUFFER_ LAST_ANALYZED
---------------------- ------------------------- ------------------------- ----------- ----------- ----- ------ ----------- ------------- ------------ ----------- --------- --- ---- --- ------- ----------------------
ZHANG|ZHANG T1 IDX_T1_1 NORMAL NONUNIQUE NO 1 55 20000 20,000 VALID 75 YES 1|1 NO DEFAULT 20250212 11:07:33
ZHANG|ZHANG T1 IDX_T1_2 NORMAL NONUNIQUE YES 1 58 19958 20,000 N/A 1941 1|1 NO DEFAULT 20250212 11:07:33
SQL> @dip
Enter value for table_name: T1
old 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='&table_name') order by 1,2,3
new 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='T1') order by 1,2,3
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ------------------------------ ---------- ------ ------------ ----------------- ------------- ------------ ----------------------
ZHANG IDX_T1_2 P1 USABLE 1 28 1902 10,000 10,000 20250212 11:07:33
ZHANG IDX_T1_2 P2 USABLE 1 30 39 10,000 10,000 20250212 11:07:33
ZHANG IDX_T1_2 P3 USABLE 0 0 0 0 0 20250212 11:07:33
--交换分区t1:p1到t1_tmp
SQL> alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes;
Table altered.
--交换分区t1_tmp到t1_bak:p1
SQL> alter table t1_bak exchange partition p1 with table t1_tmp including indexes update global indexes;
Table altered.
--检查交换后的全局索引和分区索引
SQL> @dtp
Enter value for table_name: T1
old 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='&table_name'
new 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1'
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE INTE TABLESPACE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
--------------- ---------------------- -------------------- -------------------------------------------------------------------------------- ---- ------------------ ------------ ----------- ----------------------
ZHANG T1 1:P1 TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS 0 0 20250212 11:06:38
ZHANG T1 2:P2 TO_DATE(' 2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS 10,000 1,006 20250212 11:07:33
ZHANG T1 3:P3 MAXVALUE NO USERS 0 0 20250212 11:07:33
SQL> @dind
Session altered.
Enter value for table_name: T1
old 22: where table_name='&table_name'
new 22: where table_name='T1'
OWNER(IDX|TAB) TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS PRAT BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS STATUS CF LOG PARA ORP BUFFER_ LAST_ANALYZED
---------------------- ------------------------- ------------------------- ----------- ----------- ----- ------ ----------- ------------- ------------ ----------- --------- --- ---- --- ------- ----------------------
ZHANG|ZHANG T1 IDX_T1_1 NORMAL NONUNIQUE NO 1 55 20000 20,000 VALID 75 YES 1|1 NO DEFAULT 20250212 11:07:33
ZHANG|ZHANG T1 IDX_T1_2 NORMAL NONUNIQUE YES 1 58 19958 20,000 N/A 1941 1|1 NO DEFAULT 20250212 11:07:33
SQL> @dip
Enter value for table_name: T1
old 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='&table_name') order by 1,2,3
new 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='T1') order by 1,2,3
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ------------------------------ ---------- ------ ------------ ----------------- ------------- ------------ ----------------------
ZHANG IDX_T1_2 P1 USABLE 0 0 0 0 0 20250212 11:06:38
ZHANG IDX_T1_2 P2 USABLE 1 30 39 10,000 10,000 20250212 11:07:33
ZHANG IDX_T1_2 P3 USABLE 0 0 0 0 0 20250212 11:07:33
SQL> @dtp
Enter value for table_name: T1_BAK
old 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='&table_name'
new 1: SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION||':'||PARTITION_NAME PARTITION_NAME,HIGH_VALUE,INTERVAL,TABLESPACE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1_BAK'
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE INTE TABLESPACE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
--------------- ---------------------- -------------------- -------------------------------------------------------------------------------- ---- ------------------ ------------ ----------- ----------------------
ZHANG T1_BAK 1:P1 TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS 10,000 1,006 20250212 11:07:33
ZHANG T1_BAK 2:P2 TO_DATE(' 2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO USERS
ZHANG T1_BAK 3:P3 MAXVALUE NO USERS
SQL> @dind
Session altered.
Enter value for table_name: T1_BAK
old 22: where table_name='&table_name'
new 22: where table_name='T1_BAK'
OWNER(IDX|TAB) TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS PRAT BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS STATUS CF LOG PARA ORP BUFFER_ LAST_ANALYZED
---------------------- ------------------------- ------------------------- ----------- ----------- ----- ------ ----------- ------------- ------------ ----------- --------- --- ---- --- ------- ----------------------
ZHANG|ZHANG T1_BAK IDX_T1_BAK_1 NORMAL NONUNIQUE NO 1 0 0 0 VALID 0 YES 1|1 NO DEFAULT 20250211 22:00:06
ZHANG|ZHANG T1_BAK DIX_T1_BAK_2 NORMAL NONUNIQUE YES 0 0 0 0 N/A 0 1|1 NO DEFAULT 20250211 22:00:06
SQL> @dip
Enter value for table_name: T1_BAK
old 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='&table_name') order by 1,2,3
new 1: select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED from dba_ind_partitions where index_name IN (select index_name from dba_part_indexes where owner='ZHANG' and table_name='T1_BAK') order by 1,2,3
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ------------------------------ ---------- ------ ------------ ----------------- ------------- ------------ ----------------------
ZHANG DIX_T1_BAK_2 P1 USABLE 1 28 1902 10,000 10,000 20250212 11:07:33
ZHANG DIX_T1_BAK_2 P2 USABLE 0 0 0 0 0 20250211 13:22:23
ZHANG DIX_T1_BAK_2 P3 USABLE 0 0 0 0 0 20250211 13:22:23
四、测试结论
通过上述输出我们可以得出以下观点
- t1:p1分区已正确交换至t1_bak.p1
- t1和t1_bak的全局索引未失效(依赖于update global indexes参数)
- t1和t1_bak的分区索引未失效(依赖于including indexes 参数)
- 分区交换后dba_indexes的 orphaned_entries字段为NO，说明没有采用异步全局索引维护
- 分区相关的统计信息正确交换，但是全局统计信息未更新，可以考虑交换完成后手工收集全局统计信息
五、注意事项
1.若中间表未建立对应的分区索引，则使用including indexes会提示ORA-14098
SQL> drop index idx_t1_tmp;
Index dropped.
SQL> alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes;
alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
2.若被交换的分区有DML操作，则会提示ORA-00054，DQL操作不影响
-- DML测试
-- 会话1
SQL> update t1 set name='zhang' where id=1500;
1 row updated.
-- 会话2
SQL> alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes;
alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
-- DQL测试
-- 会话1
SQL> set feed only
SQL> select * from t1 partition(p1) a,t1 partition(p1) b;
-- 会话2
SQL> alter table t1 exchange partition p1 with table t1_tmp including indexes update global indexes;
Table altered.
3.若需要交换的分区较多，并且实施过程中全局索引不需要使用，可以考虑不使用update global indexes参数，全部分区交换完成后进行全局索引的重建
alter table t1 exchange partition p1 with table t1_tmp including indexes;
alter table t1_bak exchange partition p1 with table t1_tmp including indexes;
alter index IDX_T1_1 rebuild online; --重建全局索引
4.全局统计信息未更新，可以考虑交换完成后手工收集全局统计信息
exec dbms_stats.gather_table_stats('ZHANG','T1',method_opt=>'for all columns size auto',cascade=>true,granularity=>'global',no_invalidate=>false); exec dbms_stats.gather_table_stats('ZHANG','T1_BAK',method_opt=>'for all columns size auto',cascade=>true,granularity=>'global',no_invalidate=>false);
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
