Oracle SQLPlus Copy Command Usage Syntax to Copy tables across database

advertisements

_____________________________________________________________________________________________________________________

SQL*Plus copy command is the easiest method to copy tables from one database to another database. You must configure the proper tns entries to the tnsnames.ora file before copying the table as it uses the tns for the data transfer.

Using copy command you can copy tables from local to remote or remote to remote using proper tns entries

Copy command support only CHAR, DATE, LONG, NUMBER andVARCHAR2 data types. It does not support any new data types.

Here is the copy command help.

SQL> copy help=y

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

  <db>   : database string,

  <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

A missing FROM or TO clause uses the current SQL*Plus connection.

Syntax:

COPY {FROM username[/password]@soucedb| TO username[/password]@targetdb| FROM username[/password]@sourcedb TO username[/password]@targetdb} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column ...)] USING query

Example:

SQL> create table copy_example (col1 varchar2(10), col2 long);

Table created.

SQL> desc copy_example

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 COL1                                                                       VARCHAR2(10)

 COL2                                                                       LONG

SQL>  copy from scott/scott@prod9 to  scott/scott@prod8 create example_copy using select * from copy_example;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table EXAMPLE_COPY created.

   0 rows selected from scott@prod9.

   0 rows inserted into EXAMPLE_COPY.

   0 rows committed into EXAMPLE_COPY at scott@prod8.

SQL> copy from scott/scott@prod9 to scott/scott@prod8 create copyexamp using select * from EXAMPLE;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table COPYEXAMP created.

   95120 rows selected from scott@prod9.

   95120 rows inserted into COPYEXAMP.

   95120 rows committed into COPYEXAMP at scott@prod8.

_____________________________________________________________________________________________________________________

Website Stats