4 工具
所有参见内容都在附件04_tool/下。
在《优化》一节中讨论以下工具使用的效率。
4.1 sqlldr
参见sqlldr/。
用于将格式化的文本数据上载到表中去
以表emp为例
首先编写一个控制命令的脚本文件，通常以ctl结尾，内容如下：
emp.ctl
load data
append
into table emp
fields terminated by '|'
(
no float external,
name char(20),
age integer external,
duty char(1),
salary float external,
upd_ts date(14) 'YYYYMMDDHH24MISS'
)
括号里对数据文件里每个数据域进行解释，以此在上载时与目标表进行比对。
除了append外，还有insert、replace、truncate等方式，与append大同小异，不作更多的解释。
再将上载数据组织成数据文件，通常以dat结尾，内容如下：
emp.dat
100000000001|Tom|000020|1|000000005000|20020101000000
100000000002|Jerry|000025|2|000000008000|20020101235959
分隔符要与ctl文件中fields terminated by指定的一致，这个例子中为"|"
ctl和dat文件就绪后可以执行上载，命令为：
sqlldr dbuser/oracle control=emp.ctl data=emp.dat
也可以将dat文件合并在ctl文件中，ctl文件改写为：
emp2.ctl
load data
infile *
append
into table emp
fields terminated by '|'
(
no float external,
name char(20),
age integer external,
duty char(1),
salary float external,
upd_ts date(14) 'YYYYMMDDHH24MISS'
)
begindata
100000000003|Mulder|000020|1|000000005000|20020101000000
100000000004|Scully|000025|2|000000008000|20020101235959
控制文件中infile选项跟sqlldr命令行中data选项含义相同，如使用infile *则表明数据在本控制文件以begin data开头的区域内。
这样命令变成：
sqlldr dbuser/oracle control=emp2.ctl
conventional path
通过常规通道方式上载。
rows：每次提交的记录数
bindsize：每次提交记录的缓冲区
readsize：与bindsize成对使用，其中较小者会自动调整到较大者
sqlldr先计算单条记录长度，乘以rows，如小于bindsize，不会试图扩张rows以填充bindsize；如超出，则以bindsize为准。
命令为：
sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000
direct path
通过直通方式上载，不进行SQL解析。
命令为：
sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true
4.2 exp
参见dmp/exp_demo.sh。
将数据库内的各对象以二进制方式下载成dmp文件，方便数据迁移。
buffer：下载数据缓冲区，以字节为单位，缺省依赖操作系统
consistent：下载期间所涉及的数据保持read only，缺省为n
direct：使用直通方式 ，缺省为n
feeback：显示处理记录条数，缺省为0，即不显示
file：输出文件，缺省为expdat.dmp
filesize：输出文件大小，缺省为操作系统最大值
indexes：是否下载索引，缺省为n，这是指索引的定义而非数据，exp不下载索引数据
log：log文件，缺省为无，在标准输出显示
owner：指明下载的用户名
query：选择记录的一个子集
rows：是否下载表记录
tables：输出的表名列表
下载整个实例
exp dbuser/oracle file=oradb.dmp log=oradb.log full=y consistent=y direct=y
user应具有dba权限
下载某个用户所有对象
exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000
下载一张或几张表
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000
下载某张表的部分数据
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1 buffer=4096000 feedback=10000 query=\"where col1=\'…\' and col2 \<…"
不可用于嵌套表
以多个固定大小文件方式下载某张表
exp dbuser/oracle file=1.dmp,2.dmp,3.dmp,… filesize=1000m tables=emp buffer=4096000 feedback=10000
这种做法通常用在：表数据量较大，单个dump文件可能会超出文件系统的限制
直通路径方式
direct=y，取代buffer选项，query选项不可用
有利于提高下载速度
consistent选项
自export启动后，consistent=y冻结来自其它会话的对export操作的数据对象的更新，这样可以保证dump结果的一致性。但这个过程不能太长，以免回滚段和联机日志消耗完
4.3 imp
参见dmp/imp_demo.sh。
将exp下载的dmp文件上载到数据库内。
buffer：上载数据缓冲区，以字节为单位，缺省依赖操作系统
commit：上载数据缓冲区中的记录上载后是否执行提交
feeback：显示处理记录条数，缺省为0，即不显示
file：输入文件，缺省为expdat.dmp
filesize：输入文件大小，缺省为操作系统最大值
fromuser：指明来源用户方
ignore：是否忽略对象创建错误，缺省为n，在上载前对象已被建立往往是一个正常现象，所以此选项建议设为y
indexes：是否上载索引，缺省为n，这是指索引的定义而非数据，如果上载时索引已建立，此选项即使为n也无效，imp自动更新索引数据
log：log文件，缺省为无，在标准输出显示
rows：是否上载表记录
tables：输入的表名列表
touser：指明目的用户方
上载整个实例
imp dbuser/oracle file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000
上载某个用户所有对象
imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000
上载一张或几张表
imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000
以多个固定大小文件方式上载某张表
imp dbuser/oracle file=\(1.dmp,2.dmp,3.dmp,…\) filesize=1000m tables=emp fromuser=dbuser touser=dbuser2 buffer=4096000 commit=y ignore=y feedback=10000
4.4 sqlplus
参见sqlplus/download.sh。
仅列出常用的选项，对复杂的应用不作深究
4.4.1 命令行参数
/ as {sysdba|sysopr}：使用操作系统用户验证，以osdba或osopr一员的身份登录，如验证通过，被赋予sysdba或sysopr的权限
使用格式：sqlplus "/ as sysdba"
/nolog：不执行connect操作，直接进入sqlplus操作界面
-s：silent模式，不显示sqlplus启动信息和提示符
<：接受sql脚本从标准输入重定向
<<：立即文档
4.4.2 提示符命令
accept variable [number|char|date] [format format] [default default] [prompt text] [hide]：接受输入变量
例子：accept pwd char format a8 prompt 'Password:' hide
column column [format format] [heading heading]：设定对某个域的显示格式
如果要同时改变某域的输出长度和标题，必须使用column命令
见emp的定义，name本为char(20)，输出缩为10位，duty本为 char(1)，扩张为6位，以便有足够的空间显示中文标题。
SQL>column name format a10 heading '姓名';
SQL>column duty format a6 heading '职位';
SQL>column age format 999999 heading '年龄';
SQL>column upd_ts format a14 heading '更新时间';
SQL>select name,duty,age,upd_ts from emp;
show option：显示SET的选项
spool [filename|off]：输出重定向文件
timing [start text|show|stop]：定时器
4.4.3 SET选项
autocommit：自动提交insert、update、delete带来的记录改变，缺省为off
colsep：域输出分隔符
define：识别命令中的变量前缀符，缺省为on，也就是'&'，碰到变量前缀符，后面的字符串作为变量处理
如果待更新内容包含'&'（在URL中很常见），而define非设为off，sqlplus会把'&'后面紧跟的字符串当成变量，提示输入，这里必须重新输入'&'和那个字符串，才能实现正常更新。将define设为off，就不再进行变量判断。
SQL>set define off;
SQL>update bbs_forum set url='' where forum_id=1;
echo：显示start启动的脚本中的每个sql命令，缺省为on
feedback：回显本次sql命令处理的记录条数，缺省为on
heading：输出域标题，缺省为on
linesize：输出一行字符个数，缺省为80
如果一行输出超过linesize，会回车到第二行，这样格式就会混乱。
markup html：html格式输出，缺省为off
通常需要与spool配合，否则html输出就没有意义。
numwidth：输出number类型域长度，缺省为10
长number类型的域常常因为输出长度的问题，引起误会。
pagesize：输出每页行数，缺省为24
为了避免分页，可设定为0。
termout：显示脚本中的命令的执行结果，缺省为on
timing：显示每条sql命令的耗时，缺省为off
trimout：去除标准输出每行的拖尾空格，缺省为off
trimspool：去除重定向（spool）输出每行的拖尾空格，缺省为off
4.4.4 例子
以文本形式下载表数据
oracle缺乏将表中数据输出至文本文件的工具，因此只能利用sqlplus和unix工具做变通的处理
sqlplus -s dbuser/oracle </dev/null
set colsep |;
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool tmp.txt;
select * from emp;
spool off;
exit
EOF
tr -d ' ' < tmp.txt >emp.txt 删除空格，可选
注意：一定要用spool，如果在命令行中直接用>tmp.txt可能会造成数据缺失，至少在Unixware7上如此
假定某域是char(n)，如中间出现回车\n，则下载出的这条记录的格式将会错乱，不宜采用此方法
阅读(1105) | 评论(0) | 转发(0) |
