解决ORA-02021: 不允许对远程数据库进行 DDL 操作下面通过DBLINK调用远程过程来执行这样的操作。

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

注释：

在远程的库建立一个存储过程，这个存储过程声明为自治事务，它接受一个参数，可以是你要建立的表名。
过程里面采用动态SQL建表。
然后你就可以远程调用这个过程。

但不知道这样做有什么实用意义。

上述用在远程的库建立一个存储过程的方法，不能解决“将访问本地数据库上一个方案里的表的权限授权给远程数据库上的一个用户”（即如 grant select on tab to user@dblink（或是global name） ）的问题.

参考：Your database can't establish connection with remote database at compile time. but you hope that it