jampez77 jampez77 - 5 months ago 70
SQL Question

how can i copy table and data from one database to another using oracle

I'm using Oracle PL/SQL developer. I have two databases live and dummy.

I have been using the dummy database for development and now i need to add all the new tables and data into the live database without effecting the data currently stored in it.

I also need to copy over all the new sequences and triggers.

I've tried exporting a table but had no luck and using the SQL view i only get the SQL for the creation of the data and not the data it contains.

I also found this SQL code

COPY FROM test@DUMMY - CREATE test -USING SELECT * FROM test;


But it asks me for a name and password of which I dont know and then fails after 3 attempts. It then goes on to say there is a syntax error as well.

I'm still fairly new to using Oracle and any help would be appreciated.

Answer

You can do that using datapump.

or if you want to copy from schema to schema

create table shecma2.old_table 
as 
select * from schema1.old_table

or using oracle sql developer - here is link.