SQL * Plus copy command to process Mass Data Replication

The SQL * Plus copy command is used 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 [SQL] 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. The from database clause specifies 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 from a remote database to another remote database, use Oracle net to transmit data. 2. The same database has the same schem Data between a is replicated in the [SQL] -- 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 I Nto 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 @ sy Bo2sz. 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 co Mmitted 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 @ sybo2 Sz. 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 d Ropped. 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 [SQL]-The append method is used below, specify the from and to clause 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 don E. (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 [SQL] -- replication between different databases must specify the target database 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. (ar Raysize 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 [SQL]-The following is data replication between oracle 10 GB and oracle 11 GB. cott @ SYBO2SZ> copy from scott/tiger @ sybo2sz to scott/tiger @ ora11g-> create tb_emp using select * f 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. Performance Parameter of the copy command [SQL] parameters related to copy performance Rraysize this parameter is used for the number of rows of each fetch data in SQL * Plus. The default value is 15. Valid values: 1 to 5000 copycommit. this parameter is used to copy the number of rows of data and then run commit, if the value is 0, it indicates that after all data is copied, run the commit long parameter 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 copycommit scott @ SYBO2SZ> set arraysize 15 scott @ 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 the table scott @ SYBO2S Z> @/users/robin/dba_scripts/custom/temp/cp_0000. SQL PL/SQL procedure successfully completed. 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 0 The redo size is 0 PL/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 copycommit scott @ SYBO 2SZ> set arraysize 2000 scott @ SYBO2SZ> set copycommit 5000 -- call the script scott @ SYBO2SZ again> @/users/robin/dba_scripts/custom/temp/cp_assist. SQL PL/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. 1000 000 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 0 The redo size is 0 PL/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. The script used for testing [SQL] robin @ SZDB :~ /Dba_scripts/temp M/temp> more cp_cmd. SQL SET 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 begin 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. statistic # = B. statistic # and lower (. name) = 'undo change vector size'; SELECT B. value into: v_s_redo FROM v $ statname a, v $ mystat B WHERE. statistic # = B. statistic # and lower (. 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. statistic # = B. statistic # and lower (. name) = 'undo change vector size'; SELECT B. value into: v_e_redo FROM v $ statname a, v $ mystat B WHERE. statistic # = B. statistic # and lower (. 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 ;/