Create copy of Oracle database table to another Oracle database using SQL

Question

I am wondering if there is a simple way to copy either an entire Oracle database table or data rows into another Oracle database table (different databases). Is there some type of way to do this using SQL language? My initial thought is to use a SQL query like this:

INSERT INTO outputTable
SELECT * FROM username/[email protected]

If not with SQL, is there another way to do this? I am trying to automate this task so performing a copy using SQL Developer or something along those lines will not suffice unfortunately. I am trying to do this using Python.

You would use Oracle's export/import tools for that. You can google it for more info. — sstan
– sstan, Commented Jun 1, 2016 at 21:31
Just export and import a table using exp/inp => docs.oracle.com/cd/B28359_01/server.111/b28319/exp_imp.htm — krokodilko
– krokodilko, Commented Jun 1, 2016 at 21:50

Jon Heller · Accepted Answer · 2016-06-02 00:52:44Z

3

You can create a DBLink to connect the source and the target DB, then simply use SQL to save your data in the linked DB.

Something like:

create database link "TARGET"
connect to YOUR_SCHEMA
identified by YOUR_PASSWORD
using YOUR_CONNECTION_STRING;

Once you have a DBLink, you can use plain SQL, no matter the involved tables are on different DB:

INSERT INTO YOUR_TABLE@TARGET SELECT * FROM YOUR_TABLE

edited Jun 2, 2016 at 0:52

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

answered Jun 1, 2016 at 21:31

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kayla210 · Accepted Answer · 2016-06-01 21:41:26Z

1

Try creating a database link: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm http://psoug.org/definition/create_database_link.htm

You'll only need to create the database link once, unless the credentials you need to connect with change at any point.

CREATE DATABASE LINK [Database Link Name] 
CONNECT TO [Username] IDENTIFIED BY [Password]
USING '[Service Name]';

Once the link is created, you can do something like this to retrieve data from a separate database and do whatever you want with it:

SELECT * FROM inputTable@[Database Link Name Here]

In this case:

SELECT * INTO outputTable
FROM inputTable@databaseLink

edited Jun 1, 2016 at 21:41

answered Jun 1, 2016 at 21:36

kayla210

1068 bronze badges

2 Comments

EvanL Over a year ago

Is there a way to do this without creating a database link and just adding @username/password@instance instead of @databaseLink?

kayla210 Over a year ago

As far as I know, no. This was given to me as a solution to the exact same problem you're having by a DBA several months back. It worked fairly well for my purposes.

chungtinhlakho · Accepted Answer · 2016-06-01 21:37:47Z

0

have try openQuery? in sql server, you can link to an oracle database, after that you can just you it when Openquery

example

Select * into T_NewTable from openquery(connectionanme, 'select * from your source table')

answered Jun 1, 2016 at 21:37

chungtinhlakho

93010 silver badges21 bronze badges

Collectives™ on Stack Overflow

Create copy of Oracle database table to another Oracle database using SQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related