Search

Top 60 Oracle Blogs

Recent comments

Don’t forget the COPY command in SQL*Plus (and SQLcl)

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
copy from scott@live create scott.emp_20151201 using select * from scott.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…


Don’t forget the COPY command in SQL*Plus (and SQLcl) was first posted on December 1, 2015 at 9:40 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.