Ask Question
Help Others
Home › Blogs › Zooz's blog

Create a Database Link

Thu, 07/03/2008 - 12:26pm — Zooz
Your rating: None Average: 2 (1 vote)

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

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
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!