分区交换 技术

分区交换，可以实现，普通表和分区表的某个分区之间的数据交换，它们之间的交换非常快。

/**====================================================================*/select t.table_name , t.owner  from ALL_PART_TABLES t  where t.owner = 'SCOTT';  select t.table_name ,t.partitioning_type,t.partition_count  from  USER_PART_TABLES  t;  select t.table_name,t.table_owner,t.partition_name ,t.partition_position from DBA_TAB_PARTITIONS t  where t.table_owner = 'SCOTT'and t.table_name = upper('range_part_tab') ;  --获取2016年的数据select count(*)  from range_part_tab  partition (PAR_LIST_MAX) ;drop table target_exchange_tab purge;create table target_exchange_tab asselect *  from range_part_tab where 2 = 1; --查看目标表是否有索引select count(*) from user_indexes T  where table_name= upper('target_change_tab') ; --将该数据和目标表进行交换alter table range_part_tab exchange partition PAR_LIST_MAX with table target_exchange_tab ;select count(*)  from range_part_tab  partition (PAR_LIST_MAX) ;select count(*)  from target_exchange_tab ; --再执行一次 ,数据就回来了alter table range_part_tab exchange partition PAR_LIST_MAX with table target_exchange_tab ;select count(*)  from range_part_tab  partition (PAR_LIST_MAX) ;select count(*)  from target_exchange_tab ; --复制truncate table target_exchange_tab ; insert into target_exchange_tabselect *  from range_part_tab  partition (PAR_LIST_MAX) where rownum <= 1000;commit;select count(*)  from range_part_tab  partition (PAR_LIST_MAX) ;select count(*)  from target_exchange_tab ;