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

dev_show_db_links.sql

Check the status of the private and public database links

admin_check_dblinks.sql

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

select
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
begin
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

Query;

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
/

Results:

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

  87728505          2 18-DEC-2019 16:17:35       ROBLA                dbserver1              DBLINK_INFO: (SOURCE_GLOBAL_NAME=TSTDB.example.com, DBLINK_NAME=ROB_LINK.example.com, SOURCE_AUDIT_SESSIONID=4294967295)

Audit database link operations

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