[Oracle] SYS 계정에서 일반 계정 DB LINK 삭제 방법

 

SYS계정에서 일반 계정에 생성된 Private DB LINK 삭제 방법

 

일반 계정에서 생성된 Private Database Link에 대해서는 일반 Drop database link 명령문으로는 다른 계정에서 삭제할 수 없습니다. 

 

SYS계정에서 SML 계정의 PRIVATE DB LINK - SML_LINK 삭제 시도

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 29 16:11:44 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set line 200
set pages 300
col db_link for a20
col host for a20
select * from dba_db_links;

OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ------------------
SYS                            ORCL11_LINK          REMOTE_USER                    ORCL11               24-FEB-25
SYS                            ORA11EE_LINK         REMOTE_USER                    ORA11EE              24-FEB-25
PUBLIC                         ORCL_SYS             SYSTEM                         ORCL19_KEJ           14-SEP-23
SML                            SML_LINK             TRUIN                          DBLINKTEST           24-FEB-25

4 rows selected.

SQL> drop database link SML_LINK;

drop database link SML_LINK
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> drop database link SML.SML_LINK;
drop database link SML.SML_LINK
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL>

 

 

부득이하게 해당 계정으로 접속하여 DB LINK 삭제가 어려울 시, 아래와 같은 방법으로 SYS 계정에서 일반계정들의 DB LINK를 삭제할 수 있습니다.

 

SYS 계정에서 프로시저 생성 후 일반 계정 DBLINK 삭제

-- DB LINK 삭제 프로시저 생성
SQL> 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;
   /
   
   Procedure created.
   
-- drop_dblink('SCHEMA NAME','DBLINK NAME') 형식으로 삭제할 DBLINK 입력   
SQL> exec drop_dbLink( 'SML', 'SML_LINK' );
   
PL/SQL procedure successfully completed.

-- 삭제 완료
SQL> select * from dba_db_links;

OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ------------------
SYS                            ORCL11_LINK          REMOTE_USER                    ORCL11               24-FEB-25
SYS                            ORA11EE_LINK         REMOTE_USER                    ORA11EE              24-FEB-25
PUBLIC                         ORCL_SYS             SYSTEM                         ORCL19_KEJ           14-SEP-23

3 rows selected.

SQL>

 

작업 완료 후, 해당 프로시저는 삭제해주셔도 됩니다.

SQL> drop procedure drop_dblink;

Procedure dropped.