Manage Oracle Database Links
Always test these 'manage oracle database links' commands on a test server
before implementing them on a production server.
before implementing them on a production server.
Know your open links
Once you have created and made use of a database link in Oracle,
you might want to keep an eye on the number of concurrent open database links
in your database so you can tune the open_links initialization parameter.
Read more here on limiting concurrent open links.
You will need to query v$dblink to see how many links are open in your session:
SQL> select in_transaction, count(*) from v$dblink
group by in_transaction;
IN_ COUNT(*)
--- ----------
YES 1
Here are some interesting columns and descriptions of v$dblink:
db_link Db link name
owner_id Owner name
logged_on Is the database link currently logged on?
protocol Dblink's communications protocol
open_cursors Are there any cursors open for the db link ?
in_transaction Is the db link part of a transaction which
has not been commited or rolled back yet ?
update_sent Was there an update on the db link ?
dba_db_links
To gather information on all database links in your database, query dba_db_links.
You will need dba privileges to see this view, the alternatives are user_db_links
and all_db_links.
A user who does not have dba privileges can query all_db_links to see
which db links are available to him.
SQL> select * from dba_db_links; OWNER DB_LINK HOST CREATED ---------- ---------- ---------- ---------- SCOTT REMOTEDB remotedb 23-feb-2008 23:59:05
Some interesting columns:
owner User who owns the db link,
will state 'PUBLIC' if it is a public database link.
db_link Db link name.
username The username that was specified if it was hardcoded
during the create statement, null if not specified
during the create statement.
host The tnsnames alias specified during the create
statement.
created Date and time of link creation.
Click here to read about troubleshooting database link errors.
