Oracle Tip – COPY Command – When You Don’t Have DataPump

This is an old post that I am re-cycling. The recommended method to copy data around is by using DataPump. However, if you are on an older version of Oracle, COPY command is an easy way to copy data from one database to another or even within the same database.

There are countless situations where one needs to copy production data or QA data over to the development database to manipulate or analyze it because he/she does not have the privileges to alter the data but may have the privileges to “read” the data.

SQL*Plus COPY Command

The SQL*Plus COPY command is the most straight-forward tool for the job if the copy only involves only a table or two or a query.

Using SQL*Plus COPY command one can

  • Copy the contents of a table/query from one database to another
  • Create/Insert/Append/Replace the table in the target database
  • Be selective about what is copied over (as in a subset of the columns or even rows)
  • The source and destination may look structurally different, yet, the COPY can be done

Oracle Docs has an exhaustive treatment of the command here

It is easy to miss a couple of crucial details when reading through the documentation and when using the COPY COMMAND.

  • ARRAYSIZE:

Also, known as the batch size, this is used in the form “set arraysize 1000“. This limits the numbers of rows Oracle fetches at one time – in this case 1000 rows

  • COPYCOMMIT:

This is used in the form “set copycommit 5“. It simply says COMMIT the data after COPYing every 5 batches. i.e., (5 * 1000) = 5000 in this example

Caution: By default COPYCOMMIT is 0, meaning it will not commit until all rows are copied over.

There is no ideal setting but if you are not relying on the complete COPY to either succeed or fail, using a reasonable number for the above two is better than not specifying anything.

The syntax is straightforward –

COPY FROM [SOURCE] TO [TARGET] [OVERWRITE_MODE] [TARGET_TABLE_NAME] USING [QUERY]

In my example below, I want to copy some audit data from Production to Development for analysis:

Open the DOS command prompt (or) open SQLPlus in your UNIX shell. The database that you connect to does not have to be related to the source or the destination.

>SQLPLUS my_user_id/****@mydb

SET ARRAYSIZE 1000
SET COPYCOMMIT 2

COPY FROM my_user_id/****@Source_DB TO my_user_id/****@Target_DB APPEND my_user_id.aud_some_user -
USING  -
SELECT 'Source_DB' AS db, a.*  -
FROM dba_audit_trail a -
WHERE username = 'SOME_USER'  -
    AND (sysdate-timestamp) <= 7;

Here are a few things to notice:

  • The batch size is set at 1000 and COMMIT happens every 2 batches
  • – (hyphen) is used as the line continuation character
  • APPEND is used suggesting that the data will be APPENDed to the existing table my_user_id.aud_some_user. If destination does not exist, it will be created
  • A column named “db” is introduced as part of the query and the query has a WHERE clause to limit data

The output looks like this (see confirmation about arraybind & copycommit):

SQL>
SQL> SET ARRAYSIZE 1000
SQL> SET COPYCOMMIT 2
SQL> COPY FROM my_user_id/*****@Source_DB TO my_user_id/******@Target_DB  APPEND my_user_id.aud_some_user
> USING  -
> SELECT 'Source_DB' AS db, a.*  -
> FROM dba_audit_trail a -
> WHERE username = 'SOME_USER'  -
>     AND (sysdate-timestamp) <= 7; Array fetch/bind size is 1000. (arraysize is 1000)   Will commit after every 2 array binds. (copycommit is 2) Maximum long size is 80. (long is 80) Table my_user_id.AUD_some_user_X created. 1045 rows selected from my_user_id@Source_DB. 1045 rows inserted into my_user_id.AUD_some_user. 1045 rows committed into my_user_id.AUD_some_user at my_user_id@Target_DB. SQL>

Much of the documentation in Oracle Docs is irrelevant, so I am copying just the important part that talks about the data write choices in the destination.

SQL*Plus COPY Command Options

[Begin: Copy from Oracle docs]

APPEND

Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.

CREATE

Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.

INSERT

Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in the destination_table.

REPLACE

Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.

[End: Copy from Oracle docs]

All the rules about UNDO and REDO apply to the COPY command.

Watching progress

If you knew the number of rows that the COPY is supposed to COPY, you can even watch the progress using this query (assuming you have adequate privileges):

   select
         substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min,
         elapsed_time/1000000 elapsed_seconds
  from   sys.v_$sqlarea
  where  sql_text like '%INSERT%'
    and  command_type = 2
    and  open_versions > 0

Unfortunately, Oracle has decided to deprecate the COPY command in 11g and it may only be available in future versions beyond 11g by using the SQLPLUSCOMPATIBILITY variable. The Oracle “DataPump” is the recommended replacement.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s