How can I move a single table's data to another db?

[kwil38]

I'm new to Oracle so please forgive my ignorance...

Is there a way to export the data from a single table of "database A" and then import it into an empty table that is structured the same in "database B"?

I know a little about exporting/importing the whole database, so is there a different syntax I can use to export/import a single table?

Thanks

 

[alexhu]

From the Oracle Utilities Manual

Example Export Sessions in Table Mode
In table mode, you can export table data or the table definitions. (If no rows are exported, the CREATE TABLE statement is placed in the export file, with grants and indexes, if they are specified.)

A user with the EXP_FULL_DATABASE role can use table mode to export tables from any user's schema by specifying TABLES=schemaname.tablename.

If schemaname is not specified, Export defaults to the previous schema name from which an object was exported. If there is not a previous object, Export defaults to the exporter's schema. In the following example, Export defaults to the SYSTEM schema for table a and to scott for table c:

> exp SYSTEM/password TABLES=(a, scott.b, c, mary.d)




Alex

 

[kwil38]

Alex - thanks for the info. I don't have the Oracle Utilities Manual...Is it available online somewhere?

Also, Can I use the same syntax to import? ex. -

> imp SYSTEM/password TABLES=(a, scott.b, c, mary.d)

 

[alexhu]

Docs at

http://tahiti.oracle.com/

Alex

 

[kwil38]

thanks again alex

 

[LKBrwnDBA]

Using DB link do a COPY:

COPY TO <uid2>/<pw2>@DB02
 CREATE MyTable USING Select * From MyTable;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Also you can use the REPLACE option instead of CREATE if the table already exists.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb