SQL * Plus Copy command to process Mass Data Replication

For data replication at the database table level, the most common method is create table as (CTAs. In fact, the copy command under SQL * Plus can do the same job, and it is better, and the performance is also better. What's more, data replication across platforms and heterogeneous databases is supported. The Copy command can accomplish some stream functions similarly, although the Copy command and stream method are not heavyweight. The following describes the main usage of the Copy command.

 

1. Copy Command help information

Scott @ sybo2sz> 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 lists the data types supported by copy and the usage of the Copy command from database clause to specify the connected source database, if omitted, specify the target database to be connected for the currently connected database to the database clause, if this parameter is omitted, the source and target databases connected to the current database from database to database are also specified. The target database supports data replication between tables: append | create | insert | replace supports data replication across Oracle versions and between different schemas and between the same schema. It supports data replication between heterogeneous databases, for example, Oracle to non-Oracle databases support database replication across Oracle platforms, for example, windows to Linux platforms support local databases to remote databases, remote databases to local, when copying data between a remote database and another remote database, use Oracle Net to transmit data

2. Data replication between the same database and the same schema

-- Create method. Only the from clause -- Note is specified. In the following example, the symbol "-" indicates a connection symbol, used for line feed writing Scott @ sybo2sz> copy from Scott/tiger @ sybo2sz-> Create tb_emp-> using select * from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp created. 14 rows selected from Scott @ sybo2sz. 14 rows inserted into tb_emp. 14 rows committed into tb_emp at default host connection. -- append method. Only the to clause Scott @ sybo2sz> copy to Scott/tiger @ sybo2sz-> append tb_emp-> using select * from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) 14 rows selected from default host connection. 14 rows inserted into tb_emp. 14 rows committed into tb_emp at scott@sybo2sz.scott @ sybo2sz> select count (*) from tb_emp; count (*) ---------- 28 -- insert method Scott @ sybo2sz> copy from Scott/tiger @ sybo2sz-> insert tb_emp2 using select * from EMP where deptno = 20; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) 5 rows selected from Scott @ sybo2sz. 5 rows inserted into tb_emp2. 5 rows committed into tb_emp2 at default host connection. -- replace: the number of table records obtained in the previous test is 5, and the number of records after replace is 14, as shown below, scott @ sybo2sz> copy from Scott/tiger @ sybo2sz-> replace tb_emp2 using select * from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp2 dropped. table tb_emp2 created. 14 rows selected from Scott @ sybo2sz. 14 rows inserted into tb_emp2. 14 rows committed into tb_emp2 at default host connection. -- use the column alias method -- the column alias is used below, and only several columns of data are copied. Scott @ sybo2sz> copy from Scott/tiger @ sybo2sz-> replace tb_emp2 (Eno, name, job_name) using select empno, ename, job from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp2 dropped. table tb_emp2 created. 14 rows selected from Scott @ sybo2sz. 14 rows inserted into tb_emp2. 14 rows committed into tb_emp2 at default host connection.

3. Data replication between different schemas of the same database

-- The append method is used, and the from and to clauses Scott @ sybo2sz> copy from Scott/tiger @ sybo2sz to goex_admin/xxx @ sybo2sz-> append tb_emp using select * from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp created. 14 rows selected from Scott @ sybo2sz. 14 rows inserted into tb_emp. 14 rows committed into tb_emp at goex_admin @ sybo2sz.

4. Data replication between different databases

-- For replication between different databases, you must specify the connection string Scott @ sybo2sz> copy from Scott/tiger @ sybo2sz to goex_admin/xxx @ cnmmbo-> append tb_emp using select * from EMP; array fetch/Bind size is 2000. (arraysize is 2000) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp created. 14 rows selected from Scott @ sybo2sz. 14 rows inserted into tb_emp. 14 rows committed into tb_emp at goex_admin @ cnmmbo.

5. Data replication between different Oracle versions

-- Cott @ sybo2sz> copy from Scott/tiger @ sybo2sz to Scott/tiger @ ora11g-> Create tb_emp using select * from EMP where deptno = 30; array fetch/Bind size is 2000. (arraysize is 2000) will commit after every 0 array binds. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table tb_emp created. 6 rows selected from Scott @ sybo2sz. 6 rows inserted into tb_emp. 6 rows committed into tb_emp at Scott @ ora11g. -- data can also be copied from Oracle 11g to Oracle 10g, which is omitted here -- cross-platform data replication, no environment, to be tested

