Solved

Oracle -- Database LINK -- on two different servers

Posted on 2011-02-21
8
800 Views
Last Modified: 2013-12-19
Doing the below works when i have two databases (test & dev) on the same machine (server1)

How can I do the same logic, but on different machines ?

Example = dev is on server1 and test is on server2

create public database link
  dev123     -- user defined, can be anything
connect to
  hr            -- DEV's user
identified by
  .....            -- DEV's password
using 'dev'; -- DEV's SID

create public database link
  test123     -- user defined, can be anything
connect to
  hr            -- test's user
identified by
  .....            -- test's password
using 'test'; -- test's SID

insert into accounting_structure_tab@dev123
select *
from accounting_structure_tab@test123;
0
Comment
Question by:finance_teacher
8 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34945497
you do it the exact same way


the only "trick" is you need to make sure your tnsnames.ora (or ldap if using that) is configured on DEV so your DEV db can find your TEST db on the other server.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34945510
also, you don't need (or want) 2 database links.


you only want one link, from the target system linking to the source system.

In your case, you want one link on dev that connects to test.
0
 
LVL 10

Expert Comment

by:Bawer
ID: 34945532
try this format

Select * from [ServerName].[DatabaseName].[Scheme].[TableName]
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34945560
Bawer,

that's sqlserver syntax,  doesn't apply here
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:sventhan
ID: 34945702
If you do not want to use DB link, you could use SQL Plus copy command. Its over the wire and does not require a DB link.

http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/apb.htm
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34945719
dblink is more efficient, it allows direct insert from source to target

COPY will copy all of the data locally to you client  from the source then do inserts from your client to the target

if the table is small you might not notice the lag but if it's large, dblink is definitely the way to go
Note, you can't copy  LONG type across the dblink but COPY will allow it.  Hopefully you aren't using LONG types though, they were deprecated years ago
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34945734

COPY FROM username/your_password@db-
append accounting_structure_tab-
USING select * -
from accounting_structure_tab
0
 
LVL 3

Expert Comment

by:gopisera
ID: 34948471
Here is the syntax for your question.

create database link name connect to <username> identified by <password of user> using 'tnsentry';
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now