Ask a judgment statement.

云起魔崖

There is a backup database B, which will not be open to us until the backup is complete, but sometimes it will not open for that day.

I have a database A, and a DBLINK to database B is built on A.

Run an application on A, and use the data of B, such as:
select * from all_tables @ B

How can I tell if database B is already available, I tried using an exception, as follows:
declare
aa number (10);
begin
select count (*) into aa from all_tables @ B where rownum <2;
exception
when others then
dbms_output.put_line ('Database is not ready');
end;

But it failed to catch, and the error reported was ORA-00942: The table or view does not exist.
I wonder if there is any other way? consult.

whbilly

First determine whether db_link is working?

select sysdate from dual @ B;
If there is a return value, the explanation is passed. Otherwise, you need to see what the problem is, it may be a network problem, it may be that tnsnames.ora is not configured well

If there is a return value, you need to create a public synonym for the table in the remote database that you want to access.

Such as:
create publis synonym tabl_name for sys.tabl_name@B;

whbilly

But cannot catch

It is because your exception does not meet the conditions of the error.
Your exception should be insufficient permissions

saoshe

As said upstairs, the first thing is to see if you have any problems with <@ 连接 名> this way, including permissions; (you can see the access using B.all_tables) If there is no problem, from user_tables See if the table exists, and then compare the number of records.

yuanin

dblink does not catch exceptions when it is not easy to use
I use the returned 'ORA-00942: table or view does not exist' to determine whether the database is ready to connect (provided there must be a connected table)