Solved

Connecting for single sql

Posted on 2014-01-26
2
247 Views
Last Modified: 2014-02-05
I just have the idea like below. Please let me know if this is feasible.
insert into et_hn_nomns
connect opctjad_dba/opctjad_dba@dprdt;
select * from et_hn_nomns


I want to insert into a table in one database by selecting from a table in another database.

Can this be accomplished by any way, with out using DBLink.
0
Comment
Question by:sakthikumar
2 Comments
 
LVL 12

Accepted Solution

by:
praveencpk earned 500 total points
ID: 39809906
Yes it can be done through copy command but it is deprecated as of Oracle 10g , here is the syntax for using the same.

COPY FROM HR@BOSTONDB -
TO HR@BOSTONDB -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

http://docs.oracle.com/cd/B28359_01/server.111/b31189/apb.htm#i641251

You can use data pump export for exporting the table data from source Database and importing it to target database.

here is the reference doc for expdp/impdp
http://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD52674
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39810820
If you can't use the copy command for any other reason like db links, then may be you can try expdp and impdp to export/import the data into the required tables.
0

Featured Post

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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

13 Experts available now in Live!

Get 1:1 Help Now