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,则下载出的这条记录的格式将会错乱,不宜采用此方法
阅读(1114) | 评论(0) | 转发(0) |