Admin check dblinks.sql

From Robs_Wiki
Revision as of 09:36, 11 February 2020 by Qadmin wiki (talk | contribs) (Created page with "This scripts lists the database links and checks the status of the links. <br /><br /> Sample output: <pre> *** list of available database links *** OWNER DB_LINK...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This scripts lists the database links and checks the status of the links.

Sample output:

*** list of available database links ***

OWNER           DB_LINK              HOST
--------------- -------------------- ------------------------------
PUBLIC          LN_MISCTST.MY.COMP   MISCTST
PUBLIC          ROB_APPDEV.MY.COMP  APPDEV
PUBLIC          GG_ERROR.MY.COMP     PRDDB
ROBLA           ROB_APPUAT.MY.COMP  APPUAT
SYS             ROBLA.GG_PROD        PRDDB
SYS             ROB_LINK.MY.COMP     APPUAT2

6 rows selected.

*** status of the private database links ***

OWNER   DATABASE LINK   STATUS
-----------------------------
ROBLA ROB_APPUAT.MY.COMP VALID
SYS ROBLA.GG_PROD INVALID (ORA-4052)
SYS ROB_LINK.MY.COMP VALID

PL/SQL procedure successfully completed.

*** status of the public database links ***

OWNER   DATABASE LINK   STATUS
-----------------------------
PUBLIC GG_ERROR.MY.COMP INVALID (ORA-4052)
PUBLIC LN_MISCTST.MY.COMP INVALID (ORA-4052)
SYSTEM ROB_APPDEV.MY.COMP VALID

PL/SQL procedure successfully completed.

Script:

SET SERVEROUT ON 

prompt *** list of available database links ***
col owner format a15
col db_link format a20
col host format a30
select owner, db_link, host from dba_db_links order by 1;

prompt *** status of the private database links ***
prompt
prompt OWNER   DATABASE LINK   STATUS
prompt ------------------------------ 

BEGIN
        FOR f IN (  SELECT *
                      FROM dba_db_links
                  WHERE OWNER <> 'PUBLIC'
                  ORDER BY owner, db_link)
        LOOP
            DBMS_SCHEDULER.create_job (
                job_name     => f.owner || '.CHECK_DBLINK_PRIVATE',
                job_type     => 'PLSQL_BLOCK',
                job_action   =>    'DECLARE '
                               || '  X CHAR; '
                               || 'BEGIN '
                               || '  SELECT dummy into x from dual@'
                               || f.db_link
                               || '  ;'
                               || '  DBMS_OUTPUT.put_line('''
                               || f.owner
                               || ' '
                               || f.db_link
                               || ' VALID'');'
                               || 'END ; ');
 
           BEGIN
               DBMS_SCHEDULER.run_job (f.owner || '.CHECK_DBLINK_PRIVATE ', TRUE);
           EXCEPTION
               WHEN OTHERS
               THEN
                   DBMS_OUTPUT.put_line (
                          f.owner
                       || ' '
                       || f.db_link
                       || ' INVALID (ORA'
                       || SQLCODE
                       || ')');
           END;
           DBMS_SCHEDULER.drop_job (f.owner || ' . CHECK_DBLINK_PRIVATE ');
       END LOOP;
   END;
   /

prompt *** status of the public database links ***
prompt
prompt OWNER   DATABASE LINK   STATUS
prompt ------------------------------ 

BEGIN
        FOR f IN (  SELECT *
                      FROM dba_db_links
                  WHERE OWNER = 'PUBLIC'
                  ORDER BY db_link)
        LOOP
            DBMS_SCHEDULER.create_job (
                job_name     => 'system.CHECK_DBLINK_PUBLIC',
                job_type     => 'PLSQL_BLOCK',
                job_action   =>    'DECLARE '
                               || '  X CHAR; '
                               || 'BEGIN '
                               || '  SELECT dummy into x from dual@'
                               || f.db_link
                               || '  ;'
                               || '  DBMS_OUTPUT.put_line('''
                               || 'SYSTEM'
                               || ' '
                               || f.db_link
                               || ' VALID'');'
                               || 'END ; ');
 
           BEGIN
               DBMS_SCHEDULER.run_job ( 'system.CHECK_DBLINK_PUBLIC ', TRUE);
           EXCEPTION
               WHEN OTHERS
               THEN
                   DBMS_OUTPUT.put_line (
                          'PUBLIC'
                       || ' '
                       || f.db_link
                       || ' INVALID (ORA'
                       || SQLCODE
                       || ')');
           END;
           DBMS_SCHEDULER.drop_job ('system.CHECK_DBLINK_PUBLIC ');
       END LOOP;
   END;
   /