How do I use a dblink?

Question

I'm a user on two databases, database1 and database2.

In database1, there is a public dblink

OWNER: PUBLIC
DB_LINK: DBLINKNAME
USERNAME: CURRENT_USER
HOST: DATABASE2
CREATED:...

What is the syntax for using this dblink to do a select on schema.table on database2?

Answer 1

To select from a database across the dblink, you would simply reference the database with an @ symbol:

select *
from table1@DBLINKNAME

Best practice I have seen is to provide a synonym for any table you want to use though so that the name of the DBLINK does not become part of any application code or multiple other database objects. If you have a view, then you can query just as if it were a local database object:

create view table1_vw as
select * from table1@DBLINKNAME;

select *
from table1_vw