Move Table from One DB to Another with Data

[saw15]

Good day ..

Pretty new to Oracle & working in a development environment.

I need to copy a table (with data) from one development db to another dev db.

Help / example is much appreciated.

Thanks

 

[carp]

Two ways are fairly common:

1. Use the Oracle Export utility to dump the table out of the first database and the Import utility to pull it into the other.

2. Set up a database link between the two databases, then use a CREATE TABLE ... AS ... command to recreate the table in the second database.

If your database link was called db1, then it would look like this:

CREATE TABLE cloned_table AS SELECT * FROM base_table@db1;

Note that method 1 allows you to also transport indexes, triggers, constraints, etc while method #2 only recreates the table structure and then inserts the data. Also, it can generate a lot of network traffic.

 

[rcurva]

You can also use SQPLUS if you can access both databases in the server.

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db> : database string, e.g., scott/tiger@d:chicago-mktg
<opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL SELECT statement

For example, from sqlplus:
COPY FROM scott/tiger@source_db CREATE new_table USING select * FROM source_table;
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[Triton46]

You could also write the first table out to a flat file on the server. Then use sqlldr to insert the file into the second table.