Copy data from one database (SID) to another database

Here is how to use the COPY command to copy data from one database (SID) to another database. It is often very difficult to copy all tables with data from one SID to another or append all data of one SID to another table of another SID. This can be used, for example, when moving data from a production environment to a test environment, or vice versa. The following command will help.

COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>

<db>   : database string, e.g., scott/[email protected]: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

This SELECT query will copy data from one SID to another.

Reader Feedback

Alex V. writes: Up to Oracle8i, the COPY command does not support the LOB data type as well as customer types in table definitions.

For More Information

• What do you think about this tip? E-mail the editor at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.