3 Replies Latest reply: Dec 4, 2012 2:45 PM by vlethakula RSS

    Validate Private DB links

    977887
      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
          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
            Billy~Verreynne
            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
              check

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