DATA COPY USING SQL*PLUS COPY COMMAND

“Don’t procrastinate, Do it now

Most of the Oracle dba’s would probably might not be using sqlplus copy command which is a very efficient way to copy small table data and columns from one database to another.

Copy usage:

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

we can set the following functions:

set copycommit 0 (number of copy batches after completion the changes are commited)

set arraysize 15(number of rows in batch)

1)COPY TABLE USING SQLPLUS:

copy from system/password@db1-

replace test1.tbl0101-

using select * from test.tbl0101

2)COPY COLUMNS FROM ONE TABLE TO ANOTHER TABLE:

copy from test1@db1-

to test2@db2 -

create tab2 (emp_id,emp_name,salary) -

using select * from tab1

3)COPY TABLE TO REMOTE DATABASE (add the tns entries of databases in tnsnames.ora)

copy from test1@db1 -

create tab2 -

using select * from tab1

Note: If you copy big size table,then increase your undo tablespace size and undo retention accordingly and monitor for archivelog and tracefile generations.If you have less space in mountpoint then you might end up filling the space fast.

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: