如何通过dblink truncate远程数据库上的表

一般情况下，当我们直接truncate一个远程的表的时候，通常会返回如下的错误信息：
ORA-02021: DDL operations are not allowed on a
remote database.


比如下面的示例：


先在数据库test上创建一个test表，并插入一些数据，如下：
SQL> conn toms/toms
已连接。
SQL> select instance_name from v$instance;


INSTANCE_NAME
----------------
test
SQL> create table test(no int);


表汛唇ā?


SQL> insert into test values(100);


已创建 1 行。


SQL> commit;
提交完成




然后在另外一个数据库（study）上建一个 dblink,并尝试去truncate test数据库上
toms用户下的test表：
SQL> select instance_name from v$instance;


INSTANCE_NAME
----------------
study
SQL> create database link from_test connect to toms 
identified by toms using 'local_test';


数据库链接已创建。


SQL> select * from toms.test@from_test;


未选定行


SQL> truncate table toms.test@from_test;
truncate table toms.test@from_test
                         *
ERROR 位于第 1 行:
ORA-02021: 不允许对远程数据库进行 DDL 操作




SQL> 


这时，我们得到了ORA-02021这样的错误，Oracle不允许这么做。那么如果确实有这样的需求，
我们该怎么办呢。下面我介绍一个方法：


先在test数据库上，建立一个类似如下的procedure:


SQL> create or replace procedure proc_truncate_remote_tab(p_tname in varchar2) as
  2  BEGIN
  3     EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_tname;
  4  EXCEPTION
  5     WHEN OTHERS THEN
  6          raise_application_error(-20001,SQLERRM);
  7  end;
  8  /


过程已创建。




然后在study数据库上调用test数据库上的这个procedure去完成这个truncate动作：
BEGIN
   proc_truncate_remote_tab@db_link('remote_table_name');
END;


下面测试验证一下：
SQL> select instance_name from v$instance;


INSTANCE_NAME
----------------
study


SQL> 
SQL> select *from test@from_test;


        NO
----------
       100


SQL>  begin
  2      proc_truncate_remote_tab@from_test('test');
  3   end;
  4  /


PL/SQL 过程已成功完成。


SQL> select *from test@from_test;


未选定行


SQL> 
可以看到，远程test数据库上toms用户下的表test已经被truncate掉了。