Create a Database Link

No votes yet

In many cases you might need to compare data between tables across different databases. For example your test instance and production instance.

You can easily create a database link between the 2 instances and be able to access the production data from the test database.

CREATE DATABASE LINK <Link Name>
 CONNECT TO <Schema Name>
 IDENTIFIED BY <PWD>
 USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <Host Name>)(PORT = <port#>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <Database Name>)
    )
  )'
;

So if you created this link from Test to connect to the production database, you can now view data in the production instance from Test.

All you need to do is append an @Link_name at the end of the table name

For example

SELECT * FROM <Table_name>@<Link Name>

Thanks,

Zooz

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
3 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.