使用sys用户删除其他schema下的dblink

目标数据库：
SQL> conn mytest/mytest 
Connected.
SQL> 
SQL> create table emp (id int,name varchar2(20));
Table created.
SQL> insert into emp values (1,'zhangsan');
1 row created.

SQL> insert into emp values (2,'wangwu');

1 row created.
SQL> commit;
Commit complete.

源库，先建立到目标数据库的连接
[oracle@r11g admin]$ vi tnsnames.ora
ecctest =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.22.135)(PORT = 1527))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = R6Q)
    )
  )
~
测试连接的有效性
[oracle@r11g admin]$ tnsping ecctest
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 12-DEC-2013 14:16:03
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.22.135)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = R6Q)))
OK (100 msec)
[oracle@r11g admin]$ 

在liutya用户下建立测试用的dblink
SQL> conn liutya/liutya
Connected.
SQL> 
SQL> 
SQL> create database link lecctest connect to mytest identified by mytest using 'ecctest';
create database link lecctest connect to mytest identified by mytest using 'ecctest'
                     *
ERROR at line 1:
ORA-01031: insufficient privileges
这是缺少相应的权限。
SQL> conn / as sysdba
Connected.
SQL> 
SQL> grant create database link to liutya;
Grant succeeded.
SQL> conn liutya/liutya
Connected.

创建DBLINK
SQL>  create database link lecctest connect to mytest identified by mytest using 'ecctest';
Database link created.
SQL> select * from emp@lecctest;
        ID NAME
---------- --------------------
         1 zhangsan
         2 wangwu

下面使用sys做测试删除
SQL> conn / as sysdba
Connected.
SQL> 
SQL> show user;
USER is "SYS"
SQL> set linesize 200
SQL> col owner format a20
SQL> col db_link format a20
SQL> col username format a20
SQL> 
SQL> col host format a60
SQL> 
SQL> select * from dba_db_links;
OWNER                DB_LINK              USERNAME             HOST                                                         CREATED
-------------------- -------------------- -------------------- ------------------------------------------------------------ ---------
LIUTYA               LECCTEST             MYTEST               ecctest                                                      12-DEC-13
SQL> drop database link liutya.lecctest;
drop database link liutya.lecctest
                   *
ERROR at line 1:
ORA-02024: database link not found
使用drop database link 命令是无法删除其他schema下的dblink的，是否还有其他办法呢？下面是从网上找到的两个方法：
方法一、使用下面的脚本，使用job方式删除dblink
begin
  dbms_scheduler.create_job(
    job_name=>'&owner..drop_database_link',
    job_type=>'PLSQL_BLOCK',
    job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
  );
  dbms_scheduler.run_job('&owner..drop_database_link',false);
  dbms_lock.sleep(2);
  dbms_scheduler.drop_job('&owner..drop_database_link');
end;
/
输入变量需要使用大写。
SQL> begin
  2    dbms_scheduler.create_job(
  3      job_name=>'&owner..drop_database_link',
  4      job_type=>'PLSQL_BLOCK',
  5      job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
  6    );
  7    dbms_scheduler.run_job('&owner..drop_database_link',false);
  8    dbms_lock.sleep(2);
  9    dbms_scheduler.drop_job('&owner..drop_database_link');
 10  end;
 11  /
Enter value for owner: LIUTYA
old   3:     job_name=>'&owner..drop_database_link',
new   3:     job_name=>'LIUTYA.drop_database_link',
Enter value for db_link: LECCTEST
old   5:     job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
new   5:     job_action=>'BEGIN execute immediate ''drop database link LECCTEST'';END;'
Enter value for owner: LIUTYA
old   7:   dbms_scheduler.run_job('&owner..drop_database_link',false);
new   7:   dbms_scheduler.run_job('LIUTYA.drop_database_link',false);
Enter value for owner: LIUTYA
old   9:   dbms_scheduler.drop_job('&owner..drop_database_link');
new   9:   dbms_scheduler.drop_job('LIUTYA.drop_database_link');

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links;
no rows selected
测试成功。
重建测试环境
SQL> conn liutya/liutya
Connected.
SQL> 
SQL> create database link lecctest connect to mytest identified by mytest using 'ecctest';
Database link created.
SQL> conn / as sysdba
Connected.
SQL> 
SQL> select * from dba_db_links;
OWNER                DB_LINK              USERNAME             HOST                                                         CREATED
-------------------- -------------------- -------------------- ------------------------------------------------------------ ---------
LIUTYA               LECCTEST             MYTEST               ecctest                                                      12-DEC-13
SQL> show user;
USER is "SYS"
方法二、使用下面的存储过程
Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is
            plsql   varchar2(1000);
            cur     number;
            uid     number;
            rc      number;
    begin
            select
                    u.user_id into uid
           from    dba_users u
           where   u.username = schemaName;
             plsql := 'drop database link "'||dbLink||'"';
             cur := SYS.DBMS_SYS_SQL.open_cursor;
             SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => plsql,
                   language_flag => DBMS_SQL.native,
                   userID => uid
          );
             rc := SYS.DBMS_SYS_SQL.execute(cur);


             SYS.DBMS_SYS_SQL.close_cursor(cur);
   end;
   /
 SQL> Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is
  2              plsql   varchar2(1000);
  3              cur     number;
  4              uid     number;
  5              rc      number;
  6      begin
  7              select
  8                      u.user_id into uid
  9             from    dba_users u
 10             where   u.username = schemaName;
 11               plsql := 'drop database link "'||dbLink||'"';
 12               cur := SYS.DBMS_SYS_SQL.open_cursor;
 13               SYS.DBMS_SYS_SQL.parse_as_user(
 14                     c => cur,
 15                     statement => plsql,
 16                     language_flag => DBMS_SQL.native,
 17                     userID => uid
 18            );
 19               rc := SYS.DBMS_SYS_SQL.execute(cur);
 20  
 21               SYS.DBMS_SYS_SQL.close_cursor(cur);
 22     end;
 23     /

Procedure created.
注意输入参数全部为大写。
SQL> exec drop_dblink('LIUTYA','LECCTEST');
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links;
no rows selected

SQL> drop procedure drop_dblink;  
Procedure dropped.

测试同样成功。

参考资料
Drop database link in another schema
DROP DB_LINKS of a PRIVATE user from “SYS” 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11590946/viewspace-1063080/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11590946/viewspace-1063080/