oracle中sqlldr和sqlplus使用

oracle中sqlldr和sqlplus使用

一、简介

sqlldr和sqlplus是oracle下的命令行工具,均位于/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/目录下,sqlldr可用于数据的导入,sqlplus可直接执行sql命令,sqlplus结合spool命令可对数据导出。下面分别介绍。

二、sqlldr

sqlldr可用与数据的导入。

2.1 sqlldr使用

2.1.1 sqlldr命令

sqlldr命令格式如下:

SQLLDR keyword=value [,keyword=value,...]

核心参数有:

userid:用户名密码等,格式: username/password[@servicename]
control:控制文件
log:日志文件
data:数据文件
bad:坏数据文件
discard:丢弃数据文件
discardmax:丢弃数据最大值,默认全部
error:允许出错的记录数,默认50
rows:多少条记录提交一次,默认64
skip:跳过开头的多少条记录,默认0
skip_index_maintenance:设置索引失效skip_index_maintenance=true, 注意使用后需要重建索引
bindsize:每次提交记录的缓冲区大小,单位字节,默认25600
readsize:每次读取记录的缓冲区大小,单位字节,默认1048576
silent:禁止输出信息(如header,feedback,errors,discards,partitions等)
direct:使用直通路径方式导入,默认false
parallel: 并行导入(注意:parallel并不是一个sqlldr同时起多个线程加载数据,而是不锁住加载表,允许别的路径加载。正确使用方式是同时用多个sqlldr加载数据)

更详细参数通过命令:

bin/sqlldr
2.1.2 控制文件

示例控制文件stu.ctl如下:

-- 控制文件stu.ctl
options(skip=1,rows=128) --sqlldr命令显示,skip=1表示跳过第一行
load drop database infile 'x1.csv' 'x2.csv' --导入的外部文件,可以多个。设置为*表示数据就在当前控制文件中
truncate  --清除表中原有数据, 还可填值,insert:插入(表一开始为空);append:追加记录 replace:删除(delete from xxx)旧记录,插入新记录; truncate:删除(truncate table xxx)旧记录,插入新记录; 默认insert
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
optionally enclosed by "'"  --每个字段用单引号括起
trailing nullcols --字段没值时允许为null
(
    virtual_column filler, --虚拟字段,用于跳过pl/sql 生成的第一列序号
    id int  primary key,
    no varchar(30) DEFAULT '',
    name varchar(60) DEFAULT '',
)

begindata  --以下为数据
9001,banana1

2.2 sqlldr示例

2.2.1 数据在控制文件中

控制文件stu.ctl如下:

options(skip=1,rows=128) --sqlldr命令显示,skip=1表示跳过第一行
load data infile * --导入的外部文件,可以多个
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(no,name)

begindata  --以下为数据
no,name
9001,banana1
9002,banana2
9003,banana3

执行命令:

#数据直接在控制文件中
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu.ctl
2.2.2 数据在控制文件内指定的数据文件中

控制文件stu1.ctl如下:

load data infile 'stu1.csv' --导入的外部文件,可以多个
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(
no,
name)

数据文件stu1.csv如下:

8001,banana1
8002,banana2
8003,banana3

执行命令:

#数据在控制文件内指定的数据文件中
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root@127.0.0.1:1521/helowin control=stu1.ctl
2.2.3 数据在控制文件内指定的多个数据文件中

控制文件stu2.ctl如下:

load data
infile 'stu1.csv' --导入的外部文件
infile 'stu2.csv' --导入的外部文件
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(no,name)

数据文件stu2.csv如下:

7001,banana1
7002,banana2
7003,banana3

执行命令:

#数据在控制文件内指定的数据文件(多个数据文件)中
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu2.ctl
2.2.4 数据在sqlldr命令参数指定的数据文件中

控制文件stu3.ctl如下:

load data
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(no,name)

执行命令:

#数据在sqlldr命令参数指定的数据文件中
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu3.ctl data=stu1.csv
2.2.5 控制文件调整指定字段

控制文件stu4.ctl如下:

load data
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(no "trim(:no)",  --对no字段trim
name ":name||'__'||length(:name)"  --打印name及其长度
)

数据文件stu4.csv如下:

   8001   ,banana1
8002,banana2
8003,banana3

执行命令:

#数据在sqlldr命令参数指定的数据文件中,同时控制文件对指定字段进行调整操作
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu4.ctl data=stu4.csv
2.2.6 控制文件跳过指定列

