【Oracle】Oracle系列之十七--Sqluldr2与Sqlldr

我就是我 2024-02-25 07:44 19阅读 0赞

#### 文章目录 ####

*  往期回顾
 *  1. Sqluldr2导出文本
 *  2. Sqlldr导入文本

## 往期回顾 ##

*  [【Oracle】Oracle系列之一–Oracle数据类型][Oracle_Oracle_Oracle]
 *  [【Oracle】Oracle系列之二–Oracle数据字典][Oracle_Oracle_Oracle 1]
 *  [【Oracle】Oracle系列之三–Oracle字符集][Oracle_Oracle_Oracle 2]
 *  [【Oracle】Oracle系列之四–用户管理][Oracle_Oracle]
 *  [【Oracle】Oracle系列之五–Oracle表空间][Oracle_Oracle_Oracle 3]
 *  [【Oracle】Oracle系列之六–Oracle表分区][Oracle_Oracle_Oracle 4]
 *  [【Oracle】Oracle系列之七–表的创建与管理][Oracle_Oracle 1]
 *  [【Oracle】Oracle系列之八–SQL查询][Oracle_Oracle_SQL]
 *  [【Oracle】Oracle系列之九–Oracle常用函数][Oracle_Oracle_Oracle 5]
 *  [【Oracle】Oracle系列之十–Oracle正则表达式][Oracle_Oracle_Oracle 6]
 *  [【Oracle】Oracle系列之十一–PL/SQL][Oracle_Oracle_PL_SQL]
 *  [【Oracle】Oracle系列之十二–视图、记录、同义词、序列][Oracle_Oracle 2]
 *  [【Oracle】Oracle系列之十三–游标][Oracle_Oracle 3]
 *  [【Oracle】Oracle系列之十四–触发器][Oracle_Oracle 4]
 *  [【Oracle】Oracle系列之十五–存储过程][Oracle_Oracle 5]
 *  [【Oracle】Oracle系列之十六–数据库备份][Oracle_Oracle 6]

## 1. Sqluldr2导出文本 ##

Sqluldr2是灵活与强大的Oracle文本导出程序，主要参数如下：

*  user = username/password@tnsname
 *  sql = SQL file name
 *  query = select statement
 *  field = separator string between fields
 *  record = separator string between records
 *  rows = print progress for every given rows (default, 1000000)
 *  file = output file name(default: uldrdata.txt)
 *  log = log file name, prefix with + to append mode
 *  fast = auto tuning the session level parameters(YES)
 *  text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
 *  charset = character set name of the target database.
 *  ncharset= national character set name of the target database.
 *  parfile = read command option from parameter file  
    使用sqluldr2 help=yes可以查看更多参数说明。

e.g.

sqluldr2 data/data@orcl 
            query="select * from sx_table" 
            parfile=d:\exp.par 
            file=d:\sx_table.txt

exp.par 内容如下：

head=yes
    batch=yes
    safe=yes
    rows=3000000
    record=0x0d0x0a

在当前目录下会生成一个名为”sx\_table\_sqlldr.ctl”的控制文件，通过该控制文件可以用SQL\*Loader将数据加载到sx\_table表中。

## 2. Sqlldr导入文本 ##

Sqlldr（SQL\*Loader）是Oralce用来将文本文件装载到数据库中的。

sqlldr userid=test/test@orcl 
           readsize=33554432 
           streamsize=33554432
           date_cache=10000000 
           direct=true 
           skip_index_maintenance=true
           skip_unusable_indexes=true 
           multithreading=true 
           errors=10000000 
           skip=1 
           log="d:\load.log" 
           control="d:\sx_table_sqlldr.ctl"
           data="d:\sx_table.txt"

[Oracle_Oracle_Oracle]: https://writer.blog.csdn.net/article/details/132955822
[Oracle_Oracle_Oracle 1]: https://writer.blog.csdn.net/article/details/132997299
[Oracle_Oracle_Oracle 2]: https://writer.blog.csdn.net/article/details/133047093
[Oracle_Oracle]: https://writer.blog.csdn.net/article/details/133062052
[Oracle_Oracle_Oracle 3]: https://writer.blog.csdn.net/article/details/133148656
[Oracle_Oracle_Oracle 4]: https://writer.blog.csdn.net/article/details/133156311
[Oracle_Oracle 1]: https://writer.blog.csdn.net/article/details/133190196
[Oracle_Oracle_SQL]: https://writer.blog.csdn.net/article/details/133230775
[Oracle_Oracle_Oracle 5]: https://blog.csdn.net/u011397981/article/details/133279884?spm=1001.2014.3001.5501
[Oracle_Oracle_Oracle 6]: https://blog.csdn.net/u011397981/article/details/133362496?spm=1001.2014.3001.5501
[Oracle_Oracle_PL_SQL]: https://blog.csdn.net/u011397981/article/details/133390643?spm=1001.2014.3001.5501
[Oracle_Oracle 2]: https://blog.csdn.net/u011397981/article/details/133408051?spm=1001.2014.3001.5501
[Oracle_Oracle 3]: https://blog.csdn.net/u011397981/article/details/133442823?spm=1001.2014.3001.5501
[Oracle_Oracle 4]: https://writer.blog.csdn.net/article/details/133470299
[Oracle_Oracle 5]: https://writer.blog.csdn.net/article/details/133500848
[Oracle_Oracle 6]: https://writer.blog.csdn.net/article/details/133563318