If one database link is invalid, you cannot select through it
SQL> select * from dual@z;
select * from dual@z
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.
create function status(db_link varchar2)
return varchar2 is
x number;
begin
execute immediate 'select 1 from dual@"'||
DB_LINK||
'"' into x;
return 'OK';
exception
when others then return sqlerrm;
end;
/
select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK
I created a similar function (actually, an ASSERT_USABLE_DB_LINK procedure) but I try the EXECUTE IMMEDIATE query a second time if I get an initial error the first time. If the DB link connection was made and then connectivity to the remote DB is lost and then restored, the first error will dump the broken DB link connection and the second EXECUTE IMMEDIATE query will succeed and create a new, usable DB link connection. If I get an error the second time, the DB link is really invalid and the error is thrown.
I also run following to see where the link points to … “select ‘DB Link XXXX now points to ‘ || property_value from database_properties@XXXX where property_name in (‘GLOBAL_DB_NAME’);”
@raj why not simply select * from global_name@xxx ?
Pingback: check invalid database link for the DBA – Laurent Schneider
@Laurent, yes that is a good too, originally I used it to find DB version as well (until we upgraded all to same level), so it is an old relic. the one you suggested seems simpler to code too, thanks.