How to test any database link for other schema or the whole database?

Even if you have "create any" and "select any" privileges, you still can't directly test or create database links owned by other schemas.
  

SQL> select * from dual@SCHEMA.DBLINK;
select * from dual@SCHEMA.DBLINK
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

But, there are ways to get around it. 

Method 1: 

By creating an object within the schema like this:

  

SQL> create view SCHEMA.for_dblink_test_only as select * from dual@SCHEMA.DBLINK;

View created. 

If the database link doesn't work, we'll get the error message, in this example, the database link was pointing to the "old" server:
  

SQL> create view SCHEMA.for_dblink_test_only as select * from dual@SCHEMA.DBLINK_NOWORKING;

ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor 

So, we can generate a script to test all database links:

select 'CREATE VIEW '||owner||'.for_dblink_test_only as select * from dual@'||db_link||';',
'DROP VIEW '||owner||'.for_dblink_test_only;'
from dba_db_links where username is not null;

Or even better, build a procedure to do so.

Method 2: 

If you can get execute privilege on sys.dbms_sys_sql, you can also do it without creating any object:

create procedure execute_sql( p_cmd varchar2, p_user varchar2 )
authid current_user
is
    l_user_id number;
    l_cursor  number;
    l_result  number;
begin
    -- fetch userid
    select user_id into l_user_id
      from dba_users
     where username = p_user;
    -- parse and execute
    l_cursor := sys.dbms_sys_sql.open_cursor;
    sys.dbms_sys_sql.parse_as_user( l_cursor, p_cmd, dbms_sql.native, l_user_id, true );
    l_result := sys.dbms_sys_sql.execute( l_cursor );
    sys.dbms_sys_sql.close_cursor( l_cursor );
end;
/
 

If the database link doesn't work, you'll get:



execute execute_sql('select * from dual@db_link','SCHEMA');


ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1306
ORA-06512: at "SYS.EXECUTE_SQL", line 14
ORA-06512: at line 1

If the database link works, you'll get:

execute execute_sql('select * from dual@db_link','SCHEMA');


PL/SQL procedure successfully completed.