Oracle Administration Manage Database Links

From Robs_Wiki
Revision as of 09:38, 11 February 2020 by Qadmin wiki (talk | contribs) (View current database links)
Jump to: navigation, search

View current database links


Check the status of the private and public database links


Create a database link

create database link

Drop a database link

Note that a private database link can only dropped by the user itself, not even by sys:

SQL> @show_db_links

OWNER                DB_LINK                        USERNAME             HOST
-------------------- ------------------------------ -------------------- ------------------------------
SCOTT                PROD.EBS.UNIVERSE              TIGER                MARS

SQL> drop database link PROD.EBS.UNIVERSE;
drop database link PROD.EBS.UNIVERSE
ERROR at line 1:
ORA-02024: database link not found

So a way around this is to become the user via a proxy:

SQL> alter user scott grant connect through robla;
SQL> conn robla[scott]
SQL> drop database link PROD.EBS.UNIVERSE;

Another method would be to create a procedure to do the job:

SQL> create procedure scott.weg_ermee as
execute immediate 'drop database link PROD.EBS.UNIVERSE';
end weg_ermee;

SQL> exec scott.weg_ermee;
PL/SQL procedure succesfully completed

Change a database link password

SQL> grant alter database link to scott;

SQL> connect robla[scott]

SQL> alter database link SCOTT.TIGER.ORG connect to scotty identified by manager;

Identify database link connections


col userid format a20
--col ntimstamp# format a20
col userhost format a32
col comment$text format a60
set linesize 200
select * from 
(select sessionid, statement, to_char(ntimestamp#,'DD-MON-YYYY HH24:MI:SS') ntimestamp#, userid, userhost, comment$text
from sys.aud$ where comment$text like '%DBLINK%' order by ntimestamp# desc)
where rownum < 11


 SESSIONID  STATEMENT NTIMESTAMP#                USERID               USERHOST                         COMMENT$TEXT
---------- ---------- -------------------------- -------------------- -------------------------------- ------------------------------------------------------------
  87747563          2 19-DEC-2019 07:49:33       ROBLA                dbserver1              DBLINK_INFO: (,, SOURCE_AUDIT_SESSIONID=4294967295)

  87728505          2 18-DEC-2019 16:17:35       ROBLA                dbserver1              DBLINK_INFO: (,, SOURCE_AUDIT_SESSIONID=4294967295)

Audit database link operations

Oracle 12c provides a DBLINK_INFO column in SYS.UNIFIED_AUDIT_TRAIL.