控制文件stu5.ctl如下:

load data
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(
skip_col filler, --虚拟字段,跳过当前列
no,
name
)

数据文件stu5.csv如下:

id,8001,banana1
id,8002,banana2
id,8003,banana3

执行命令:

#数据在sqlldr命令参数指定的数据文件中,同时控制文件对指定字段进行调整操作,跳过指定列
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu5.ctl data=stu5.csv
2.2.7 添加自增字段和日期字段

控制文件stu6.ctl如下:

load data infile 'stu1.csv' --导入的外部文件,可以多个
append
into table stu --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(
id recnum, --自增序号,不来自数据
no,
name,
update_time date 'yyyy-mm-dd hh24:mi:ss')

数据文件stu6.csv如下:

8001,banana1,2021-06-16 10:10:11
8002,banana2,2021-06-16 10:10:11
8003,banana3,2021-06-16 10:10:11

执行命令:

#数据在sqlldr命令参数指定的数据文件中,同时控制文件对指定字段进行调整操作,添加自增字段,添加日期字段
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=stu6.ctl data=stu6.csv
2.2.8 并行加载数据

控制文件teacher.ctl如下:

load data
append
into table teacher --要插入的表
fields terminated by ','  --每行记录各字段间用逗号分割
trailing nullcols --字段没值时允许为null
(id,no,name)

数据文件teacher1.csv如下:

1001,,banana1
1002,1002,banana2
1003,1003,

数据文件teacher2.csv如下:

2001,2001,banana1
2002,2002,banana2
2003,2003,banana3

数据文件teacher3.csv如下:

8001,8001,banana1
8002,8002,banana2
8003,8003,banana3

执行命令:

#并行加载数据,通过指定skip_index_maintenance=true, (此时可以关闭日志输出:alter table xx nologging)
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=teacher.ctl direct=true parallel=true data=teacher1.csv skip_index_maintenance=true

nohup /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=teacher.ctl direct=true parallel=true data=teacher2.csv skip_index_maintenance=true &

nohup  /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlldr userid=school_user/root control=teacher.ctl direct=true parallel=true data=teacher3.csv skip_index_maintenance=true &

命令执行完后重建索引:

alter index TEACHER_IDX_1 rebuild ;
alter index TEACHER.PK_TEACHER rebuild ;

索引相关操作:

-- 查看主键约束
select * from USER_CONSTRAINTS where CONSTRAINT_TYPE='P' and TABLE_NAME='TEACHER';
-- 删除主键约束  alter table TEACHER drop constraint 约束名
alter table TEACHER drop constraint SYS_C0011120;
-- 添加主键约束
alter table teacher add constraint pk_teacher primary key (id);

-- 查看索引
select * from USER_INDEXES where TABLE_NAME=upper('teacher');

2.3 常见问题

2.3.1 SQL*Loader-937
问题:parallel=true 并行执行时报错SQL*Loader-937: parallel load requested and STU has enabled triggers or constraints
解决:对并行的直接路径导入有限制,表中不能有check、referential约束和触发器
2.3.2 ORA-26002
问题:parallel=true 并行执行时报错 ORA-26002: Table SCHOOL_USER.TEACHER has index defined upon it.
解决:查看session中target属性 target load type是bulk还是normal。默认bulk模式,不写索引
方式一, drop index, 即删除索引,drop index index_name 或主键约束  alter table TEACHER drop constraint pk_name
方式二, disable parallel loading  即是改为normal模式
方式三,skip_index_maintain, 即在sqlldr命令行中设置参数skip_index_maintain=true, 注意数据加载完后需要重建索引 alter index index_name rebuild
2.3.3 Message 2100
问题:Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL[oracle@da89b322aeae docker_oracle_space]
原因:没有设置环境变量
解决:设置环境变量 vim /etc/profile
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export PATH=$ORACLE_HOME/bin:$PATH
最后更新环境变量 source /etc/profile
2.3.4 ORA-02437
问题:ORA-02437: cannot validate (SCHOOL_USER.PK_TEACHER) - primary key violated
原因:表没有主键,但有重复数据,在有重复数据列上添加主键就报错
解决:删除重复数据
2.3.5 ORA-01502
问题:ORA-01502: index 'SCHOOL_USER.PK_TEACHER' or partition of such index is in unusable state
原因:索引状态为UNUSABLE引起,通过语句查看 select INDEX_NAME, STATUS from DBA_INDEXES where TABLE_NAME='TEACHER';
解决:重建索引即可,alter index PK_TEACHER rebuild ;

