如何通过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掉了。