一、将表数据按指定行数进行拆分,并装在多个文件中。
sqluldr2 username/password@tnsname query = "此处填写SQL查询语句" head=yes field=“|” file=XXX_%B.txt safe=yes batch=yes rows=10000
其中:
sqluldr2需要在绝对路径下执行。
文件名后加_%B,可以让生成多个文件时,按_1、_2、_3的命名顺序依次创建文件。
二、其他参数介绍(机翻,仅供参考)
英文 | 中文机翻 |
---|---|
user = username/password@tnsname | 用户 = 用户名/密码@数据库地址 |
sql = SQL file name | sql= SQL 文件名 |
query = select statement | 查询 = 查询语句 |
field = separator string between fields | field = 字段之间的分隔符字符串 |
record = separator string between records | record = 记录之间的分隔符字符串 |
rows = print progress for every given rows (default, 1000000) | rows = 打印每个给定行的进度(默认值,1000000) |
file = output file name(default: uldrdata.txt) | file = 输出文件名(默认:uldrdata.txt) |
log = log file name, prefix with + to append mode | log = 日志文件名,前缀+表示追加模式 |
fast = auto tuning the session level parameters(YES) | fast = 自动调整会话级参数(YES) |
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM). | 文本 = 输出类型(MYSQL、CSV、MYSQLINS、ORACLEINS、FORM) |
charset = character set name of the target database. | charset = 目标数据库的字符集名称 |
ncharset= national character set name of the target database. | ncharset=目标数据库的国家字符集名称 |
parfile = read command option from parameter file | parfile = 从参数文件中读取命令选项 |
read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level | read = 在会话级别设置 DB_FILE_MULTIBLOCK_READ_COUNT |
sort = set SORT_AREA_SIZE at session level (UNIT:MB) | sort = 在会话级别设置 SORT_AREA_SIZE (UNIT:MB) |
hash = set HASH_AREA_SIZE at session level (UNIT:MB) | hash = 在会话级别设置 HASH_AREA_SIZE (UNIT:MB) |
array = array fetch size | 数组 = 数组获取大小 |
head = print row header(Yes/No) | head = 打印行标题(是/否) |
batch = save to new file for every rows batch (Yes/No) | 批次=将每行批次保存到新文件(是/否) |
size = maximum output file piece size (UNIB:MB) | size = 最大输出文件块大小 (UNIB:MB) |
serial = set _serial_direct_read to TRUE at session level | Serial = 在会话级别将 _serial_direct_read 设置为 TRUE |
trace = set event 10046 to given level at session level | 跟踪 = 将事件 10046 设置为会话级别的给定级别 |
table = table name in the sqlldr control file | table = sqlldr 控制文件中的表名 |
control = sqlldr control file and path. | control = sqlldr 控制文件和路径 |
mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE | 模式 = sqlldr 选项,INSERT 或 APPEND 或 REPLACE 或 TRUNCATE |
buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB) | buffer = sqlldr READSIZE 和 BINDSIZE,默认 16 (MB) |
long = maximum long field size | long = 最大长字段大小 |
width = customized max column width (w1:w2:…) | 宽度 = 自定义最大列宽 (w1:w2:…) |
quote = optional quote string | quote = 可选的引号字符串 |
data = disable real data unload (NO, OFF) | data = 禁用实际数据卸载(NO、OFF) |
alter = alter session SQLs to be execute before unload | alter = 更改卸载前要执行的会话 SQL |
safe = use large buffer to avoid ORA-24345 error (Yes/No) | 安全 = 使用大缓冲区以避免 ORA-24345 错误(是/否) |
crypt = encrypted user information only (Yes/No) | crypt = 仅加密的用户信息(是/否) |
sedf/t = enable character translation function | sedf/t = 启用字符翻译功能 |
null = replace null with given value | null = 用给定值替换 null |
escape = escape character for special characters | escape = 特殊字符的转义字符 |
escf/t = escape from/to characters list | escf/t = 从字符列表转义/转义到字符列表 |
format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs. | 格式 = MYSQL: MySQL 插入 SQL, SQL: 插入 SQL |
exec = the command to execute the SQLs. | exec = 执行SQL的命令 |
prehead = column name prefix for head line. | prehead = 标题行的列名前缀 |
rowpre = row prefix string for each line. | rowpre = 每行的行前缀字符串 |
rowsuf = row sufix string for each line. | rowsuf = 每行的行后缀字符串 |
colsep = separator string between column name and value. | colsep = 列名和值之间的分隔符字符串 |
presql = SQL or scripts to be executed before data unload. | presql = 数据卸载之前要执行的 SQL 或脚本 |
postsql = SQL or scripts to be executed after data unload. | postgresql = 数据卸载后要执行的 SQL 或脚本 |
lob = extract lob values to single file (FILE). | lob = 将 lob 值提取到单个文件 (FILE) |
lobdir = subdirectory count to store lob files . | lobdir = 存储 lob 文件的子目录数 |
split = table name for automatically parallelization. | split = 自动并行化的表名 |
degree = parallelize data copy degree (2-128). | Degree = 并行化数据复制度数 (2-128) |
hint = MySQL SQL hint for the Insert, for example IGNORE. | hint = 用于插入的 MySQL SQL 提示,例如 IGNORE |
unique = Unique Column List for the MySQL target table. | unique = MySQL 目标表的唯一列列表 |
update = Enable MySQL ON DUPLICATE SQL statement(YES/NO). | 更新 = 启用 MySQL ON DUPLICATE SQL 语句(是/否) |