Solved

Oracle -- copy table from one db to another db ?

Posted on 2011-02-21
4
1,449 Views
Last Modified: 2013-12-19
I have two databases (test & dev) on the same machine (server1) and tried running the below in Oracle SQL Developer, but get errors.

How can i easily copy the test database accounting_structure_tab table into dev ?

truncate DEV.ACCOUNTING_STRUCTURE_TAB
INSERT INTO DEV.ACCOUNTING_STRUCTURE_TAB
SELECT *
from TEST.ACCOUNTING_STRUCTURE_TAB
0
Comment
Question by:finance_teacher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34944508
create a database link in DEV pointing to TEST


then....  (you need "truncate table"  not "truncate")

truncate table ACCOUNTING_STRUCTURE_TAB;



INSERT INTO ACCOUNTING_STRUCTURE_TAB
SELECT *
from ACCOUNTING_STRUCTURE_TAB@your_test_db_link;

I assume the table structure is the same in both dev and test?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34944519
Do you have a DBLInk? if you do not create one.

INSERT INTO DEV.ACCOUNTING_STRUCTURE_TAB
SELECT *
from TEST.ACCOUNTING_STRUCTURE_TAB@dblink
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34944527
if you want to do this from within a pl/sql procedure or anonymous block,  you must wrap the truncate inside an execute immediate  because trucate is ddl which isn't part of pl/sql syntax


begin

execute immediate 'truncate table ACCOUNTING_STRUCTURE_TAB';

INSERT INTO ACCOUNTING_STRUCTURE_TAB
SELECT *
from ACCOUNTING_STRUCTURE_TAB@your_test_db_link;

end;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34944846
There is also a sql COPY command.

The docs have the syntax.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 recover a database from a user managed backup

752 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