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

119 阅读 0 评论 79 点赞

我是靠谱客的博主繁荣蚂蚁，最近开发中收集的这篇文章主要介绍如何通过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掉了。