Tag: sqlplus

One of the developers asked me to copy a small table from Live to Dev. In situations like this, my first thought is to use the SQL*Plus COPY command. By the way, this command is also available in SQLcl.

It’s super-easy and has been around forever. Provided you can live with the data type restrictions, it’s a lot less hassle than expdp/impdp, even with the NETWORK_LINK option.

As always, it’s in the documentation, but the SQL*Plus help text tells you how to use it, so you don’t even have to RTFM is you don’t want to. 🙂

SQL> help copy

COPY
----

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query

where database has the following syntax:
username[/password]@connect_identifier
SQL>

What I ended up doing was something like this.

conn scott@dev

-- Using a TNS alias.
copy from scott@live create emp_20151201 using select * from emp;

-- Or using the EZ connect syntax.
copy from scott@my-host:1521/orcl create emp_20151201 using select * from emp;

Because it’s been around for so long, you rarely see people talk about it, which made me wonder how many newer folks may not have noticed it, hence this blog post. 🙂

Cheers

Tim…

PS. Here is the usage for the COPY command from SQLcl.

SQL> help copy
COPY
----

Non sqlplus additions are explicit BYTE options (default is CHAR
i.e. varchar2(1000 CHAR)) and LOB support.

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER, LOB and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE|APPEND_BYTE|CREATE_BYTE|REPLACE_BYTE}
destination_table
[(column, column, column, ...)] USING query

where database has the following syntax:
username[/password]@connect_identifier
SQL>