6. Copy Command Performance Parameters

Copy performance-related parameters arraysize this parameter is used for SQL * Plus the number of rows per fetch data. The default value is 15, valid value: 1 to 5000 copycommit this parameter is used to copy the number of rows of data and then execute commit. If this value is 0, after all data is copied, run commitlong. this parameter is used to set the maximum length of the long character type, oracle does not recommend using the long type but the lob type instead -- first set the arraysize parameter and copycommitscott @ sybo2sz> set arraysize 15scott @ sybo2sz> set copycommit 0 -- clear the cache Scott @ sybo2sz> alter System flush buffer_cache; scott @ sybo2sz> alter system flush shared_pool; -- execute the script to call copy to create a table Scott @ sybo2sz> @/users/Robin/dba_scripts/custom/temp/cp_cmd.sqlpl/SQL procedure successfully. array fetch/Bind size is 15. (arraysize is 15) will commit when done. (copycommit is 0) Maximum long size is 5000. (Long is 5000) Table cp_big_tb created. 1000000 rows selected from Scott @ sybo2sz. 1000000 rows inserted into cp_big_tb. 1000000 rows committed into cp_big_tb at scott@sybo2sz.PL/SQL procedure successfully completed. the elapsed time is 41.84 seconds. the undo size is 0the redo size is 0pl/SQL procedure successfully completed. -- The above result indicates that, when the Copy command is used, undo and redo are not generated. The time for copying Row 1 million data is 41.84 seconds. The following command clears the target table Scott @ sybo2sz> drop table cp_big_tb purge; -- clear the cache Scott @ sybo2sz> alter system flush buffer_cache; Scott @ sybo2sz> alter system flush shared_pool; -- set the new arraysize and copycommitscott @ sybo2sz> set arraysize 2000scott @ sybo2sz> set copycommit 5000 -- call the script Scott @ sybo2sz> @/users/Robin/dba_scripts/custom/temp/cp_cmd. SQL /SQL procedure successfully completed. array fetch/Bind size is 2000. (arraysize is 2000) will commit after every 5000 array binds. (copycommit is 5000) Maximum long size is 5000. (Long is 5000) Table cp_big_tb created. 1000000 rows selected from Scott @ sybo2sz. 1000000 rows inserted into cp_big_tb. 1000000 rows committed into cp_big_tb at scott@sybo2sz.PL/SQL procedure successfully completed. the elapsed time is 24.65 seconds. the undo size is 0the redo size is 0pl/SQL procedure successfully completed. -- from the above results, we can see that the latter consumes much less time than the former, saving nearly half of the time.

7. scripts used for testing

robin@SZDB:~/dba_scripts/custom/temp> more cp_cmd.sqlSET SERVEROUTPUT ON;VARIABLE start_time NUMBER;VARIABLE end_time NUMBER;VARIABLE v_s_undo NUMBER;VARIABLE v_s_redo NUMBER;VARIABLE v_e_undo NUMBER;VARIABLE v_e_redo NUMBER;VARIABLE v_diff_dt NUMBER;VARIABLE v_diff_undo NUMBER;VARIABLE v_diff_redo NUMBER;--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612BEGIN   SELECT DBMS_UTILITY.get_time INTO :start_time FROM DUAL;   SELECT b.VALUE     INTO :v_s_undo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';   SELECT b.VALUE     INTO :v_s_redo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';END;/COPY from scott/tiger@sybo2sz -to scott/tiger@sybo2sz -create cp_big_tb -using -select * from big_table;BEGIN   SELECT DBMS_UTILITY.get_time INTO :end_time FROM DUAL;   SELECT b.VALUE     INTO :v_e_undo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';   SELECT b.VALUE     INTO :v_e_redo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';END;/BEGIN   :v_diff_dt := round((:end_time - :start_time)/100,2);   :v_diff_undo := :v_e_undo - :v_s_undo;   :v_diff_redo := :v_e_redo - :v_s_redo;   DBMS_OUTPUT.put_line ('The elapsed time is ' || TO_CHAR (:v_diff_dt)||' seconds.');   DBMS_OUTPUT.put_line ('The undo size is ' || TO_CHAR (:v_diff_undo));   DBMS_OUTPUT.put_line ('The redo size is ' || TO_CHAR (:v_diff_redo));END;/

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan