解决方案

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.

将执行两个 exchange partition 命令。

第一个是从源表到临时表。

第二个是从临时表到真正的目标表。

第 11-18 做了个安全保护，确保列名（包括隐藏的）通过简单的哈希来对齐

 

创建表来测试

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.

表 T1 和 T2 应该可以进行交换

由于表结构不同，表 T1 和 T3 应该不正常。

SQL> SQL> exec exchange_par('T1','T2','PX','PY');

PL/SQL procedure successfully completed.


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 "SYS.EXCHANGE_PAR", line 20
ORA-06512: at line 1