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.
Click Here to Expand Forum to Full Width