This discussion is archived
3 Replies Latest reply: Dec 4, 2012 12:45 PM by vlethakula RSS

Validate Private DB links

977887 Newbie
Currently Being Moderated
Hi,

We have a need to write a script to validate the DB links in each database.

I am stuck with, how to validate the private DB links.

I have the privilege to run the script even as SYS or SYSTEM.

Any reply is appreciated.

Thanks,
Aravind.
  • 1. Re: Validate Private DB links
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Select * from v$dblink 
    but to do what you want as i think you need to create procedure , in the code you will use the above view to check the database link and inserted information in another table with date .

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin005.htm
  • 2. Re: Validate Private DB links
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    974884 wrote:

    We have a need to write a script to validate the DB links in each database.
    No scripts please. Use PL/SQL properly. Stored in the database.
    I am stuck with, how to validate the private DB links.
    It can only be validated when executing code as the schema that owns the database link.

    This means
    a) using dynamic SQL with that database link as part of the SQL
    b) parse and execute that SQL cursor as the schema that owns the database link

    The first part is fairly easy. Iterate through DBA_DB_LINKS and build a dynamic SQL statement - something like "+select * from dual@some_db_link+".

    The next part is parsing this as a cursor as the db link schema owner. This needs to be done using DBMS_SYS_SQL package. It is not officially documented as far as I know, but it works exactly as DBMS_SQL, with the addition of being able to specify the schema to parse the cursor as. (this is basically how Apex works - being able to run Apex applications as a specific schema).
  • 3. Re: Validate Private DB links
    vlethakula Expert
    Currently Being Moderated
    check

    http://dineshkumarv.wordpress.com/2012/02/23/using-dbms_sys_sql-to-test-private-db_links/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points