Create Oracle Database Link
before implementing them on a production server.
Syntax :
CREATE {PUBLIC} DATABASE LINK <database link name>
{CONNECT TO <oracle user id>
IDENTIFIED BY <remote oracle user's password> }
USING ' <dbstring> ';
Privileges needed
When creating an oracle database link, you can decide to create it privately,
for use only by the owner of the database link,
or publicly, for use by all other database users who have the right privileges.
If you do not specify the word 'PUBLIC' in the create statement, the database
link will be created as a private one by default.
Once a public database link has been created, it will be owned by a special
user named 'PUBLIC'.
The second decision is to create it with a hard-coded username and password,
or without access credentials.
If you create a public database link without access credentials, any user
accessing it will do so with his own username and password.
Take care when creating a public dblink with a hardcoded username and
password, it is a security risk.
Creation
Now that you are ready to create it, you must provide a database link
name
(this is what your db link will be called).
One common problem with dblinks is that Oracle will not allow you to
create a db link whose name is different from the global name
of the remote database if the local database's 'GLOBAL_NAMES' initialization
parameter is set to 'TRUE'.
Lastly, you must specify a valid tnsnames alias which Oracle will use
to connect to the remote database.
To test if your tnsnames.ora alias is valid, try to access it through
sql*plus on your local server.
To be able to create a database link, you will need to have the system
privilege
'CREATE DATABASE LINK'
or
'CREATE PUBLIC DATABASE LINK'
granted to you.
The remote user will need to have at least 'CREATE SESSION' privileges.
Examples:
Let's create a private database link using a hard-coded username and password.
SQL>Create database link remotedb connect to scott identified by tiger
using 'remotedb.world.com';
Database link created.
The same db link without a username/password:
SQL>create database link remotedb using 'remotedb.world.com';
Database link created.
Here's an example to create a public database link with a username and
password:
SQL> create public database link remotedb connect to scott identified
by tiger using 'remotedb.world.com';
Database link created.
Here's a public database link without a username and password.
SQL>create public database link remotedb using 'remotedb.world.com';
Database link created.
Next: How to 'use' a database link.
Click here to read about troubleshooting database link errors.
