A question came in recently.
“Can we exchange a partition from one partitioned table to another without using non-partitioned table as an interim? “
The answer is no. As per the docs
However, if this is a common requirement and you would like the equivalent of:
alter table T1 exchange partition PX with table T2 partition PY
then all it takes is a little PL/SQL to build an API to allow this.
SQL> create sequence seq;
Sequence created.
SQL> create or replace
2 procedure exchange_par(
3 p_table1 varchar2,
4 p_table2 varchar2,
5 p_table1_par varchar2,
6 p_table2_par varchar2) is
7 l_tab_cnt int;
8 l_hash_cnt int;
9 l_interim varchar2(100);
10 begin
11 select count(distinct tot), count(*)
12 into l_hash_cnt, l_tab_cnt
13 from
14 ( select table_name, sum(ora_hash(column_name)) tot
15 from user_tab_cols
16 where table_name in (p_table1,p_table2)
17 group by table_name
18 );
19
20 if l_tab_cnt != 2 or l_hash_cnt != 1 then
21 raise_application_error(-20000,'Dead in the water');
22 end if;
23 l_interim := 'TMPTAB$'||seq.nextval;
24
25 begin
26 execute immediate
27 'drop table '||l_interim||' purge';
28 exception
29 when others then
30 if sqlcode = -942 then null; else raise; end if;
31 end;
32
33 execute immediate
34 'create table '||l_interim||' for exchange with table '||p_table1;
35
36 execute immediate
37 'alter table '||p_table1||' exchange partition '||p_table1_par||
38 ' with table '||l_interim;
39
40 execute immediate
41 'alter table '||p_table2||' exchange partition '||p_table2_par||
42 ' with table '||l_interim;
43
44 execute immediate
45 'drop table '||l_interim||' purge';
46
47 end;
48 /
Procedure created.
The premise here is simple. We’ll do two exchange partition commands
- The first one being from the source table to a temporary table
- The second one being from the temporary table to the true target table.
Lines 11-18 are just a little safety net to protect this from ourselves – we make sure that the column names (including the hidden ones) are highly probable to be in alignment via some simple hashing. It is left as a reader exercise to extend this to data types and the like. (It is not critical because the exchange partition command will simply fail if the database decides it cannot be done).
Now we can create a couple of tables to see it in action
SQL> create table t1
2 partition by list ( owner) automatic
3 ( partition px values ('SYS') )
4 as select * from dba_objects;
Table created.
SQL>
SQL> create table t2
2 partition by list ( owner) automatic
3 ( partition py values ('SYS') )
4 as select * from dba_objects;
Table created.
SQL>
SQL> create table t3
2 partition by list ( owner) automatic
3 ( partition pz values ('SYSTEM') )
4 as select * from dba_segments;
Table created.
Tables T1 and T2 should be ok to do a swap
SQL> exec exchange_par('T1','T2','PX','PY');
PL/SQL procedure successfully completed.
And tables T1 and T3 should not be ok due to the differing table structures.
SQL> exec exchange_par('T1','T3','PX','PZ');
BEGIN exchange_par('T1','T3','PX','PZ'); END;
*
ERROR at line 1:
ORA-20000: Dead in the water
ORA-06512: at "MCDONAC.EXCHANGE_PAR", line 20
ORA-06512: at line 1
The routine can be made as sophisticated as you like, eg, you could also take care of handling the indexes with appropriate use of UPDATE INDEXES in the DDL, or INCLUDING/EXCLUDING VALIDATION clauses depending on your needs. But the main point of this post is that the limitations as per the docs are trivially worked around with a little PL/SQL.
Learning is not a spectator sport 
Got some thoughts? Leave a comment