Skip to Main Content
  • Questions

  • As a DBA how to verify if private database link is functional

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saurabh.

Asked: August 20, 2016 - 8:23 am UTC

Last updated: August 25, 2016 - 2:49 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

Being a DBA, how can we verify if private database link created in application schema is working fine? If application schema's password is not known to DBA.

We can verify public database link using select * from dual@public_db_link; How private db links can be verified by a DBA if application schema's password is not known.

Regards,
Saurabh

and Connor said...

Check this oracle magazine article

http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23asktom-1906478.html

It shows you how to connect as another user without having their password. You can then connect as the owner of the private link, and run the same 'select from dual' you would normally run.

Hope this helps.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Saurabh Shukla, August 21, 2016 - 2:31 pm UTC

It helped

current_schema

Toon Koppelaars, August 21, 2016 - 5:57 pm UTC

As a DBA, wouldn't a simple "alter user set current_schema=..;" do the trick also?
Chris Saxon
August 22, 2016 - 3:28 am UTC

I'm not sure that works ?

SQL> conn demo/demo
Connected.

SQL> create database link remotedb connect to demo identified by demo11 using 'db11';

Database link created.

SQL> select count(*) from tab@remotedb;

  COUNT(*)
----------
         1

SQL> conn mydba/mydba
Connected.
SQL> alter session set current_schema = demo;

Session altered.

SQL> select count(*) from tab@remotedb;
select count(*) from tab@remotedb
                         *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn demo/demo
Connected.

SQL> select count(*) from tab@remotedb;

  COUNT(*)
----------
         1


Scheduler?

A reader, August 24, 2016 - 7:58 am UTC

Proxy is nice, but the user cannot be locked and ALTER USER is a bit aggressive.

I tried with the scheduler 

SQL> conn scott/tiger
Connected.
SQL>
SQL> create database link L1.EXAMPLE.COM connect to scott identified by tiger using '&_CONNECT_IDENTIFIER';

Database link created.

SQL>
SQL> create database link L2.EXAMPLE.COM connect to scott identified by wrongpassword using '&_CONNECT_IDENTIFIER';

Database link created.

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> SET SERVEROUT ON 
SQL>
SQL> BEGIN
  2      FOR f IN (  SELECT *
  3                    FROM dba_db_links
  4                ORDER BY owner, db_link)
  5      LOOP
  6          DBMS_SCHEDULER.create_job (
  7              job_name     => f.owner || '.DBLINK',
  8              job_type     => 'PLSQL_BLOCK',
  9              job_action   =>    'DECLARE '
 10                              || '  X CHAR; '
 11                              || 'BEGIN '
 12                              || '  SELECT dummy into x from dual@'
 13                              || f.db_link
 14                              || '  ;'
 15                              || '  DBMS_OUTPUT.put_line('''
 16                              || f.owner
 17                              || ' '
 18                              || f.db_link
 19                              || ' VALID'');'
 20                              || 'END ; ');
 21
 22          BEGIN
 23              DBMS_SCHEDULER.run_job (f.owner || '.DBLINK ', TRUE);
 24          EXCEPTION
 25              WHEN OTHERS
 26              THEN
 27                  DBMS_OUTPUT.put_line (
 28                         f.owner
 29                      || ' '
 30                      || f.db_link
 31                      || ' INVALID (ORA'
 32                      || SQLCODE
 33                      || ')');
 34          END;
 35          DBMS_SCHEDULER.drop_job (f.owner || ' . DBLINK ');
 36      END LOOP;
 37  END;
 38  /
SCOTT L1.EXAMPLE.COM VALID
SCOTT L2.EXAMPLE.COM INVALID (ORA-4052)

PL/SQL procedure successfully completed.

SQL>

Connor McDonald
August 25, 2016 - 2:49 am UTC

nice example

Laurent Schneider, August 24, 2016 - 8:00 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.