SQLPlUS Copy命令學習筆記_20110806
Oracle 10g開始在SQLPLUS中引入了Copy命令
SQLPLUS Copy命令可以在不同的資料庫之間或不同的使用者之間複製表資料,且錶帶有Long欄位不能使用CTAS時相當有用;
示例:
sqlplus /nolog
set long 1000
set copycommit 10
set arraysize 5000
copy from to create new_t using select * from t;
copy from to insert new_t using select * from t;
copy from to append new_t using select * from t;
copy from to replace new_t using select * from t;
Copy Command
The SQL*Plus COPY command can copy data between two databases via SQL*Net . The preferred method of doing this is to use SQL*Plus on the host where the database resides. If performing the copy command from a client SQL*Net connection, the data is transferred through the client machine.
The copy command copies data from one Oracle instance to another. The data is simply copied directly from a source to a target. The format of the copy command is:
COPY FROM database TO database action - destination_table (column_name, column_name...) USING query
The action can include:
1) create – If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied.
2) replace – If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data.
3) insert – If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts.
4) append– Inserts the data into the table if it exists, otherwise it will create the table and then insert the data.
#可以設定的引數
Once the command above is executed, the copy utility displays the values of three parameters, each of which can be set with the SQL*Plus set command.
The arraysize specifies the number of rows that SQL*Plus will retrieve from the database at one time.
The copycommit parameter specifies how often a commit is performed and is related to the number of trips – one trip is the number of rows defined in arraysize.
The long parameter displays the maximum number of characters copied for each column with a LONG datatype.
#設定不同的列名
The command above did not specify column names for the new table (new_t). As a result, the new table will have the same column names as the table being copied. If different column names are required, they can be specified after the table name:
SQL>copy from to create new_t using(col1,col2,col3,....) select * from t;
However, if one column name is specified, they all must be specified.
A DBA could perform. this same function with a database link from one database pointing to another. The appeal of the copy command is that it only requires SQL*Net service names and proper privileges to get the job done. For those environments that restrict the usage of database links, the copy utility can be leveraged. In addition, the copy command provides many options, as defined by the actions create, replace, insert and append.
If the copy command is executed from a client PC to copy data from remote database DB0 to remote database DB1, the data will be copied from DB0 to the client PC and then to DB1. For this reason, it is best to use SQL*Plus from either remote host and not require the data to travel through a client machine in order to reach its final destination.
參考文件<
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10248702/viewspace-704339/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQLPLUS 的COPY命令SQL
- lsof命令學習筆記筆記
- Redis 學習筆記命令Redis筆記
- Git命令學習筆記Git筆記
- Shells命令列學習筆記命令列筆記
- 【Linux學習筆記】reboot命令Linux筆記boot
- oracle學習筆記-常用的命令Oracle筆記
- 學習《PLSQL開發指南》筆記—— SQLPLUS建立過程前的學習SQL筆記
- linux學習筆記---一些命令學習Linux筆記
- MongoDB 學習筆記之常用 shell 命令MongoDB筆記
- MySQL學習筆記之命令總結MySql筆記
- oracle學習筆記--oracle常用的命令Oracle筆記
- SGI STL學習筆記(3):copy演算法實現細節筆記演算法
- SQLPLUS COPY 功能。SQL
- ZooKeeper 學習筆記一:常用命令筆記
- Linux 常用命令 學習筆記Linux筆記
- OpenSSL學習筆記:openssl命令列的使用筆記命令列
- git常用命令-Git學習筆記Git筆記
- numpy的學習筆記\pandas學習筆記筆記
- git status 命令總結 —— Git 學習筆記 06Git筆記
- Linux常用命令cp學習筆記Linux筆記
- git checkout 命令詳解—— Git 學習筆記 16Git筆記
- IT學習筆記筆記
- 學習筆記筆記
- 04_Python集合與深淺copy_全棧開發學習筆記Python全棧筆記
- 學習 Nginx 的一些筆記,命令配置等Nginx筆記
- 使用 seed 命令建立模擬資料(學習筆記)筆記
- git reset 命令詳解(二)—— Git 學習筆記 08Git筆記
- git reset 命令詳解(一)—— Git 學習筆記 07Git筆記
- Docker 入門學習筆記二:常用命令Docker筆記
- Linux學習筆記:常用100條命令(一)Linux筆記
- Linux學習筆記:常用100條命令(二)Linux筆記
- Linux學習筆記:常用100條命令(三)Linux筆記
- 《Linux系統free命令的使用》學習筆記Linux筆記
- 【linux學習筆記七】關機重啟命令Linux筆記
- 【學習筆記】數學筆記
- 《JAVA學習指南》學習筆記Java筆記
- Zero Copy 學習總結