The usage of copy from in oracle Java code 1. syntax and usage instructions 1.1 syntax below let's look at the syntax and usage instructions of SQL * Copy Command. Before interpreting the syntax of SQL * Plus Copy Command, we must make it clear that SQL * Plus Copy Command is neither a method nor a function nor an SQL statement. It is a command ), of course, this command must be run in SQL * Plus. SQL * Plus Copy Command syntax: COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE} destination_table [(column, column, column,...)] USING query: COPY-this does not need to be explained. The main command, declare that you want To perform the COPY operation From Database-source Database To Database-target Database. Note that there are three optional methods in brackets (separated by "| ), if the source and target data tables are in the same Schema, you can write only From Database or To Database. Of course, it can also be the third method, write All From Database and To Database. However, if the source and target data tables are not in the same Schema, you must use the third method, that is, the format of From Database and To Database is the same: USERID/PASSWORD @ SID, which should be familiar to everyone. {APPEND | CREATE | INSERT | REPLACE}-Method of declaring operation data. The following describes how to Append a record to an existing target table by APPEND. If the target table does not exist, it is automatically created, this is equivalent to Create. Create-Create a target table and add records to it. If the target table already exists, an error is returned. Insert-Insert a record into an existing target table. Unlike Append, if the target table does not exist and is not created automatically, an error is returned. Replace-overwrite existing data in the target table with the queried data. If the target table does not exist, it is automatically created. Destination_table-name of the target table [(column,...)]-You can specify the name of the column in the target table. If not specified, the column name in the Query is automatically used. USING query-query statement. The exchange data comes from here. Note: 1. this method is suitable for small data migration and can only be run in SQL * PLUS. 2. this method cannot copy large Object tables. Otherwise, an exception "CPY0012: Object PES ypes cannot be copied" is thrown. Copying data between data tables by Java code is one of the tasks that Oracle DBAs often face, oracle provides a variety of solutions for this task. The SQL * Plus Copy command is one of them. The SQL * Plus Copy command uses SQL * Net to Copy or move data between different tables (the same server or different servers. In the actual running environment, selecting the SQL * Plus Copy command can effectively improve the data replication performance. The following describes how to use the SQL * Plus Copy Command, and compares the performance with the other two solutions, so as to provide a reference for using the Copy command. 1. syntax and usage instructions 1.1 syntax below let's take a look at the syntax and usage instructions of the SQL * Copy command. Before interpreting the syntax of the SQL * Plus Copy command, we must clarify that the SQL * Plus Copy command is neither a method nor a function nor an SQL statement. It is a command ), of course, this command must be run in SQL * Plus. Syntax of the SQL * Plus Copy command: COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE} destination_table [(column, column, column,...)] USING query: COPY-this does not need to be explained. The main command, declare that you want To perform the COPY operation From Database-source Database To Database-target Database. Note that there are three optional methods in brackets (separated by "| ), if the source and target data tables are in the same Schema, you can write only From Database or To Database. Of course, it can also be the third method, write All From Database and To Database. However, if the source and target data tables are not in the same Schema, you must use the third method, that is, the format of From Database and To Database is the same: USERID/PASSWORD @ SID, which should be familiar to everyone. {APPEND | CREATE | INSERT | REPLACE}-Method of declaring operation data. The following describes how to Append a record to an existing target table by APPEND. If the target table does not exist, it is automatically created, this is equivalent to Create. Create-Create a target table and add records to it. If the target table already exists, an error is returned. Insert-Insert a record into an existing target table. Unlike Append, if the target table does not exist and is not created automatically, an error is returned. Replace-overwrite existing data in the target table with the queried data. If the target table does not exist, it is automatically created. Destination_table-name of the target table [(column,...)]-You can specify the name of the column in the target table. If not specified, the column name in the Query is automatically used. USING query-query statement. The exchange data comes from here. 1.2 examples of use the SQL * Plus Copy command is as follows: 1.2.1 copying data in the same Schema of the same server: specify both From database and To database SQL> copy from scott/tiger @ lsj to scott/tiger @ lsj create dept1 using select * from dept; only specify From Database SQL> copy from scott/tiger @ lsj create dept2 using select * from dept; only specify To Database SQL> copy to scott/tiger @ lsj create dept3 using select * from dept; 1.2.2 copy data in different schemas of the same server: In this case, the same Specify From Database and To Database SQL> copy from scott/tiger @ lsj to lsjdemo/lsjdemo @ lsj create dept using select * from dept. In this case, you do not need to use scott in using select * from dept. demp format. 1.2.3 copy data between different servers: SQL> conn lsj/lsj @ sunserve is connected. SQL> copy from scott/tiger @ lsj to lsj/lsj @ sunserve create dept using select * from dept; 2.4 Performance Experiment Results experiment data: number of records: 5,082,500 data volume: 504 M Experiment Results solution ------------------------ execution time (seconds) --------- Undo (M) ------ Redo (M) Copy command --------------- 520.51 -------------------- 0 ---------------- 592 Insert... Select... ---- 631.64 ------------------ 345 ------------- 1720 Create Table... ------------- 244.79 -------------------- 0 ---------------- 515 2. Summary Create Table... As select... It is the fastest and generates the least Undo and Redo information. Therefore, use this solution whenever possible. However, this scheme has certain limitations, that is, the target table must not exist and cannot be used to append records to the existing target table. Insert... Select... It is the slowest, and generates the most Undo and Redo information, which puts the most pressure on I/O. The advantage is that you are familiar with it and easy to use. It is suitable for processing a small amount of data, this solution is not recommended if you want to process a large amount of data. Copy Command can process the situations where the Create Table cannot be processed, that is, to append records to an existing data Table. Compared with insert, the Copy Command is more efficient and generates less Redo information without generating Undo information, therefore, the Copy Command is recommended when a large amount of data is appended.