Thursday, June 18, 2015

Testing all Oracle database links for all schema's

I had to validate quite a lot of database links this week, and a lot of different schema's were involved. A database link is deemed valid/usable when the statement "select * from dual@db_link" succeeds, so simply executing this code on every database link would suffice.

However, when connected as one user, you can validate the database links for that user and any public links. Validating the database links for other users doesn't seem to be possible from that single connection. You can't prefix the database link name with the schema, as database links themselves can contain dots. Thus, the schema prefix will be treated as part of the link name, causing the statement to fail (because the link cannot be found under that name).

Then I figured that using a stored procedure created in the owner schema could validate the database link in that schema, when created with definer's rights (the default). And because you can use dynamic SQL ("execute immediate" in this case), you can create the procedure in the other schema, invoke it to test the database link in that same schema, and drop it afterwards.

-- Execute as SYS user (because of ORA-01031)

CREATE OR REPLACE PROCEDURE test_any_db_link ( p_owner IN varchar2
                                             , p_link  IN varchar2
                                             )
AS
   l_owner  varchar2(100);
BEGIN
   IF p_owner = 'PUBLIC' THEN
      l_owner := 'SYSTEM';
   ELSE
      l_owner := p_owner;
   END IF;
   --
   BEGIN
     EXECUTE IMMEDIATE 'create procedure '||l_owner||'.test_any_db_link_temp '||
                       'as '||
                       '  l_dummy varchar2(1); '||
                       'begin '||
                       '  select * into l_dummy from dual@'||p_link||'; '||
                       '  dbms_output.put_line(''OK : '||p_owner||'.'||
                                                         p_link||'''); '||
                       'exception '||
                       ' when others then '||
                       '   dbms_output.put_line(''NOK: '||p_owner||'.'||
                                                          p_link||'''||'' ''||
                                                          sqlcode); '||
                       'end;';
     EXECUTE IMMEDIATE 'begin '||l_owner||'.test_any_db_link_temp; end;';
   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('ERR: '||p_owner||'.'||p_link||' '||sqlcode);
   END;
   EXECUTE IMMEDIATE 'drop procedure '||l_owner||'.test_any_db_link_temp';
END;
/

set serveroutput on size 1000000;

spool test_db_links

BEGIN
  FOR r_stmt IN (select 'begin test_any_db_link('''||owner||''','''||
                                                     db_link||'''); end;' stmt 
                   from dba_db_links 
                  order by owner, db_link
                )
  LOOP
    execute immediate r_stmt.stmt;
  END LOOP;
END;
/

spool off

drop procedure test_any_db_link;

-- done

Main points about this code:
  • You execute it as SYS, because of an ORA-01031 (insufficient privileges) otherwise
  • The script creates a procedure for every test, and drops it afterwards
  • The status of a link is either OK (everything checks out), or ERR (the procedure could not be created, because the link is not valid). In theory, the status could be NOK, when the link is valid, but something else prevents the execution of "select * from dual" from succeeding. So far, I never saw an NOK code
  • This script was used on database versions ranging from 9.2 to 12.1
  • The error output is very short, you could enhance this with dbms_utility.format_error_stack or something similar
  • When a database link is marked as "ERR", investigate further. I have some "ExtProc" call type database links, and those were marked as "ERR", because there is no dual at the other end
  • For PUBLIC links, I translated the user for the procedure to SYSTEM, as you cannot create a procedure under the "schema PUBLIC"

1 comment:

  1. Thanks for share this scripT !!!
    This helped so much to me.

    ReplyDelete