说明:
本文基于linux操作系统，通过安装sqlplus客户端，使用sqlload加载数据文件到oracle数据库，执行sql脚本进行粒度加工，最后通过spool工具导出数据文件到指定目录，由shell脚本主导的数据迁移过程
一、sqlplus安装
安装包下载:
注:请根据自身linux系统下载相应安装包，我这里是aarch64系统；可通过命令：uname -a 查看当前系统框架。
下载对应系统工具包:
oracle-instantclient19.10-basic-19.10.0.0.0-1.aarch64
oracle-instantclient19.10-sqlplus-19.10.0.0.0-1.aarch64
oracle-instantclient19.10-tools-19.10.0.0.0-1.aarch64
安装工具包命令:
rpm -ivh ./*
当前文件夹有其他安装包时请逐个安装，无先后之分
查看客户端目录:
whereis oracle #一般都是 /usr/lib/oracle
创建tnsnames.ora文件，此文件用于记录、创建dblink信息:
cd /usr/lib/oracle/19.10/client64
mkdir -p network/admin
vim tnsnames.ora
增加如下内容，创建dblink注意修改ip地址:
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IPADDR)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)编辑环境变量:
vim /etc/profile
在尾部增加如下变量:
export ORACLE_HOME=/usr/lib/oracle/19.10/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PATH编译环境变量使之生效:
source /etc/profile
测试连接远端oracle数据库(dwm用户):
sqlplus dwm/dwm@orcl二、sqlload导入数据
1.由于这里是测试和开发阶段表结构并没有固定，因此采用触发式采集表结构的方式导入数据。这里要导出表结构，因此先说一下导出数据工具spool
spool常用的设置：
- set head off：输出域标题，缺省为on
- set pagesize 0：输出每页行数，缺省为24，为了避免分页，可设定为0
- set feedback off：回显本次sql命令处理的记录条数，缺省为on
- set linesize 2000：行宽设置的稍微大点，防止数据被截断，导致导出文本尾部有太多空格
- set trimspool on：去除重定向（spool）输出每行的拖尾空格，缺省为off
#从数据库中提取列名，用逗号隔开，并对特殊类型做转换
sqlplus -S dwm/dwm@orcl<<EOF>/dev/null
set head off
set pagesize 0
spool /oracle/column_dwm.txt
desc table_name;
spool off
exit
EOF
sed -i '1,2d;$d' /oracle/column_dwm.txt
column_dwm=`cat /oracle/column_dwm.txt|awk '{print $1}'|xargs|sed 's/ /,/g'|sed "s/CRTTIME/crttime timestamp 'YYYY-MM-DD HH24:MI:SS.FF3'/g"`
#大部分数据类型均可自动插入，时间戳类型比较特殊需要注明数据类型2.使用sqlload工具将文本导入数据库需要进行两部操作：
第一步：生成 ctl 格式的控制文件
控制文件的组成部分：
- load data
- infile 'data_file.txt' --指定数据文件，可以指定多个文件
- insert into table_name --以何种方式加载，insert要求表数据为空
- fields terminated by ',' --被加载数据文件的分隔符，这里以逗号为例
- (col1,col2,col3...) --导入表的列名，注意顺序
加载数据时(基础第三步) insert 可根据需要更换加载方式：
append --追加插入，相当于增量插入
replace --替换插入，针对表中有键值约束的插入方式
truncate --清表插入，相当于全量插入
以上五步为控制文件的基础步骤，根据实际需求会增加不同的内容
OPTIONS(...) --加载数据时的参数设置和命令显示
CHARACTERSET 'UTF8' --指定编码格式
TRAILING NULLCOLS --表的字段没有对应值时允许为空
OPTIONALLY ENCLOSED BY ' " ' --数据中的包围符
以下是shell中自动追加控制文件的样例
#此处需延用上一部生成的列名
echo "(${column_dwm})" >/oracle/ctl/table_name.ctl
sed -i '1i TRAILING NULLCOLS' /oracle/ctl/table_name.ctl
sed -i '1i FIELDS TERMINATED BY ','" /oracle/ctl/table_name.ctl
sed -i '1i TRUNCATE INTO TABLE TABLE_NAME' /oracle/ctl/table_name.ctl
sed -i "1i INFILE 'data_file_path'" /oracle/ctl/table_name.ctl
sed -i '1i CHARACTERSET 'UTF8'' /oracle/ctl/table_name.ctl
sed -i '1i LOAD DATA' /oracle/ctl/table_name.ctl
sed -i '1i OPTIONS(readsize=270900000,bindsize=270900000,rows=10000,parallel=true,errors=1,silent=(feedback))' /oracle/ctl/table_name.ctlbindsize 每次提交记录的缓冲区的最大值，满存则提交，默认256000 Bytes
rows 每次提交的行数，默认6，最大值65534
通过bindsize的设定，要比默认值和通过参数rows计算的缓冲区大小更优先，即两者是相互制约的关系
行长*rows>bindsize则以bindsize大小提交，相反则按rows设定值提交，设定时应同时考虑两个参数的设定值
parallel 执行并行加载
readsize 为读取数据的大小
errors 允许的错误的数目
silent 运行中隐藏消息，feedback每次提交信息
代码中的bindsize和rows的参数设定是通过对其中一张宽表的测试得出的最优值。从下图，log文件中标红色的部分可以算出行长，根据行长预设想要提交的行数及bindsize参数。通过调整参数测出加载数据的时长，进而得出最优参数
第二步：加载数据
sqlldr导入命令格式：
sqlldr dwm/dwm@orcl control=/oracle/ctl/table_name.ctl log=/oracle/logs/table_name_dwm.log bad=/oracle/logs/table_name_dwm.bad这里可以设置log和bad文件的保存路径，日志文件中可以看到相关控制文件中的部分参数以及报错信息，bad文件中保存加载税局过程中的报错数据
三、执行sql脚本，进行数据的粒度加工
由于此步骤需要跨用户，且是远程操作，即数仓上面的跨层进行数据加工，因此需要角色拥有对其他用户表查询的权限。以dwm到dws两个用户为例，数据加载到dws层势必会用到dwm层的表数据，确保表存在的情况下，如果权限不足则报如下图错误，可以预先尝试跨用户对表进行 DML 操作
查看角色拥有的系统权限：
select * from role_sys_privs;
查看用户拥有的系统权限：
select * from user_sys_privs;
在本地客户端以管理员身份授予用户权限：
grant select any table to dwm;
grant select any table to dws;
为了方便跨用户进行DML操作，我这里赋予用户所有权限；
grant all privileges to dwm;
grant all privileges to dws;
下面就可以执行sql脚本进行数据加工
sqlplus dws/dws@orcl<<EOF>/oracle/logs/table_name_dws.log
@/oracle/sql_file/table_name.sql;
commit;
exit
EOF
这里可以直接使用sqlplus采用@脚本存放路径来执行sql脚本，标准输出到log文件，以便查看报错信息；进行 DML 操作时注意commit
四、导出数据
1.为了输出规范的、符合格式要求的数据文件，需要再select时用字符串连接老规范格式。比如要输出 ‘1，张三，18，广东深圳’ 这样的数据格式，就需要用select id||','||name||','||age||','||addr from emp这样的语句。因此，需要触发输出表字段拼接的字符串
#提取卸数层表字段，并用“||','||”拼接
sqlplus -S dws/dws@orcl <<EOF>/dev/null 2>&1
set head off
set pagesize 0
spool /oracle/column_dws.txt
desc table_name;
spool off
exit
EOF
sed -i '1,2d;$d' /oracle/column_dws.txt
column_dws=`cat /oracle/column_dws.txt|awk '{print $1}'|xargs|sed 's/ /||'\',\''||/g'`2.导出数据
spool导出数据命令格式：
sqlplus -S dws/dws@orcl <<EOF>/dev/null
set head off
set trimspool on
set feedback off
set pagesize 0
set linesize 2500
set arraysize 5000
spool /oracle/unload/table_name.txt
select ${column_dws} from table_name;
spool off
exit
EOF导出数据结束！
另外再说几个特殊报错：
1.SQLLDR导入数据，字段长度默认是char的极限值 255 字节，如果数据长度超过这个值就要注明长度，扩容。如字段：khzwm char(1000)
2.如果表字段与select，from，group等关键字重名，建表时字段要加双引号。查询时同样也要加双引号不然报错
3.ORA-00923：FROM keyword not found where expected
“关键字未发现”----有可能时手误写错如：FROM误写成FORM。这属于常规报错，但当你语法都改完之后还报这个错就要注意了，由于oracle存储中文默认是UTF-8格式，当有中文注释时就可能出现格式问题，导致这个报错，我这里是把SQL脚本改为UTF-8格式完美解决。
4.SP2-0734：unknown command beginning ''....'' -rest of line ignored
“未知的命令开头...”----出现这个错误一般都是格式的问题或是注释的问题
①将SQL文件的语句中的所有中文注释改为英文注释（这个备选优先下面方法）
②SQL文件开头设置参数：set sqlblanklines on，这是因为有空行导致sql语句加载到机器内存中截断了，一般设置这个参数就能解决问题
365
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
