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.
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
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]
Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.
Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.
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.
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.
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.