三 、sqlplus

sqlplus用于在命令行中执行sql,结合spool命令还可对数据导出。

3.1 sqlplus使用

3.1.1 sqlplus命令

sqlplus命令为:

sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]
其中:
<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
<logon> is: {<username>[/<password>][@<connect_identifier>] | / }
              [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]              

更详细命令查看:

bin/sqlplus -H
3.1.2 sqlplus连接

sqlplus连接数据库,常用的有以下几种:

sqlplus username/passowrd
sqlplus username/passowrd as sysdba
sqlplus username/passowrd@service_name
sqlplus username/passowrd@//host:port/sid
3.1.3 sqlplus内参数设置

sqlplus内常用参数设置有:

set timing on: 显示sql运行时间,默认off
set autotrace on: 允许对执行sql分析,默认off
set trimout on: 去除标准输出每行拖尾空格,默认off
set echo on: 显示启动脚本的sql命令,默认on
set feedback on:显示sql处理条数,默认on
set colsep '':设置列间分隔符
set heading off:显示标题域,默认on
set pagesize 0: 设置每页行数,0为不分页,默认24
set linesize 80: 设置一行字符个数,默认80
set numberwidth 10: 设置number类型域长度,默认10
set termout off:显示脚本执行结果,默认on
set serveroutput on: 设置输出类似dbms_output
set verify off: 显示确认信息
set serveroutput on:设置显示DBMS_OUTPUT.PUTLINE输出
set sqlblanklines on: 识别空行,不把空行当成一条语句的结束,用于解决一条语句放多行且中间有空行(另一种处理方式是在空行前加/)
3.1.4 sql中换行处理
  1. 含子程序时的换行处理

    sqlplus内,在pl/sql中,如果有子程序(declear/begin/end),则使用的分号被视为子程序的一部分,此时使用分号来标识换行则不能识别,此时标识语句结束换行用符号/
    
  2. 常规语句含空行处理

    sqlplus内,空行当做一条语句的结束,想要识别空行,有两种:
    1:设置参数 set sqlblanklines on;
    2: 空行头添加 \
    
3.1.5 sqlplus命令行特殊符号
  1. 斜杠/: /是sqlplus中的命令,表示再次执行刚刚执行过保存在缓存中的命令

3.2 sqlplus示例

3.2.1 常规执行
#sqlplus执行
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlplus -s school_user/root@127.0.0.1:1521/helowin <<EOF
set heading off;
set feedback off;
set termout off;
set echo off;
set verify off;
set colsep '|';
set trimout on;
set pagesize 0;
set trimspool on;
set linesize 200;
set line 3000;
set serveroutput off;

select * from stu where rownum<5 ;

--下面语句包含空行,使用/
select *
\
from stu where rownum<5 ;

EOF
3.2.2 简写方式执行
#简写方式执行
sql="
set heading off;
set feedback off;
set termout off;
set echo off;
set verify off;
set colsep '|';
set trimout on;
set pagesize 0;
set trimspool on;
set linesize 1200;
set line 3000;
set serveroutput off;
select * from stu where rownum<5 ;
"
rs=`echo "${sql}" | /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlplus -s school_user/root `
echo ${rs}
3.2.3 指定sql文件执行

这里同时示例结合spool导出数据到文件中。

sql文件spool_stu.sql如下:

set echo off;
set heading off;
set feedback off;
set termout on;
set trimspool off;
-- 通过spool导出数据到指定文件中
spool /home/oracle/docker_oracle_space/sqlplus/stu_spool.csv
    select id||','||no||','||name from stu;
spool off
exit

指定sql文件执行:

#指定sql文件执行
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlplus school_user/root  @/home/oracle/docker_oracle_space/sqlplus/spool_stu.sql
3.2.3 含子程序执行
#sqlplus含子程序执行
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/sqlplus -s school_user/root@127.0.0.1:1521/helowin <<EOF
set heading off;
set feedback off;
set termout off;
set echo off;
set verify off;
set colsep '|';
set trimout on;
set pagesize 0;
set trimspool on;
set linesize 200;
set line 3000;
set serveroutput off;
set serveroutput on;
set sqlblanklines on;

begin
   DBMS_OUTPUT.PUT_LINE('hello');
end;
/
select * from stu where rownum<5 ;
EOF
  • 1
    点赞
  • 6
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论

打赏作者

panda-star

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值