Use copy command to copy tables on local or remote database.

Generally we use CREATE TABLE tab1 AS SELECT..... to copy a table's data to some other table.

There are some limitations with this method.

1) Oracle doesn't commit in between while copying data, so undo segment may run out of space.

2) This technique cannot be used to copy LONG type columns in a table.

Let's see how to use copy command to get rid of these problems.

Suppose we want to create a new table tab_new with data of table tab_old. In this case copy command will be used as

COPY FROM username/password@db_name
CREATE tab_new
USING SELECT * FROM tab_old;

This will create a new table tab_new with data of table tab_old.

Some other options which can be used in place of CREATE are

INSERT : Inserts records in an existing table.

APPEND : Creates the target table, if it doesn't exists, and inserts records.

REPLACE : Drops the existing table, creates new table and inserts records.

For more info visit.