I wanted to pass along an Oracle tip I discovered, and have passed along to
When I was creating a database link the other day, I could create the link
ok but could not get it to work. After exhausting all research thru the
Oracle manuals, I found an article on Oracle's Metalink that explained what
I needed to do. This tip is undocumented in the Oracle manuals.
The following is pasted from a Word document I put together...ENJOY !
Found this in a Metalink article (DocID=1024124.6)
This is not documented in the Oracle manuals and can be useful (even required) when the init.ora parameter GLOBAL_NAMES is set to TRUE.....
Note that if GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire.
Database links are created using the following syntax:
SQL> create database link
connect to identified by
The name of the database link should match the global name of the target
database if GLOBAL_NAMES=TRUE. This may seem restricting since then there can
be only one database link per schema to a given database if global_names is
set to true. To overcome this use database link qualifiers. For example:
SQL> create database link oradb@link1
NOTE: in this example, 'link1' is database link qualifier
and 'D:BOSTON-MFG' is the connect string
My NOTE : your select to the remote database would look like this :
select * from table_name@oradb@link1;
You are right that the documentation of Oracle about DB links is messy and incomplete (for example DB links in OPS). I usually set GLOBAL_NAMES to false. That way you might have some problems with advanced replications, etc. but you don't have to use DB link qualifiers.