Difference between revisions of "Oracle Administration Manage Database Links"

From Robs_Wiki
Jump to: navigation, search
(Identify database link connections)
(View current database links)
Line 3: Line 3:
 
== View current database links ==
 
== View current database links ==
 
[[show_db_links.sql|dev_show_db_links.sql]]<br />
 
[[show_db_links.sql|dev_show_db_links.sql]]<br />
 +
== Check the status of the private and public database links ==
 +
[[admin_check_dblinks.sql|admin_check_dblinks.sql]]
  
 
== Create a database link ==
 
== Create a database link ==

Revision as of 09:38, 11 February 2020

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.