韩国dr.oracle怎么样孕妇能用么
点击联系发帖人
时间：2016-08-08 20:25
1682人阅读
SAP Basis（7）
SAPOracleservice
DEVQASPRDFTP
brarchivebrarchivebrarchivebrbackupSAPbrarchivebrarchivebrbackupSAPbrbrbrarchive
OS SuSE Linux Enterprise 9.3
修改/etc/fstab，添加这一行
saperpdev:/prddbarch&&& /oracle/PRD/oraarch2&&& nfs rsize=32768,wsize=32768,hard 0 0
注：请先行在第二镜像点创建目录，目录权限均为ora&sid&:dba
修改init&SID&.ora，将参数设为如下
filesystemio_options&&&& = setall
disk_asynch_io&&&&&&&&&& = true
log_archive_dest_1 = "LOCATION=/oracle/PRD/oraarch/PRDarch MANDATORY REOPEN"
log_archive_dest_2 = "LOCATION=/oracle/PRD/oraarch2/PRDarch REOPEN"
创建远程数据库连接的口令文件。
orapwd -file=/oracle/&SID&/102_64/dbs/PWD&SID&.ora password=xxxx entries=100
重启数据库后，主系统就会写往两个点写日志了。
修改/etc/fstab，
mirrprd:/oracle/PRD/mirrTrans /oracle/DEV/mirrTrans/standby nfs defaults 0 0
把镜像机器上的处理目录挂到第二日志点的传输脚本目录的standby目录下。
修改/oracle/&SID&/102_64/network/admin/tnsnames.ora
PRIMARYPRD.WORLD =
& (DESCRIPTION =
&&& (ADDRESS_LIST =
&&&&& (ADDRESS =
&&&&&&& (COMMUNITY = SAP.WORLD)
&&&&&&& (PROTOCOL = TCP)
&&&&&&& (HOST = &Your Primary DB IP or hostname&)
&& &&&&&(PORT = 1527)
&&& (CONNECT_DATA =
&&&&& (SID = &Your primary DB SID&)
&&&&& (GLOBAL_NAME = PRD.WORLD)
重启listener后，tnsping primaryprd看一下结果，是否通了。
在/oracle/&SID&下创建传输日志的工作目录，目录结构如下
/oracle/&SID&/mirrTrans&&&& 工作目录
/oracle/&SID&/mirrTrans/logs 传输脚本产生的日志将会发在这个目录下
/oracle/&SID&/mirrTrans/primary 查询主系统的最新日志更新序列后产生的序列号文件
/oracle/&SID&/mirrTrans/standby 镜像系统日志处理目录，挂接到第二日志点的目录中
在此目录下生成search_seq.sql文件
connect system/&password&@PRIMARYPRD
set tab off
set pagesize 0
set linesize 50
spool primary/primary.sequence
select max(sequence#) from v$log_history
测试一下运行的情况
sqlplus /nolog @search_seq.sql
cat primary/primary.sequence
你会看到形如1234这样的数字，这代表了当前主系统更新到的号。
创建transfer_log.sh
ORACLE_HOME=/oracle/DEV/102_64
export ORACLE_HOME
WORK_DIR=/oracle/DEV/mirrTrans
STANDBY_DIR=$WORK_DIR/standby
PRIMARY_DIR=$WORK_DIR/primary
ARCH_DIR=/prddbarch
TRANSFER_FILE_LIST=""
UNTRANSFER_FILE_LIST=""
WORK_IN_LAN=0
START_TIME=`date "+%F_%T"`
LOGFILE_NAME=$WORK_DIR/logs/transfer_log_$START_TIME.log
STATUS_LOG=$WORK_DIR/transfer_status_`date "+%Y%m"`.log
log_status(){
& echo $1 && $STATUS_LOG
& echo $1 && $LOGFILE_NAME
log_space(){
log_separator(){
& log "========================================================================="
generate_file_list(){
& start=$1
& start=`expr $start + 1`
& while [ $start -le $end ]
&& dbf=PRDarch1_"$start"_.dbf
&& echo $dbf
&& if test -f $STANDBY_DIR/$dbf || test -f $STANDBY_DIR/$dbf.bz2
&&&&& UNTRANSFER_FILE_LIST=$UNTRANSFER_FILE_LIST" "$dbf
&&&&& TRANSFER_FILE_LIST=$TRANSFER_FILE_LIST" "$dbf
&& start=`expr $start + 1`
& log_space
& log "[info] Files following have been transferred to standby site"
& for file_name in $UNTRANSFER_FILE_LIST
&&& log "[info] $file_name ..."
& log_space
& log "[info] Files following need to transfer to standby site"
& if [ "$TRANSFER_FILE_LIST" != "" ]
&&& size=0
&&& for file_name in $TRANSFER_FILE_LIST
&&&& log "[info] $file_name ..."
&&&& file_size=`ls -l $ARCH_DIR/$file_name|awk '{print $5}'`
&&&& size=`expr $size + $file_size`
&&& log "[info] Total size is: `echo $size|awk '{printf "%4.2f", $1/}'`MB"
&&& log "[info] All files have been transferred to the standby site"
& log_space
transfer_file(){
log "[info] Timestamp: `date '+%F %T'`"
log "[info] Transfer file $1 via FTP"
ftp -i -v -n mirrprd&&EOF && $LOGFILE_NAME
user &ftpuser& &ftppassword&
cd /oracle/PRD/mirrTrans
touch $STANDBY_DIR/$1.end
log "[info] Timestamp: `date '+%F %T'`"
log "[info] Transfer file $1 done"
log "[info] Delete $1, because it has been transferred to standby site"
log "[info] Delete $1 done"
process_file(){
& log_space
& if [ $WORK_IN_LAN = 1 ]
&&& echo "[info] Work in LAN, transfer file directly"
&&& transfer_file $1
&&& log "[info] Work not in LAN, compress it first"
&&& log "[info] Timestamp: `date '+%F %T'`"
&&& bzip2 $1
&&& log "[info] Timestamp: `date '+%F %T'`"
&&& log "[info] Compress done"
&&& transfer_file $1.bz2
& log_space
end_job(){
& log_status "$START_TIME|transferLog|$1"
& log "[info] Timestamp: `date '+%F %T'`"
& log "[info] Transfer logs completed $2"
& log_separator
& rm transfer.lck
cd $WORK_DIR
if test -f transfer.lck
&& log "[info] A process is running, quit this one now"
log "[info] Mirror Logs Transfer (1.0)"
log "[info] Start of processing at: $START_TIME"
log "[info] Parameters"
log "[info] WORK_DIR:&&&&&&&&&&&&&&&&&& $WORK_DIR"
log "[info] PRIMARY_DIR:&&&&&&&&&&&&&&& $PRIMARY_DIR"
log "[info] STANDBY_DIR:&&&&&&&&&&&&&&& $STANDBY_DIR"
log "[info] File transfer via:&&&&&&&&& FTP"
log "[info] I. To make sure there is only one process running to transfer the file"
log "[info] Register this process"
touch transfer.lck
log "[info] Register done"
log "[info] Get the latest archived log sequence of Primary DB"
log "[exec] sqlplus /nolog @search_seq.sql"&
$ORACLE_HOME/bin/sqlplus /nolog @search_seq.sql &&$LOGFILE_NAME
log "[info] Get sequence done"
read PRIMARY_SEQUENCE & $PRIMARY_DIR/primary.sequence
log "[info] PRIMARY_SEQUENCE:&&&&&&&&& $PRIMARY_SEQUENCE"
read STANDBY_SEQUENCE & $STANDBY_DIR/standby.sequence
log "[info] STANDBY_SEQUENCE:&&&&&&&&& $STANDBY_SEQUENCE"
if [ $STANDBY_SEQUENCE = $PRIMARY_SEQUENCE ]
&& log "[info] Standby has updated to the latest sequence with Primary, don't do anything"
&& end_job 1 "without doing anything"
generate_file_list $STANDBY_SEQUENCE $PRIMARY_SEQUENCE
if [ "$TRANSFER_FILE_LIST" == "" ]
&& log "[info] No new logs need to transfer, do nothing and exit"
&& end_job 2 "without doning anything"
cd $ARCH_DIR
for FILE_NAME in $TRANSFER_FILE_LIST
& process_file $FILE_NAME
cd $WORK_DIR
end_job 0 "successfully"
这个脚本有个参数WORK_IN_LAN，当把它设为1时，表示工作在同一内网中，则需要传输的日志不会被压缩，直接发送即可。
请参看以下主流程的图例。
之所以有一个检查tranfer.lck的动作，是因为万一上一个传输动作没有完成，则这次的传输自动停止，以免发生冲突。所以第一次运行时，需要在镜像点那边先获取镜像数据库的序列号，并
cat 1234 & standby.sequence
以下是处理文件的流程。
每次传输完一个文件后，都会在镜像系统的对应目录下生成一个以.end为结尾的文件，表示传输完成。
运行这个脚本，sh transfer_log.sh，会在logs目录下产生一个这样的日志transfer_log__13:40:01.log
[info] Mirror Logs Transfer (1.0)
[info] Start of processing at: _13:40:01
[info] Parameters
[info] WORK_DIR: /oracle/DEV/mirrTrans
[info] PRIMARY_DIR: /oracle/DEV/mirrTrans/primary
[info] STANDBY_DIR: /oracle/DEV/mirrTrans/standby
[info] File transfer via: FTP
[info] I. To make sure there is only one process running to transfer the file
[info] Register this process
[info] Register done
[info] Get the latest archived log sequence of Primary DB
[exec] sqlplus /nolog @search_seq.sql
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Apr 11 13:40:01 2008
Copyright (c) , Oracle.& All Rights Reserved.
Connected.
&&&&&&&&& 9669
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
[info] Get sequence done
[info] PRIMARY_SEQUENCE: 9669
[info] STANDBY_SEQUENCE: 9665
[info] Files following have been transferred to standby site
[info] PRDarch1_12.dbf ...
[info] PRDarch1_12.dbf ...
[info] Files following need to transfer to standby site
[info] PRDarch1_12.dbf ...
[info] PRDarch1_12.dbf ...
[info] Total size is: 387.64MB
[info] Work not in LAN, compress it first
[info] Timestamp:
[info] Timestamp:
[info] Compress done
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 via FTP
Connected to mirrprd.
220---------- Welcome to Pure-FTPd [TLS] ----------
220-You are user number 1 of 50 allowed.
220-Local time is now 13:41. Server port: 21.
220-IPv6 connections are also welcome on this server.
220 You will be disconnected after 15 minutes of inactivity.
331 User Nippon OK. Password required
230-User Nippon has group access to:& ftp&&&&& video&&& audio&&& dialout
230- uucp&&&
230 OK. Current directory is /home/Nippon
Remote system type is UNIX.
Using binary mode to transfer files.
200 TYPE is now 8-bit binary
250 OK. Current directory is /oracle/PRD/mirrTrans
local: PRDarch1_12.dbf.bz2 remote: PRDarch1_12.dbf.bz2
229 Extended Passive mode OK (|||23429|)
150 Accepted data connection
226-30556.3 Mbytes free disk space
226-File successfully transferred
226 122.915 seconds (measured here), 451.40 Kbytes per second
bytes sent in 02:02 (451.47 KB/s)
221-Goodbye. You uploaded 55484 and downloaded 0 kbytes.
221 Logout.
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 done
[info] Delete PRDarch1_12.dbf.bz2, because it has been transferred to standby site
[info] Delete PRDarch1_12.dbf.bz2 done
[info] Work not in LAN, compress it first
[info] Timestamp:
[info] Timestamp:
[info] Compress done
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 via FTP
Connected to mirrprd.
220---------- Welcome to Pure-FTPd [TLS] ----------
220-You are user number 1 of 50 allowed.
220-Local time is now 13:44. Server port: 21.
220-IPv6 connections are also welcome on this server.
220 You will be disconnected after 15 minutes of inactivity.
331 User Nippon OK. Password required
230-User Nippon has group access to:& ftp&&&&& video&&& audio&&& dialout
230- uucp&&&
230 OK. Current directory is /home/Nippon
Remote system type is UNIX.
Using binary mode to transfer files.
200 TYPE is now 8-bit binary
250 OK. Current directory is /oracle/PRD/mirrTrans
local: PRDarch1_12.dbf.bz2 remote: PRDarch1_12.dbf.bz2
229 Extended Passive mode OK (|||26613|)
150 Accepted data connection
226-30497.2 Mbytes free disk space
226-File successfully transferred
226 135.808 seconds (measured here), 445.02 Kbytes per second
bytes sent in 02:15 (445.09 KB/s)
221-Goodbye. You uploaded 60438 and downloaded 0 kbytes.
221 Logout.
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 done
[info] Delete PRDarch1_12.dbf.bz2, because it has been transferred to standby site
[info] Delete PRDarch1_12.dbf.bz2 done
[info] Timestamp:
[info] Transfer logs completed successfully
=========================================================================
再到镜像系统查看，在相应的目录下，应该会有一堆.bz2文件。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：24634次
排名：千里之外
原创：10篇
(1)(1)(1)(1)(6)查看: 5736|回复: 22
enq: DR - contention 是个什么等待，谢谢
论坛徽章:7
enq: DR - contention 是个什么等待，谢谢
oracle10g 10.2.0.3 RAC
hp-ux 11.23
第二个节点
select * from v$session_wait where wait_class && 'Idle';
& && && && && & sid& && && && &seq#& && & event
1& & & & 3293& & & & 7& & & & enq: DR - contention& & & & name|mode& & & & & & & & 0006& & & & 0& & & & 0& & & & 00& & & & 0& & & & 0& & & & 00& & & & & & & & 0& & & & Other& & & & 0& & & & 179143& & & & WAITING
然后通过select * from v$session where sid = 3293;
1& & & & CDCCEA68& & & & 3293& & & & 1& & & & 0& & & & CF508& & & & 0& & & & & & & & 0& & & & & & & & & & & & CD2CE0& & & & ACTIVE& & & & DEDICATED& & & & 0& & & & SYS& & & & oracle& & & & 2185& & & & hisdb02& & & & UNKNOWN& & & & oracle@hisdb02 (RECO)& & & & BACKGROUND& & & & 00& & & & 0& & & & & & & & 0& & & & 00& & & & 0& & & & & & & & 0& & & & & & & & & & & & & & & & & & & & & & & & 0& & & & & & & & 0& & & & & & & & 0& & & & -1& & & & 0& & & & 0& & & & 0& & & &
10:48:56& & & & 179219& & & & NO& & & & NONE& & & & NONE& & & & NO& & & & & & & & DISABLED& & & & DISABLED& & & & DISABLED& & & & 0& & & & & & & & VALID& & & & 1& & & & 3293& & & & 7& & & & 624& & & & enq: DR - contention& & & & name|mode& & & & & & & & 0006& & & & 0& & & & 0& & & & 00& & & & 0& & & & 0& & & & 00& & & & & & & & 0& & & & Other& & & & 0& & & & 179149& & & & WAITING& & & & SYS$BACKGROUND& & & & DISABLED& & & & FALSE& & & & FALSE
该会话竟然是 oracle@hisdb02 (RECO）
The recoverer background process (RECO)
RECO is a background process for distributed transactions. The RECO process manager two-phase commits to track and resolve in-doubt transactions.
该后台进程是用于分布式事务的。
有另外一台检验数据库通过dblink来获取该数据库数据的，
目前有两次大量的latch free产生，oracle 9206 ，latch#=98 buffer busy chains
不明白为什么会有这个等待，metalink也查不到
版主给个建议，谢谢
论坛徽章:7
论坛徽章:112
对于2PC的分布式事务,当因为某些原因,比如
1）数据库服务器异常[比如hang,crash,掉电[shutdown abort]
2）网络中断，超时
3）进程异常退出
& &&&比如kill -9
4）应用异常
等原因导致某一个阶段的事务失败,Oracle的reco进程会自动去处理这些失败的事务.但是在RECO完全解决该事务前，分布式事务中的dx[读/写] 锁不释放。
有些时候,可能因为某些原因,也需要人为的手工甘于一下，比如这些失败的2PC事务上正好在一些很关键的数据块上有lock,从而可能导致
更多的阻塞.为了不影响应用,这个时候就需要DBA介入去处理了.
论坛徽章:7
enq: DR - contention 是个什么等待
我怎么处理呢，我google ，metalink都找不到相应的处理方法
给个提示，谢谢
论坛徽章:112
这个要具体问题具体分析,你先看看dba_2pc_pending视图中有没有是否存在这样的分布式事务,状态是什么?
论坛徽章:7
有20多条，状态都是collecting
论坛徽章:7
都是12.19日切换以后开始的
论坛徽章:112
把这些都rollback force了吧
然后如果觉得这些东西在dba_2pc_pending里很不爽,你就调用DBMS_TRANSACTION包把这些事务purge掉吧(如果拿不准，就不要做这个动作了),找相应的support 工程师给你搞一下.
论坛徽章:7
如何 rollback force?
招聘 : 论坛徽章:66
原帖由 道可道非常道 于
15:56 发表
如何 rollback force?
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有
北京市公安局海淀分局网监中心备案编号： 广播电视节目制作经营许可证：编号（京）字第1149号oracle10g - Does Oracle really offer any OCI demo/sample programs? - Stack Overflow
to customize your list.
Announcing Stack Overflow Documentation
We started with Q&A. Technical documentation is next, and we need your help.
Whether you're a beginner or an experienced developer, you can contribute.
Oracle, says that there are some demo/sample programs for OCI.
In my case I'd like to find examples of using OCIConnectionPoolCreate().
There should be in a file called cdemocp.c, according to the documentation:
But its far from clear what I should install to get these files?
I would have hoped I could download them from Oracle's own website. Seems not. Certainly no handy link from the above page.
I have a tried installing (onto windows):
An Oracle 10g XE database server, no such source files
Oracle client (sdk options on), no such source files
searched within our linux installations -- where did I find the fabled \rdbms\demo\ folder which contains a few make files referring to the demo source files, the
but no source files. So running these makefiles fails.
Basically, no luck finding them.
If I google for cdemocp.c I find only:
the typical multiple copies of Oracle's own documentation
other people saying they cant find it.
( I finding using oracle's documentation is like lord of the rings, only a faint connection with reality, and probably too many words. I'm not enjoying it. )
OK found the OCI demo files. It was a serious pain to find them.
From Oracle, get some "Companion Disk". Such as:
"Oracle Database 10g Companion CD Release 2" :
For me all the various "Universal"
installers I tried crashed, or refused to run.
Even when run on the OS they were supposed to be run on.
But, having expanded the installer you can just dig out .jar files.
These .jar file are really just .zip files with a diffident extension. This file:
102010_win64_x64_companion\companion\Disk1\stage\Components\panion\10.2.0.3.0\1\DataFiles\filegroup2.jar
when extracted, contains all the OCI demo files I wanted under rdbms/demo/. Voilà.
( I list them here so anyone googling for them might find this page. )
cdemdpco.c cdemdpin.c cdemdpit.c cdemdplp.c
cdemdpno.c
cdemdpro.c
cdemdpss.c cdemo1.c cdemo2.c cdemo3.c cdemo4.c cdemo5.c cdemo81.c cdemo82.c cdemoanydata1.c cdemoanydata2.c cdemobj.c cdemocoll.c cdemocor.c cdemocor1.c cdemocp.c cdemocpproxy.c cdemodp.c cdemodp_lip.c cdemodr1.c cdemodr2.c cdemodr3.c cdemodsa.c cdemodsc.c cdemodt.c cdemoext.c cdemofil.c cdemofo.c cdemofor.c cdemoin1.c cdemoin2.c cdemoin3.c cdemol2l.c cdemolb.c cdemolb2.c cdemolbs.c cdemoplb.c cdemoqc.c cdemoqc2.c cdemorid.c cdemort.c cdemosc.c cdemoses.c cdemosp.c cdemostc.c cdemosyev.c cdemothr.c cdemoucb.c cdemoucbl.c cdemouni.c cdemoup1.c cdemoup2.c cdemoupk.c cdemoxml.c extdemo2.c extdemo4.c extdemo5.c extdemo6.c nchdemo1.c obndra.c oci02.c oci03.c oci04.c oci05.c oci06.c oci07.c oci08.c oci09.c oci10.c oci11.c oci12.c oci13.c oci14.c oci15.c oci16.c oci17.c oci18.c oci19.c oci20.c oci21.c oci22.c oci23.c oci24.c oci25.c ociaqarraydeq.c ociaqarrayenq.c ociaqdemo00.c ociaqdemo01.c ociaqdemo02.c ociucb.c readpipe.c strmmon.c
cdemo6.h cdemo82.h cdemobj.h cdemocoll.h cdemocor.h cdemodp.h cdemodp0.h cdemodr1.h cdemodr2.h cdemodr3.h cdemodsc.h cdemoin1.h cdemoin2.h cdemoin3.h cdemolb.h cdemorid.h cdemort.h cdemoses.h cdemosp.h cdemostc.h cdemothr.h extdemo2.h extdemo4.h extdemo5.h extdemo6.h mdemo1.h mymdemo1.h cdemo6.cc mdemo1.cpp mdemo1o.cpp occiaqlis.cpp occiaqop.cpp occiblob.cpp occiclob.cpp occicoll.cpp occidesc.cpp occidml.cpp occiinh.cpp occilbar.cpp occimb1.cpp occiobj.cpp occipobj.cpp occipool.cpp occiproc.cpp occiscp.cpp occistre.cpp occiuni1.cpp occiuni2.cpp
Update from above since some links changed
First download:
Then if you have 7zip on windows you can drill down to the zip and jar file like this:
Downloads\linux.x64_11gR2_examples.zip\examples\stage\Components\panion\11.2.0.1.0\1\DataFiles\filegroup2.jar\rdbms\demo\
3,071103355
After struggling to find the OCI sample programs for Oracle 12 on Windows, I found them here:
You need to log onto oracle and on the windows item, click "see all"
Microsoft Windows x64 (64-bit)
File 1, File 2
Oracle Database Examples (12.1.0.2.0) for Microsoft Windows (x64)
winx64_12102_examples.zip (576,214,828 bytes)
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled1868人阅读
数据库相关（13）
本来项目所用的数据库从Sybase转为Oracle, 导数据是用户他们自己的责任, 所以导数据我是不用管的, 也不在我的工作范围内, 但是有机会能学下BCP和SQLLDR还是很好的, 唉, 晚上的时间没啦! T.T (本人所用系统是XP)
在上篇文章 中, 把表结构先建立好, 但是先别建立主键, 外键, 索引, &并建立下面的文件夹.
bcp.txt, bcpout.bat, sqlldr.txt, sqlldrout.bat 放于: E:/sybase_temp/数据库数据文件放于: E:/sybase_temp/data/ctl文件放于: E:/sybase_temp/ctl/log文件放于: E:/sybase_temp/log/error_log文件放于: E:/sybase_temp/error/
其中: bcp.txt是生成bcpout.bat的脚本,& bcpout.bat是把sybase的用户表数据导出为文件的程序, sqlldr.txt是生成sqlldrout.bat的脚本, sqlldrout.bat是把导出的文件中的数据导入到oracle中的表里面的程序.
准备工作做好后, 那就开始啦~~~~~~~~~
1. 可用如下方法生成一个可以一次导出一个数据库中所有表的数据的执行脚本。编辑一个如下文本文件 文件名称例为 E:/sybase_temp/bcp.txt ：
use&database1& --用到的数据库
select 'bcp database1..' + name + ' out E:/sybase_temp/data/' + name + '.txt -U user1 -P&pw1 -S&server1 -c -t"|,&|"& -r "&~|&" ' from sysobjects where type = 'U' --bcp database1..CMS_CATEGORY out E:/sybase_temp/data/CMS_CATEGORY.txt -U user1 -P&dw1 -S&server1 -c -t"|,&|"& -r "&~|&"&&& -- -U 用户名 -P 密码 -S 服务器名 -t字段分隔符(默认是制表符/t, 不过还是要用复杂点的好) -r行分隔符(默认是回车符/n, 如果用默认的,字段中存在回车符将导致数据错误)go
注意: E:/sybase_temp/data 文件夹必须要存在才行在dos里面执行:isql -U&user1 -P pw1 -S&server1 -i E:/sybase_temp/bcp.txt -o E:/sybase_temp/bcpout.bati参数后为输入文件，o参数后文件为输入文件执行后得到的输出文件。执行后可以得到一个后缀名为 BAT 的批处理文件（在unix下则生成一个shell文件并更改相应的执行权限），可直接执行。执行后将在指定的目录下导出了相应的TXT(保存为TXT为个人喜好)数据文件。一个表的数据为一个文件。如在UNIX下则可不用BAT后缀。
2. 执行生成的bcpout.bat文件&& 在E:/sybase_temp/文件夹下面有很多文件被创建, 这些文件里面都是数据库表的数据, 检查数据库表的数量跟产生的文件的数量是否相符.
3.编辑文本 E:/sybase_temp/sqlldr.txt :
use database1--用到的数据库
select 'sqlldr
control=E:/sybase_temp/ctl/' + name + '.ctl& log=E:/sybase_temp/log/' + name + '.log bad=E:/sybase_temp/error/' + name + '_error.log& ' from sysobjects where type = 'U'
--sqlldr&& && control=E:/sybase_temp/ctl/CMS_CATEGORY.ctl& log=E:/sybase_temp/log/CMS_CATEGORY.log bad=E:/sybase_temp/error/CMS_CATEGORY_error.log
注意: E:/sybase_temp/ctl/,& E:/sybase_temp/log/ , E:/sybase_temp/error/ 文件夹必须要存在才行在dos里面执行:
isql -U&user1 -P&pw1 -S&server1 -i E:/sybase_temp/sqlldr.txt -o E:/sybase_temp/sqlldrout.bat
4. 在项目在写一个生成ctl文件的java类(还有很多方法的), sybase jdbc驱动是jconn2.jar
SybaseTableCtl.java
import&java.sql.*;
import&java.util.L
import&java.util.ArrayL
import&java.io.F
import&java.io.FileW
import&java.io.BufferedW
public&class&SybaseTableCtl&{
&&&&public&static&final&int&SYBASE_DATABASE&=&1;
&&&&public&static&final&String&SYBASE_DATETIME_TYPE&=&"datetime";
&&&&public&static&final&String&SYBASE_TIMESTAMP_TYPE&=&"timestamp";
&&&&private&int&databaseType&=&1;
&&&&public&int&getDatabaseType()&{
&&&&&&&&return&databaseT
&&&&public&void&setDatabaseType(int&databaseType)&{
&&&&&&&&this.databaseType&=&databaseT
&&&&public&Connection&getConn(){
&&&&&&&&try&{
&&&&&&&&&&&&
&&&&&&&&&&&&Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
&&&&&&&&&&&&
&&&&&&&&&&&&Connection&con&=&DriverManager.getConnection("jdbc:sybase:Tds:***.***.****.***:5000/database1?FAKE_METADATA=true&charset=cp936&jconnect_version=0",&"sa",&"");
&&&&&&&&&&&&return&
&&&&&&&&}catch(ClassNotFoundException&cnf){
&&&&&&&&&&&&System.out.println("driver¬&find:"+cnf);
&&&&&&&&&&&&return&null;
&&&&&&&&}catch(SQLException&sqle){
&&&&&&&&&&&&System.out.println("can?t&connection&db:"+sqle);
&&&&&&&&&&&&return&null;
&&&&&&&&}&catch&(Exception&e)&{
&&&&&&&&&&&&System.out.println("Failed&to&load&JDBC/ODBC&driver.");
&&&&&&&&&&&&return&null;
&&&&public&List&getTableNameList()&throws&Exception&{
&&&&&&&&List&tableNames&=&null;
&&&&&&&&String&sql&=&getTableSql();
&&&&&&&&if(sql&!=&null&&&&!"".equals(sql)){
&&&&&&&&&&&&Connection&conn&=&null;
&&&&&&&&&&&&Statement&stmt&=&null;
&&&&&&&&&&&&ResultSet&rs&=&null;
&&&&&&&&&&&&try{
&&&&&&&&&&&&&&&&conn&=&getConn();
&&&&&&&&&&&&&&&&if(conn&!=&null){
&&&&&&&&&&&&&&&&&&&&stmt&=&conn.createStatement();
&&&&&&&&&&&&&&&&&&&&rs&=&stmt.executeQuery(sql);
&&&&&&&&&&&&&&&&&&&&if(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&tableNames&=&new&ArrayList();
&&&&&&&&&&&&&&&&&&&&&&&&tableNames.add(rs.getString(1).toUpperCase());
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&while(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&tableNames.add(rs.getString(1).toUpperCase());
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&}else&return&null;
&&&&&&&&&&&&}catch(Exception&e){
&&&&&&&&&&&&&&&&e.printStackTrace();
&&&&&&&&&&&&&&&&return&null;
&&&&&&&&&&&&}finally{
&&&&&&&&&&&&&&&&if(conn&!=&null)&conn.close();
&&&&&&&&&&&&}
&&&&&&&&return&tableN
&&&&public&void&createCtlTxt(String&dataFilePath,String&dataExt,&String&outFilePath,&String&ext,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&String&field_terminator,&String&row_terminator)&throws&Exception&{
&&&&&&&&Connection&conn&=&null;
&&&&&&&&Statement&stmt&=&null;
&&&&&&&&ResultSet&rs&=&null;
&&&&&&&&try{
&&&&&&&&&&&&conn&=&getConn();
&&&&&&&&&&&&if(conn&!=&null){
&&&&&&&&&&&&&&&&stmt&=&conn.createStatement();
&&&&&&&&&&&&&&&&List&tables&=&getTableNameList();
&&&&&&&&&&&&&&&&if(tables&==&null){
&&&&&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&getTableNameList为null,&查询不到用户表");
&&&&&&&&&&&&&&&&&&&&return&;
&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&String&tableName&=&null;
&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&String&sql&=&"select&b.name,(select&name&from&systypes&where&usertype&=&b.usertype)&type_name&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"&from&sysobjects&a,&syscolumns&b&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"&where&a.type&=&'U'&and&a.id&=&b.id&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"and&a.name&='";
&&&&&&&&&&&&&&&&String&txt&=&"";
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&sql&="&+&sql);
&&&&&&&&&&&&&&&&boolean&doCretat&=&false;
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&用户表数目:&"&+&tables.size());
&&&&&&&&&&&&&&&&int&num&=&0;
&&&&&&&&&&&&&&&&for(int&i=0;&i&&&tables.size();&i++){
&&&&&&&&&&&&&&&&&&&&tableName&=&(String)&tables.get(i);
&&&&&&&&&&&&&&&&&&&&rs&=&stmt.executeQuery(sql&+&tableName&+&"'&");
&&&&&&&&&&&&&&&&&&&&txt&=&"load&&&data /n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"infile&&&'"&+&dataFilePath&+&File.separator&+&&tableName&+&"."&+&dataExt&+&"'&&/"str&'"&+&row_terminator&+&"'/"/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&truncate&&&into&&&table&&"&+&tableName&+&"&/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&fields&&&terminated&&&by&&&'"&+&field_terminator&+&"'&/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&trailing&nullcols&/n"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"(";
&&&&&&&&&&&&&&&&&&&&String&_txt&=&"";
&&&&&&&&&&&&&&&&&&&&while(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&doCretat&=&true;
&&&&&&&&&&&&&&&&&&&&&&&&String&field&=&rs.getString(1);
&&&&&&&&&&&&&&&&&&&&&&&&if(field&!=&null&&&&!"".equals(field)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&field.toUpperCase();
&&&&&&&&&&&&&&&&&&&&&&&&&&&&if(rs.getString(2)&!=&null&&&&rs.getString(2).equals(SybaseTableCtl.SYBASE_DATETIME_TYPE)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&"&/"TO_DATE(TO_CHAR(TO_TIMESTAMP(:"&+&field&+&",&'MON-DD-YYYY&HH:MI:SS:FF&AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON&DD&YYYY&HH24:MI:SS'),'MON&DD&YYYY&HH24:MI:SS')/"&,";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&&&&&&&&&else&if(rs.getString(2)&!=&null&&&&rs.getString(2).equals(SybaseTableCtl.SYBASE_TIMESTAMP_TYPE)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&"&TIMESTAMP&'YYYY-MM-DD&HH:MI:SS:FF&AM'&,";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}else{
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&",";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&if(_txt&!=&null&&&&!"".equals(_txt)){
&&&&&&&&&&&&&&&&&&&&&&&&_txt&=&_txt.trim();
&&&&&&&&&&&&&&&&&&&&&&&&_txt&=&_txt.substring(0,_txt.length()&-1);&&
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&txt&+=&_txt&+&")";
&&&&&&&&&&&&&&&&&&&&if(doCretat){
&&&&&&&&&&&&&&&&&&&&&&&&File&outputFile&=&new&File(outFilePath&+&File.separator&+&tableName&+&"."&+&ext);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&FileWriter&out&=&new&FileWriter(outputFile,&false);
&&&&&&&&&&&&&&&&&&&&&&&&BufferedWriter&bufferOut&=&new&BufferedWriter(out);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut.write(txt);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut.close();
&&&&&&&&&&&&&&&&&&&&&&&&out.close();
&&&&&&&&&&&&&&&&&&&&&&&&outputFile&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&out&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&num&++;
&&&&&&&&&&&&&&&&&&&&}else{
&&&&&&&&&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&:&"&+&tableName&+&"no&ctl");
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&ctl文件建立数目:&"&+&num);
&&&&&&&&&&&&};
&&&&&&&&}catch(Exception&e){
&&&&&&&&&&&&e.printStackTrace();
&&&&&&&&}finally{
&&&&&&&&&&&&if(conn&!=&null)&conn.close();
&&&&public&String&getTableSql(){
&&&&&&&&if(getDatabaseType()&==&SybaseTableCtl.SYBASE_DATABASE){
&&&&&&&&&&&&return&"select&name&from&sysobjects&where&type&=&'U'&";
&&&&&&&&}else&return&"";
&&&&public&static&void&main(String[]&args)&throws&Exception&{
&&&&&&&&SybaseTableCtl&ctr&=&new&SybaseTableCtl();
&&&&&&&&ctr.setDatabaseType(SybaseTableCtl.SYBASE_DATABASE);
&&&&&&&&ctr.createCtlTxt("E://sybase_temp//data","txt","E://sybase_temp//ctl","ctl","|,&|",&"&~|&");
编译执行main方法, 如果没有出错的话,
生成如E:/sybase_temp/ctl/CMS_CATEGORY.ctl这样的文件
load&& data &infile&& 'E:/sybase_temp/data/CMS_CATEGORY.txt'& "str '&~|&'"& truncate&& into&& table& CMS_CATEGORY & fields&& terminated&& by&& '|,&|' & trailing nullcols (CATEGORY_ID,CATEGORY_NAME,CATEGORY_DESC,
CREATE_DATE "TO_DATE(TO_CHAR(TO_TIMESTAMP(:CREATE_DATE, 'MON-DD-YYYY HH:MI:SS:FF AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON DD YYYY HH24:MI:SS'),'MON DD YYYY HH24:MI:SS')" )
(今天突然想到为什么我要连接sybase呢, 连接oracle也是可以的,& 并且可以直接判断字段的类型, 再加于修改)
5. 执行生成的sqlldrout.bat文件&&
检查error和log文件夹下面的文件, 注意如果ctl控制文件的脚本有错误的话, 那error可能没有log通知的.
导入数据后记得把主键,外键和索引都建上.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!整个过程中最难搞的是日期的部分, 在字段field 后面加"TO_DATE(TO_CHAR(TO_TIMESTAMP(:field, 'MON-DD-YYYY HH:MI:SS:FF AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON DD YYYY HH24:MI:SS'),'MON DD YYYY HH24:MI:SS')"注意'NLS_DATE_LANGUAGE=AMERICAN' , 不然第一个to_timestamp都不能转换, NLS_DATE_LANGUAGE默认是SIMPLIFIED CHINESE, 识别不了Det,Oct月份这样的写法对字段类型是dete和timestamp都可以,如果毫秒对你还有用的话, 那就要对这两个类型加以判断, 再进行转换!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------可爱的分割线---------------------------------------------------------------------
可能出现的问题: 1. 数据库连接不上2. 无法获取文件, 原因是ctl文件里面的infile文件路径或文件名不对3. 有些值太大了, 导致字段无法导入
4. 其他问题
----------------------------------------------------------------可爱的分割线---------------------------------------------------------------------
控制文件相关说明:--OPTIONS(DIRECT=TRUE,skip=0,ERRORS=0,READSIZE=655360)load&& data --这个固定, 并且必要&infile&& 'E:/sybase_temp/data/ZJK_PJZJXX.txt' "str '&~|&'"&&&&&& --要导入的数据文件, 可以写多个infile, "str '行分隔符'" 指定行的分隔符的& truncate&& into&& table& ZJK_PJZJXX& --导入的表& fields&& terminated&& by&& ',|,,'&&& --指定字段分隔符& trailing nullcols &&&&(ID,ID_HD,ID_ZJ,PJJB,SCORE,REMARK,TIME_PJ TIMESTAMP 'MON DD YYYY HH:MI:SS:FF AM'& , LOGIN_NAME,PJR)
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行&& LOAD DATA&& INFILE *& -- 因为数据同控制文件在一起，所以用 * 表示&& append&&& -- 这里用了 append 来操作，在表 users 中附加记录&&& INTO TABLE users&& when LOGIN_TIMES&&'8'& -- 还可以用 when 子句选择导入符合条件的记录&& Fields terminated by ","& trailing nullcols&& (&& & virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号&& & user_id "user_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值&& & user_name "'Hi '||upper(:user_name)",--,还能用SQL函数或运算对数据进行加工处理&& & login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符&& & last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL&& )&& BEGINDATA --数据从这里开始&& && ,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN&& 1,1,Unmi,3, 20:34&& 2,2,Fantasia,5,&& 3,3,隔叶黄莺,8,&& 4,4,Kypfos,NULL,NULL& 5,5,不知秋,1,&
根据用户下所有表生成控制文件--在操作系统上生成文件CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2,p_filename VARCHAR2)ISfile_handle utl_file.file_Write_content VARCHAR2(32767);Write_file_name VARCHAR2(1000);BEGIN--open filewrite_file_name := p_file_handle := utl_file.fopen('UTL_DIR',write_file_name,'w');write_content := text_--write fileIF utl_file.is_open(file_handle) THENutl_file.put_line(file_handle,write_content);dbms_output.put_line(write_content);END IF;--close fileutl_file.fclose(file_handle);EXCEPTIONWHEN OTHERS THENBEGINIF utl_file.is_open(file_handle) THENutl_file.fclose(file_handle);END IF;END;END sp_Write_/
--生成单个表sqlldr控制文件存储过程CREATE OR REPLACE PROCEDURE P_generate_sqlldr_null(p_table_name IN VARCHAR2) AS&& l_curr_line&&&&&& LONG;&& l_table_name&&&&& user_tables.table_name%TYPE;BEGIN&& select&& table_name&& into&&&& l_table_name&& from&&&& user_tables&& where&&& table_name =upper(p_table_name);&& l_curr_line := 'LOAD DATAINFILE '''||lower(l_table_name)||'.txt'||''' '||'"'||'str X'||'''7C0D0A'''||'"'||'INTO TABLE ';& l_curr_line := l_curr_line||l_table_name||'& FIELDS TERMINATED BY '||''''||'|/t'||''''||'& TRAILING NULLCOLS(';&& for rec in& ( select& table_name,column_name,column_id,nullable&& from&&& user_tab_columns&& where&& table_name =upper(p_table_name)&& order by& column_id) loop&&& if rec.column_id = 1 THEN&&&&&&&& IF rec.nullable = 'Y' THEN&&&&& l_curr_line := l_curr_line||'&&&&& '||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''//N''',80);&&&&&&&& ELSE&&&&& l_curr_line := l_curr_line||'&&&&& '||rpad(rec.column_name,80);&&&&&&&& END IF;&&& ELSE&&&&&& IF rec.nullable = 'Y' THEN&&&&& l_curr_line := l_curr_line||'&&&&& ,'||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''//N''',80);&&&&&& ELSE&&&&& l_curr_line := l_curr_line||'&&&&& ,'||rpad(rec.column_name,80);&&&&&&&& END IF;
&&&&&& l_curr_line := l_curr_line||')';& sp_write_log(l_curr_line,l_table_name||'.ctl');END P_generate_sqlldr_/--全部生成用户下所有表控制文件并且放在操作系统中指定的目录中CREATE OR REPLACE PROCEDURE p_gener_user_sqlldr ASBEGIN& FOR x IN (SELECT table_name FROM user_tables) LOOP&&&&& P_GENERATE_SQLLDR_null(x.table_name);& END LOOP;END p_gener_user_/
select& SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,&& SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL
alter session set NLS_DATE_LANGUAGE=AMERICAN; --SIMPLIFIED CHINESE
alter&& session&& set&&& NLS_DATE_FORMAT='yyyy-mon-dd';--DD-MON-RR--select to_date('Oct 6 :44:000AM', 'rr-MM-dd hh24:mi:ss','nls_date_language=american')
select to_timestamp('Oct 6 :44:000AM', 'Mon dd YYYY hh:mi:ss:ff AM')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：373316次
积分：5217
积分：5217
排名：第3512名
原创：139篇
转载：41篇
评论：184条
(2)(1)(3)(2)(2)(1)(1)(1)(2)(3)(1)(1)(2)(1)(4)(2)(3)(2)(2)(3)(5)(14)(6)(1)(2)(2)(3)(7)(8)(6)(3)(2)(6)(1)(9)(9)(3)(2)(1)(1)(1)(1)(1)(1)(4)(10)(27)(14)}
SAP Basis（7）
SAPOracleservice
DEVQASPRDFTP
brarchivebrarchivebrarchivebrbackupSAPbrarchivebrarchivebrbackupSAPbrbrbrarchive
OS SuSE Linux Enterprise 9.3
修改/etc/fstab，添加这一行
saperpdev:/prddbarch&&& /oracle/PRD/oraarch2&&& nfs rsize=32768,wsize=32768,hard 0 0
注：请先行在第二镜像点创建目录，目录权限均为ora&sid&:dba
修改init&SID&.ora，将参数设为如下
filesystemio_options&&&& = setall
disk_asynch_io&&&&&&&&&& = true
log_archive_dest_1 = "LOCATION=/oracle/PRD/oraarch/PRDarch MANDATORY REOPEN"
log_archive_dest_2 = "LOCATION=/oracle/PRD/oraarch2/PRDarch REOPEN"
创建远程数据库连接的口令文件。
orapwd -file=/oracle/&SID&/102_64/dbs/PWD&SID&.ora password=xxxx entries=100
重启数据库后，主系统就会写往两个点写日志了。
修改/etc/fstab，
mirrprd:/oracle/PRD/mirrTrans /oracle/DEV/mirrTrans/standby nfs defaults 0 0
把镜像机器上的处理目录挂到第二日志点的传输脚本目录的standby目录下。
修改/oracle/&SID&/102_64/network/admin/tnsnames.ora
PRIMARYPRD.WORLD =
& (DESCRIPTION =
&&& (ADDRESS_LIST =
&&&&& (ADDRESS =
&&&&&&& (COMMUNITY = SAP.WORLD)
&&&&&&& (PROTOCOL = TCP)
&&&&&&& (HOST = &Your Primary DB IP or hostname&)
&& &&&&&(PORT = 1527)
&&& (CONNECT_DATA =
&&&&& (SID = &Your primary DB SID&)
&&&&& (GLOBAL_NAME = PRD.WORLD)
重启listener后，tnsping primaryprd看一下结果，是否通了。
在/oracle/&SID&下创建传输日志的工作目录，目录结构如下
/oracle/&SID&/mirrTrans&&&& 工作目录
/oracle/&SID&/mirrTrans/logs 传输脚本产生的日志将会发在这个目录下
/oracle/&SID&/mirrTrans/primary 查询主系统的最新日志更新序列后产生的序列号文件
/oracle/&SID&/mirrTrans/standby 镜像系统日志处理目录，挂接到第二日志点的目录中
在此目录下生成search_seq.sql文件
connect system/&password&@PRIMARYPRD
set tab off
set pagesize 0
set linesize 50
spool primary/primary.sequence
select max(sequence#) from v$log_history
测试一下运行的情况
sqlplus /nolog @search_seq.sql
cat primary/primary.sequence
你会看到形如1234这样的数字，这代表了当前主系统更新到的号。
创建transfer_log.sh
ORACLE_HOME=/oracle/DEV/102_64
export ORACLE_HOME
WORK_DIR=/oracle/DEV/mirrTrans
STANDBY_DIR=$WORK_DIR/standby
PRIMARY_DIR=$WORK_DIR/primary
ARCH_DIR=/prddbarch
TRANSFER_FILE_LIST=""
UNTRANSFER_FILE_LIST=""
WORK_IN_LAN=0
START_TIME=`date "+%F_%T"`
LOGFILE_NAME=$WORK_DIR/logs/transfer_log_$START_TIME.log
STATUS_LOG=$WORK_DIR/transfer_status_`date "+%Y%m"`.log
log_status(){
& echo $1 && $STATUS_LOG
& echo $1 && $LOGFILE_NAME
log_space(){
log_separator(){
& log "========================================================================="
generate_file_list(){
& start=$1
& start=`expr $start + 1`
& while [ $start -le $end ]
&& dbf=PRDarch1_"$start"_.dbf
&& echo $dbf
&& if test -f $STANDBY_DIR/$dbf || test -f $STANDBY_DIR/$dbf.bz2
&&&&& UNTRANSFER_FILE_LIST=$UNTRANSFER_FILE_LIST" "$dbf
&&&&& TRANSFER_FILE_LIST=$TRANSFER_FILE_LIST" "$dbf
&& start=`expr $start + 1`
& log_space
& log "[info] Files following have been transferred to standby site"
& for file_name in $UNTRANSFER_FILE_LIST
&&& log "[info] $file_name ..."
& log_space
& log "[info] Files following need to transfer to standby site"
& if [ "$TRANSFER_FILE_LIST" != "" ]
&&& size=0
&&& for file_name in $TRANSFER_FILE_LIST
&&&& log "[info] $file_name ..."
&&&& file_size=`ls -l $ARCH_DIR/$file_name|awk '{print $5}'`
&&&& size=`expr $size + $file_size`
&&& log "[info] Total size is: `echo $size|awk '{printf "%4.2f", $1/}'`MB"
&&& log "[info] All files have been transferred to the standby site"
& log_space
transfer_file(){
log "[info] Timestamp: `date '+%F %T'`"
log "[info] Transfer file $1 via FTP"
ftp -i -v -n mirrprd&&EOF && $LOGFILE_NAME
user &ftpuser& &ftppassword&
cd /oracle/PRD/mirrTrans
touch $STANDBY_DIR/$1.end
log "[info] Timestamp: `date '+%F %T'`"
log "[info] Transfer file $1 done"
log "[info] Delete $1, because it has been transferred to standby site"
log "[info] Delete $1 done"
process_file(){
& log_space
& if [ $WORK_IN_LAN = 1 ]
&&& echo "[info] Work in LAN, transfer file directly"
&&& transfer_file $1
&&& log "[info] Work not in LAN, compress it first"
&&& log "[info] Timestamp: `date '+%F %T'`"
&&& bzip2 $1
&&& log "[info] Timestamp: `date '+%F %T'`"
&&& log "[info] Compress done"
&&& transfer_file $1.bz2
& log_space
end_job(){
& log_status "$START_TIME|transferLog|$1"
& log "[info] Timestamp: `date '+%F %T'`"
& log "[info] Transfer logs completed $2"
& log_separator
& rm transfer.lck
cd $WORK_DIR
if test -f transfer.lck
&& log "[info] A process is running, quit this one now"
log "[info] Mirror Logs Transfer (1.0)"
log "[info] Start of processing at: $START_TIME"
log "[info] Parameters"
log "[info] WORK_DIR:&&&&&&&&&&&&&&&&&& $WORK_DIR"
log "[info] PRIMARY_DIR:&&&&&&&&&&&&&&& $PRIMARY_DIR"
log "[info] STANDBY_DIR:&&&&&&&&&&&&&&& $STANDBY_DIR"
log "[info] File transfer via:&&&&&&&&& FTP"
log "[info] I. To make sure there is only one process running to transfer the file"
log "[info] Register this process"
touch transfer.lck
log "[info] Register done"
log "[info] Get the latest archived log sequence of Primary DB"
log "[exec] sqlplus /nolog @search_seq.sql"&
$ORACLE_HOME/bin/sqlplus /nolog @search_seq.sql &&$LOGFILE_NAME
log "[info] Get sequence done"
read PRIMARY_SEQUENCE & $PRIMARY_DIR/primary.sequence
log "[info] PRIMARY_SEQUENCE:&&&&&&&&& $PRIMARY_SEQUENCE"
read STANDBY_SEQUENCE & $STANDBY_DIR/standby.sequence
log "[info] STANDBY_SEQUENCE:&&&&&&&&& $STANDBY_SEQUENCE"
if [ $STANDBY_SEQUENCE = $PRIMARY_SEQUENCE ]
&& log "[info] Standby has updated to the latest sequence with Primary, don't do anything"
&& end_job 1 "without doing anything"
generate_file_list $STANDBY_SEQUENCE $PRIMARY_SEQUENCE
if [ "$TRANSFER_FILE_LIST" == "" ]
&& log "[info] No new logs need to transfer, do nothing and exit"
&& end_job 2 "without doning anything"
cd $ARCH_DIR
for FILE_NAME in $TRANSFER_FILE_LIST
& process_file $FILE_NAME
cd $WORK_DIR
end_job 0 "successfully"
这个脚本有个参数WORK_IN_LAN，当把它设为1时，表示工作在同一内网中，则需要传输的日志不会被压缩，直接发送即可。
请参看以下主流程的图例。
之所以有一个检查tranfer.lck的动作，是因为万一上一个传输动作没有完成，则这次的传输自动停止，以免发生冲突。所以第一次运行时，需要在镜像点那边先获取镜像数据库的序列号，并
cat 1234 & standby.sequence
以下是处理文件的流程。
每次传输完一个文件后，都会在镜像系统的对应目录下生成一个以.end为结尾的文件，表示传输完成。
运行这个脚本，sh transfer_log.sh，会在logs目录下产生一个这样的日志transfer_log__13:40:01.log
[info] Mirror Logs Transfer (1.0)
[info] Start of processing at: _13:40:01
[info] Parameters
[info] WORK_DIR: /oracle/DEV/mirrTrans
[info] PRIMARY_DIR: /oracle/DEV/mirrTrans/primary
[info] STANDBY_DIR: /oracle/DEV/mirrTrans/standby
[info] File transfer via: FTP
[info] I. To make sure there is only one process running to transfer the file
[info] Register this process
[info] Register done
[info] Get the latest archived log sequence of Primary DB
[exec] sqlplus /nolog @search_seq.sql
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Apr 11 13:40:01 2008
Copyright (c) , Oracle.& All Rights Reserved.
Connected.
&&&&&&&&& 9669
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
[info] Get sequence done
[info] PRIMARY_SEQUENCE: 9669
[info] STANDBY_SEQUENCE: 9665
[info] Files following have been transferred to standby site
[info] PRDarch1_12.dbf ...
[info] PRDarch1_12.dbf ...
[info] Files following need to transfer to standby site
[info] PRDarch1_12.dbf ...
[info] PRDarch1_12.dbf ...
[info] Total size is: 387.64MB
[info] Work not in LAN, compress it first
[info] Timestamp:
[info] Timestamp:
[info] Compress done
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 via FTP
Connected to mirrprd.
220---------- Welcome to Pure-FTPd [TLS] ----------
220-You are user number 1 of 50 allowed.
220-Local time is now 13:41. Server port: 21.
220-IPv6 connections are also welcome on this server.
220 You will be disconnected after 15 minutes of inactivity.
331 User Nippon OK. Password required
230-User Nippon has group access to:& ftp&&&&& video&&& audio&&& dialout
230- uucp&&&
230 OK. Current directory is /home/Nippon
Remote system type is UNIX.
Using binary mode to transfer files.
200 TYPE is now 8-bit binary
250 OK. Current directory is /oracle/PRD/mirrTrans
local: PRDarch1_12.dbf.bz2 remote: PRDarch1_12.dbf.bz2
229 Extended Passive mode OK (|||23429|)
150 Accepted data connection
226-30556.3 Mbytes free disk space
226-File successfully transferred
226 122.915 seconds (measured here), 451.40 Kbytes per second
bytes sent in 02:02 (451.47 KB/s)
221-Goodbye. You uploaded 55484 and downloaded 0 kbytes.
221 Logout.
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 done
[info] Delete PRDarch1_12.dbf.bz2, because it has been transferred to standby site
[info] Delete PRDarch1_12.dbf.bz2 done
[info] Work not in LAN, compress it first
[info] Timestamp:
[info] Timestamp:
[info] Compress done
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 via FTP
Connected to mirrprd.
220---------- Welcome to Pure-FTPd [TLS] ----------
220-You are user number 1 of 50 allowed.
220-Local time is now 13:44. Server port: 21.
220-IPv6 connections are also welcome on this server.
220 You will be disconnected after 15 minutes of inactivity.
331 User Nippon OK. Password required
230-User Nippon has group access to:& ftp&&&&& video&&& audio&&& dialout
230- uucp&&&
230 OK. Current directory is /home/Nippon
Remote system type is UNIX.
Using binary mode to transfer files.
200 TYPE is now 8-bit binary
250 OK. Current directory is /oracle/PRD/mirrTrans
local: PRDarch1_12.dbf.bz2 remote: PRDarch1_12.dbf.bz2
229 Extended Passive mode OK (|||26613|)
150 Accepted data connection
226-30497.2 Mbytes free disk space
226-File successfully transferred
226 135.808 seconds (measured here), 445.02 Kbytes per second
bytes sent in 02:15 (445.09 KB/s)
221-Goodbye. You uploaded 60438 and downloaded 0 kbytes.
221 Logout.
[info] Timestamp:
[info] Transfer file PRDarch1_12.dbf.bz2 done
[info] Delete PRDarch1_12.dbf.bz2, because it has been transferred to standby site
[info] Delete PRDarch1_12.dbf.bz2 done
[info] Timestamp:
[info] Transfer logs completed successfully
=========================================================================
再到镜像系统查看，在相应的目录下，应该会有一堆.bz2文件。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：24634次
排名：千里之外
原创：10篇
(1)(1)(1)(1)(6)查看: 5736|回复: 22
enq: DR - contention 是个什么等待，谢谢
论坛徽章:7
enq: DR - contention 是个什么等待，谢谢
oracle10g 10.2.0.3 RAC
hp-ux 11.23
第二个节点
select * from v$session_wait where wait_class && 'Idle';
& && && && && & sid& && && && &seq#& && & event
1& & & & 3293& & & & 7& & & & enq: DR - contention& & & & name|mode& & & & & & & & 0006& & & & 0& & & & 0& & & & 00& & & & 0& & & & 0& & & & 00& & & & & & & & 0& & & & Other& & & & 0& & & & 179143& & & & WAITING
然后通过select * from v$session where sid = 3293;
1& & & & CDCCEA68& & & & 3293& & & & 1& & & & 0& & & & CF508& & & & 0& & & & & & & & 0& & & & & & & & & & & & CD2CE0& & & & ACTIVE& & & & DEDICATED& & & & 0& & & & SYS& & & & oracle& & & & 2185& & & & hisdb02& & & & UNKNOWN& & & & oracle@hisdb02 (RECO)& & & & BACKGROUND& & & & 00& & & & 0& & & & & & & & 0& & & & 00& & & & 0& & & & & & & & 0& & & & & & & & & & & & & & & & & & & & & & & & 0& & & & & & & & 0& & & & & & & & 0& & & & -1& & & & 0& & & & 0& & & & 0& & & &
10:48:56& & & & 179219& & & & NO& & & & NONE& & & & NONE& & & & NO& & & & & & & & DISABLED& & & & DISABLED& & & & DISABLED& & & & 0& & & & & & & & VALID& & & & 1& & & & 3293& & & & 7& & & & 624& & & & enq: DR - contention& & & & name|mode& & & & & & & & 0006& & & & 0& & & & 0& & & & 00& & & & 0& & & & 0& & & & 00& & & & & & & & 0& & & & Other& & & & 0& & & & 179149& & & & WAITING& & & & SYS$BACKGROUND& & & & DISABLED& & & & FALSE& & & & FALSE
该会话竟然是 oracle@hisdb02 (RECO）
The recoverer background process (RECO)
RECO is a background process for distributed transactions. The RECO process manager two-phase commits to track and resolve in-doubt transactions.
该后台进程是用于分布式事务的。
有另外一台检验数据库通过dblink来获取该数据库数据的，
目前有两次大量的latch free产生，oracle 9206 ，latch#=98 buffer busy chains
不明白为什么会有这个等待，metalink也查不到
版主给个建议，谢谢
论坛徽章:7
论坛徽章:112
对于2PC的分布式事务,当因为某些原因,比如
1）数据库服务器异常[比如hang,crash,掉电[shutdown abort]
2）网络中断，超时
3）进程异常退出
& &&&比如kill -9
4）应用异常
等原因导致某一个阶段的事务失败,Oracle的reco进程会自动去处理这些失败的事务.但是在RECO完全解决该事务前，分布式事务中的dx[读/写] 锁不释放。
有些时候,可能因为某些原因,也需要人为的手工甘于一下，比如这些失败的2PC事务上正好在一些很关键的数据块上有lock,从而可能导致
更多的阻塞.为了不影响应用,这个时候就需要DBA介入去处理了.
论坛徽章:7
enq: DR - contention 是个什么等待
我怎么处理呢，我google ，metalink都找不到相应的处理方法
给个提示，谢谢
论坛徽章:112
这个要具体问题具体分析,你先看看dba_2pc_pending视图中有没有是否存在这样的分布式事务,状态是什么?
论坛徽章:7
有20多条，状态都是collecting
论坛徽章:7
都是12.19日切换以后开始的
论坛徽章:112
把这些都rollback force了吧
然后如果觉得这些东西在dba_2pc_pending里很不爽,你就调用DBMS_TRANSACTION包把这些事务purge掉吧(如果拿不准，就不要做这个动作了),找相应的support 工程师给你搞一下.
论坛徽章:7
如何 rollback force?
招聘 : 论坛徽章:66
原帖由 道可道非常道 于
15:56 发表
如何 rollback force?
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有
北京市公安局海淀分局网监中心备案编号： 广播电视节目制作经营许可证：编号（京）字第1149号oracle10g - Does Oracle really offer any OCI demo/sample programs? - Stack Overflow
to customize your list.
Announcing Stack Overflow Documentation
We started with Q&A. Technical documentation is next, and we need your help.
Whether you're a beginner or an experienced developer, you can contribute.
Oracle, says that there are some demo/sample programs for OCI.
In my case I'd like to find examples of using OCIConnectionPoolCreate().
There should be in a file called cdemocp.c, according to the documentation:
But its far from clear what I should install to get these files?
I would have hoped I could download them from Oracle's own website. Seems not. Certainly no handy link from the above page.
I have a tried installing (onto windows):
An Oracle 10g XE database server, no such source files
Oracle client (sdk options on), no such source files
searched within our linux installations -- where did I find the fabled \rdbms\demo\ folder which contains a few make files referring to the demo source files, the
but no source files. So running these makefiles fails.
Basically, no luck finding them.
If I google for cdemocp.c I find only:
the typical multiple copies of Oracle's own documentation
other people saying they cant find it.
( I finding using oracle's documentation is like lord of the rings, only a faint connection with reality, and probably too many words. I'm not enjoying it. )
OK found the OCI demo files. It was a serious pain to find them.
From Oracle, get some "Companion Disk". Such as:
"Oracle Database 10g Companion CD Release 2" :
For me all the various "Universal"
installers I tried crashed, or refused to run.
Even when run on the OS they were supposed to be run on.
But, having expanded the installer you can just dig out .jar files.
These .jar file are really just .zip files with a diffident extension. This file:
102010_win64_x64_companion\companion\Disk1\stage\Components\panion\10.2.0.3.0\1\DataFiles\filegroup2.jar
when extracted, contains all the OCI demo files I wanted under rdbms/demo/. Voilà.
( I list them here so anyone googling for them might find this page. )
cdemdpco.c cdemdpin.c cdemdpit.c cdemdplp.c
cdemdpno.c
cdemdpro.c
cdemdpss.c cdemo1.c cdemo2.c cdemo3.c cdemo4.c cdemo5.c cdemo81.c cdemo82.c cdemoanydata1.c cdemoanydata2.c cdemobj.c cdemocoll.c cdemocor.c cdemocor1.c cdemocp.c cdemocpproxy.c cdemodp.c cdemodp_lip.c cdemodr1.c cdemodr2.c cdemodr3.c cdemodsa.c cdemodsc.c cdemodt.c cdemoext.c cdemofil.c cdemofo.c cdemofor.c cdemoin1.c cdemoin2.c cdemoin3.c cdemol2l.c cdemolb.c cdemolb2.c cdemolbs.c cdemoplb.c cdemoqc.c cdemoqc2.c cdemorid.c cdemort.c cdemosc.c cdemoses.c cdemosp.c cdemostc.c cdemosyev.c cdemothr.c cdemoucb.c cdemoucbl.c cdemouni.c cdemoup1.c cdemoup2.c cdemoupk.c cdemoxml.c extdemo2.c extdemo4.c extdemo5.c extdemo6.c nchdemo1.c obndra.c oci02.c oci03.c oci04.c oci05.c oci06.c oci07.c oci08.c oci09.c oci10.c oci11.c oci12.c oci13.c oci14.c oci15.c oci16.c oci17.c oci18.c oci19.c oci20.c oci21.c oci22.c oci23.c oci24.c oci25.c ociaqarraydeq.c ociaqarrayenq.c ociaqdemo00.c ociaqdemo01.c ociaqdemo02.c ociucb.c readpipe.c strmmon.c
cdemo6.h cdemo82.h cdemobj.h cdemocoll.h cdemocor.h cdemodp.h cdemodp0.h cdemodr1.h cdemodr2.h cdemodr3.h cdemodsc.h cdemoin1.h cdemoin2.h cdemoin3.h cdemolb.h cdemorid.h cdemort.h cdemoses.h cdemosp.h cdemostc.h cdemothr.h extdemo2.h extdemo4.h extdemo5.h extdemo6.h mdemo1.h mymdemo1.h cdemo6.cc mdemo1.cpp mdemo1o.cpp occiaqlis.cpp occiaqop.cpp occiblob.cpp occiclob.cpp occicoll.cpp occidesc.cpp occidml.cpp occiinh.cpp occilbar.cpp occimb1.cpp occiobj.cpp occipobj.cpp occipool.cpp occiproc.cpp occiscp.cpp occistre.cpp occiuni1.cpp occiuni2.cpp
Update from above since some links changed
First download:
Then if you have 7zip on windows you can drill down to the zip and jar file like this:
Downloads\linux.x64_11gR2_examples.zip\examples\stage\Components\panion\11.2.0.1.0\1\DataFiles\filegroup2.jar\rdbms\demo\
3,071103355
After struggling to find the OCI sample programs for Oracle 12 on Windows, I found them here:
You need to log onto oracle and on the windows item, click "see all"
Microsoft Windows x64 (64-bit)
File 1, File 2
Oracle Database Examples (12.1.0.2.0) for Microsoft Windows (x64)
winx64_12102_examples.zip (576,214,828 bytes)
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled1868人阅读
数据库相关（13）
本来项目所用的数据库从Sybase转为Oracle, 导数据是用户他们自己的责任, 所以导数据我是不用管的, 也不在我的工作范围内, 但是有机会能学下BCP和SQLLDR还是很好的, 唉, 晚上的时间没啦! T.T (本人所用系统是XP)
在上篇文章 中, 把表结构先建立好, 但是先别建立主键, 外键, 索引, &并建立下面的文件夹.
bcp.txt, bcpout.bat, sqlldr.txt, sqlldrout.bat 放于: E:/sybase_temp/数据库数据文件放于: E:/sybase_temp/data/ctl文件放于: E:/sybase_temp/ctl/log文件放于: E:/sybase_temp/log/error_log文件放于: E:/sybase_temp/error/
其中: bcp.txt是生成bcpout.bat的脚本,& bcpout.bat是把sybase的用户表数据导出为文件的程序, sqlldr.txt是生成sqlldrout.bat的脚本, sqlldrout.bat是把导出的文件中的数据导入到oracle中的表里面的程序.
准备工作做好后, 那就开始啦~~~~~~~~~
1. 可用如下方法生成一个可以一次导出一个数据库中所有表的数据的执行脚本。编辑一个如下文本文件 文件名称例为 E:/sybase_temp/bcp.txt ：
use&database1& --用到的数据库
select 'bcp database1..' + name + ' out E:/sybase_temp/data/' + name + '.txt -U user1 -P&pw1 -S&server1 -c -t"|,&|"& -r "&~|&" ' from sysobjects where type = 'U' --bcp database1..CMS_CATEGORY out E:/sybase_temp/data/CMS_CATEGORY.txt -U user1 -P&dw1 -S&server1 -c -t"|,&|"& -r "&~|&"&&& -- -U 用户名 -P 密码 -S 服务器名 -t字段分隔符(默认是制表符/t, 不过还是要用复杂点的好) -r行分隔符(默认是回车符/n, 如果用默认的,字段中存在回车符将导致数据错误)go
注意: E:/sybase_temp/data 文件夹必须要存在才行在dos里面执行:isql -U&user1 -P pw1 -S&server1 -i E:/sybase_temp/bcp.txt -o E:/sybase_temp/bcpout.bati参数后为输入文件，o参数后文件为输入文件执行后得到的输出文件。执行后可以得到一个后缀名为 BAT 的批处理文件（在unix下则生成一个shell文件并更改相应的执行权限），可直接执行。执行后将在指定的目录下导出了相应的TXT(保存为TXT为个人喜好)数据文件。一个表的数据为一个文件。如在UNIX下则可不用BAT后缀。
2. 执行生成的bcpout.bat文件&& 在E:/sybase_temp/文件夹下面有很多文件被创建, 这些文件里面都是数据库表的数据, 检查数据库表的数量跟产生的文件的数量是否相符.
3.编辑文本 E:/sybase_temp/sqlldr.txt :
use database1--用到的数据库
select 'sqlldr
control=E:/sybase_temp/ctl/' + name + '.ctl& log=E:/sybase_temp/log/' + name + '.log bad=E:/sybase_temp/error/' + name + '_error.log& ' from sysobjects where type = 'U'
--sqlldr&& && control=E:/sybase_temp/ctl/CMS_CATEGORY.ctl& log=E:/sybase_temp/log/CMS_CATEGORY.log bad=E:/sybase_temp/error/CMS_CATEGORY_error.log
注意: E:/sybase_temp/ctl/,& E:/sybase_temp/log/ , E:/sybase_temp/error/ 文件夹必须要存在才行在dos里面执行:
isql -U&user1 -P&pw1 -S&server1 -i E:/sybase_temp/sqlldr.txt -o E:/sybase_temp/sqlldrout.bat
4. 在项目在写一个生成ctl文件的java类(还有很多方法的), sybase jdbc驱动是jconn2.jar
SybaseTableCtl.java
import&java.sql.*;
import&java.util.L
import&java.util.ArrayL
import&java.io.F
import&java.io.FileW
import&java.io.BufferedW
public&class&SybaseTableCtl&{
&&&&public&static&final&int&SYBASE_DATABASE&=&1;
&&&&public&static&final&String&SYBASE_DATETIME_TYPE&=&"datetime";
&&&&public&static&final&String&SYBASE_TIMESTAMP_TYPE&=&"timestamp";
&&&&private&int&databaseType&=&1;
&&&&public&int&getDatabaseType()&{
&&&&&&&&return&databaseT
&&&&public&void&setDatabaseType(int&databaseType)&{
&&&&&&&&this.databaseType&=&databaseT
&&&&public&Connection&getConn(){
&&&&&&&&try&{
&&&&&&&&&&&&
&&&&&&&&&&&&Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
&&&&&&&&&&&&
&&&&&&&&&&&&Connection&con&=&DriverManager.getConnection("jdbc:sybase:Tds:***.***.****.***:5000/database1?FAKE_METADATA=true&charset=cp936&jconnect_version=0",&"sa",&"");
&&&&&&&&&&&&return&
&&&&&&&&}catch(ClassNotFoundException&cnf){
&&&&&&&&&&&&System.out.println("driver¬&find:"+cnf);
&&&&&&&&&&&&return&null;
&&&&&&&&}catch(SQLException&sqle){
&&&&&&&&&&&&System.out.println("can?t&connection&db:"+sqle);
&&&&&&&&&&&&return&null;
&&&&&&&&}&catch&(Exception&e)&{
&&&&&&&&&&&&System.out.println("Failed&to&load&JDBC/ODBC&driver.");
&&&&&&&&&&&&return&null;
&&&&public&List&getTableNameList()&throws&Exception&{
&&&&&&&&List&tableNames&=&null;
&&&&&&&&String&sql&=&getTableSql();
&&&&&&&&if(sql&!=&null&&&&!"".equals(sql)){
&&&&&&&&&&&&Connection&conn&=&null;
&&&&&&&&&&&&Statement&stmt&=&null;
&&&&&&&&&&&&ResultSet&rs&=&null;
&&&&&&&&&&&&try{
&&&&&&&&&&&&&&&&conn&=&getConn();
&&&&&&&&&&&&&&&&if(conn&!=&null){
&&&&&&&&&&&&&&&&&&&&stmt&=&conn.createStatement();
&&&&&&&&&&&&&&&&&&&&rs&=&stmt.executeQuery(sql);
&&&&&&&&&&&&&&&&&&&&if(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&tableNames&=&new&ArrayList();
&&&&&&&&&&&&&&&&&&&&&&&&tableNames.add(rs.getString(1).toUpperCase());
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&while(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&tableNames.add(rs.getString(1).toUpperCase());
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&}else&return&null;
&&&&&&&&&&&&}catch(Exception&e){
&&&&&&&&&&&&&&&&e.printStackTrace();
&&&&&&&&&&&&&&&&return&null;
&&&&&&&&&&&&}finally{
&&&&&&&&&&&&&&&&if(conn&!=&null)&conn.close();
&&&&&&&&&&&&}
&&&&&&&&return&tableN
&&&&public&void&createCtlTxt(String&dataFilePath,String&dataExt,&String&outFilePath,&String&ext,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&String&field_terminator,&String&row_terminator)&throws&Exception&{
&&&&&&&&Connection&conn&=&null;
&&&&&&&&Statement&stmt&=&null;
&&&&&&&&ResultSet&rs&=&null;
&&&&&&&&try{
&&&&&&&&&&&&conn&=&getConn();
&&&&&&&&&&&&if(conn&!=&null){
&&&&&&&&&&&&&&&&stmt&=&conn.createStatement();
&&&&&&&&&&&&&&&&List&tables&=&getTableNameList();
&&&&&&&&&&&&&&&&if(tables&==&null){
&&&&&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&getTableNameList为null,&查询不到用户表");
&&&&&&&&&&&&&&&&&&&&return&;
&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&String&tableName&=&null;
&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&String&sql&=&"select&b.name,(select&name&from&systypes&where&usertype&=&b.usertype)&type_name&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"&from&sysobjects&a,&syscolumns&b&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"&where&a.type&=&'U'&and&a.id&=&b.id&"&+
&&&&&&&&&&&&&&&&&&&&&&&&"and&a.name&='";
&&&&&&&&&&&&&&&&String&txt&=&"";
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&sql&="&+&sql);
&&&&&&&&&&&&&&&&boolean&doCretat&=&false;
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&用户表数目:&"&+&tables.size());
&&&&&&&&&&&&&&&&int&num&=&0;
&&&&&&&&&&&&&&&&for(int&i=0;&i&&&tables.size();&i++){
&&&&&&&&&&&&&&&&&&&&tableName&=&(String)&tables.get(i);
&&&&&&&&&&&&&&&&&&&&rs&=&stmt.executeQuery(sql&+&tableName&+&"'&");
&&&&&&&&&&&&&&&&&&&&txt&=&"load&&&data /n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"infile&&&'"&+&dataFilePath&+&File.separator&+&&tableName&+&"."&+&dataExt&+&"'&&/"str&'"&+&row_terminator&+&"'/"/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&truncate&&&into&&&table&&"&+&tableName&+&"&/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&fields&&&terminated&&&by&&&'"&+&field_terminator&+&"'&/n&"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"&trailing&nullcols&/n"
&&&&&&&&&&&&&&&&&&&&&&&&&&&&+&"(";
&&&&&&&&&&&&&&&&&&&&String&_txt&=&"";
&&&&&&&&&&&&&&&&&&&&while(rs.next()){
&&&&&&&&&&&&&&&&&&&&&&&&doCretat&=&true;
&&&&&&&&&&&&&&&&&&&&&&&&String&field&=&rs.getString(1);
&&&&&&&&&&&&&&&&&&&&&&&&if(field&!=&null&&&&!"".equals(field)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&field.toUpperCase();
&&&&&&&&&&&&&&&&&&&&&&&&&&&&if(rs.getString(2)&!=&null&&&&rs.getString(2).equals(SybaseTableCtl.SYBASE_DATETIME_TYPE)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&"&/"TO_DATE(TO_CHAR(TO_TIMESTAMP(:"&+&field&+&",&'MON-DD-YYYY&HH:MI:SS:FF&AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON&DD&YYYY&HH24:MI:SS'),'MON&DD&YYYY&HH24:MI:SS')/"&,";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&&&&&&&&&else&if(rs.getString(2)&!=&null&&&&rs.getString(2).equals(SybaseTableCtl.SYBASE_TIMESTAMP_TYPE)){
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&"&TIMESTAMP&'YYYY-MM-DD&HH:MI:SS:FF&AM'&,";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}else{
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&_txt&+=&",";
&&&&&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&if(_txt&!=&null&&&&!"".equals(_txt)){
&&&&&&&&&&&&&&&&&&&&&&&&_txt&=&_txt.trim();
&&&&&&&&&&&&&&&&&&&&&&&&_txt&=&_txt.substring(0,_txt.length()&-1);&&
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&&&&&txt&+=&_txt&+&")";
&&&&&&&&&&&&&&&&&&&&if(doCretat){
&&&&&&&&&&&&&&&&&&&&&&&&File&outputFile&=&new&File(outFilePath&+&File.separator&+&tableName&+&"."&+&ext);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&FileWriter&out&=&new&FileWriter(outputFile,&false);
&&&&&&&&&&&&&&&&&&&&&&&&BufferedWriter&bufferOut&=&new&BufferedWriter(out);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut.write(txt);
&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut.close();
&&&&&&&&&&&&&&&&&&&&&&&&out.close();
&&&&&&&&&&&&&&&&&&&&&&&&outputFile&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&out&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&bufferOut&=&null;
&&&&&&&&&&&&&&&&&&&&&&&&num&++;
&&&&&&&&&&&&&&&&&&&&}else{
&&&&&&&&&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&:&"&+&tableName&+&"no&ctl");
&&&&&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&}
&&&&&&&&&&&&&&&&System.out.println("createCtlTxt:&ctl文件建立数目:&"&+&num);
&&&&&&&&&&&&};
&&&&&&&&}catch(Exception&e){
&&&&&&&&&&&&e.printStackTrace();
&&&&&&&&}finally{
&&&&&&&&&&&&if(conn&!=&null)&conn.close();
&&&&public&String&getTableSql(){
&&&&&&&&if(getDatabaseType()&==&SybaseTableCtl.SYBASE_DATABASE){
&&&&&&&&&&&&return&"select&name&from&sysobjects&where&type&=&'U'&";
&&&&&&&&}else&return&"";
&&&&public&static&void&main(String[]&args)&throws&Exception&{
&&&&&&&&SybaseTableCtl&ctr&=&new&SybaseTableCtl();
&&&&&&&&ctr.setDatabaseType(SybaseTableCtl.SYBASE_DATABASE);
&&&&&&&&ctr.createCtlTxt("E://sybase_temp//data","txt","E://sybase_temp//ctl","ctl","|,&|",&"&~|&");
编译执行main方法, 如果没有出错的话,
生成如E:/sybase_temp/ctl/CMS_CATEGORY.ctl这样的文件
load&& data &infile&& 'E:/sybase_temp/data/CMS_CATEGORY.txt'& "str '&~|&'"& truncate&& into&& table& CMS_CATEGORY & fields&& terminated&& by&& '|,&|' & trailing nullcols (CATEGORY_ID,CATEGORY_NAME,CATEGORY_DESC,
CREATE_DATE "TO_DATE(TO_CHAR(TO_TIMESTAMP(:CREATE_DATE, 'MON-DD-YYYY HH:MI:SS:FF AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON DD YYYY HH24:MI:SS'),'MON DD YYYY HH24:MI:SS')" )
(今天突然想到为什么我要连接sybase呢, 连接oracle也是可以的,& 并且可以直接判断字段的类型, 再加于修改)
5. 执行生成的sqlldrout.bat文件&&
检查error和log文件夹下面的文件, 注意如果ctl控制文件的脚本有错误的话, 那error可能没有log通知的.
导入数据后记得把主键,外键和索引都建上.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!整个过程中最难搞的是日期的部分, 在字段field 后面加"TO_DATE(TO_CHAR(TO_TIMESTAMP(:field, 'MON-DD-YYYY HH:MI:SS:FF AM','NLS_DATE_LANGUAGE=AMERICAN'),'MON DD YYYY HH24:MI:SS'),'MON DD YYYY HH24:MI:SS')"注意'NLS_DATE_LANGUAGE=AMERICAN' , 不然第一个to_timestamp都不能转换, NLS_DATE_LANGUAGE默认是SIMPLIFIED CHINESE, 识别不了Det,Oct月份这样的写法对字段类型是dete和timestamp都可以,如果毫秒对你还有用的话, 那就要对这两个类型加以判断, 再进行转换!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------可爱的分割线---------------------------------------------------------------------
可能出现的问题: 1. 数据库连接不上2. 无法获取文件, 原因是ctl文件里面的infile文件路径或文件名不对3. 有些值太大了, 导致字段无法导入
4. 其他问题
----------------------------------------------------------------可爱的分割线---------------------------------------------------------------------
控制文件相关说明:--OPTIONS(DIRECT=TRUE,skip=0,ERRORS=0,READSIZE=655360)load&& data --这个固定, 并且必要&infile&& 'E:/sybase_temp/data/ZJK_PJZJXX.txt' "str '&~|&'"&&&&&& --要导入的数据文件, 可以写多个infile, "str '行分隔符'" 指定行的分隔符的& truncate&& into&& table& ZJK_PJZJXX& --导入的表& fields&& terminated&& by&& ',|,,'&&& --指定字段分隔符& trailing nullcols &&&&(ID,ID_HD,ID_ZJ,PJJB,SCORE,REMARK,TIME_PJ TIMESTAMP 'MON DD YYYY HH:MI:SS:FF AM'& , LOGIN_NAME,PJR)
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行&& LOAD DATA&& INFILE *& -- 因为数据同控制文件在一起，所以用 * 表示&& append&&& -- 这里用了 append 来操作，在表 users 中附加记录&&& INTO TABLE users&& when LOGIN_TIMES&&'8'& -- 还可以用 when 子句选择导入符合条件的记录&& Fields terminated by ","& trailing nullcols&& (&& & virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号&& & user_id "user_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值&& & user_name "'Hi '||upper(:user_name)",--,还能用SQL函数或运算对数据进行加工处理&& & login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符&& & last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL&& )&& BEGINDATA --数据从这里开始&& && ,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN&& 1,1,Unmi,3, 20:34&& 2,2,Fantasia,5,&& 3,3,隔叶黄莺,8,&& 4,4,Kypfos,NULL,NULL& 5,5,不知秋,1,&
根据用户下所有表生成控制文件--在操作系统上生成文件CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2,p_filename VARCHAR2)ISfile_handle utl_file.file_Write_content VARCHAR2(32767);Write_file_name VARCHAR2(1000);BEGIN--open filewrite_file_name := p_file_handle := utl_file.fopen('UTL_DIR',write_file_name,'w');write_content := text_--write fileIF utl_file.is_open(file_handle) THENutl_file.put_line(file_handle,write_content);dbms_output.put_line(write_content);END IF;--close fileutl_file.fclose(file_handle);EXCEPTIONWHEN OTHERS THENBEGINIF utl_file.is_open(file_handle) THENutl_file.fclose(file_handle);END IF;END;END sp_Write_/
--生成单个表sqlldr控制文件存储过程CREATE OR REPLACE PROCEDURE P_generate_sqlldr_null(p_table_name IN VARCHAR2) AS&& l_curr_line&&&&&& LONG;&& l_table_name&&&&& user_tables.table_name%TYPE;BEGIN&& select&& table_name&& into&&&& l_table_name&& from&&&& user_tables&& where&&& table_name =upper(p_table_name);&& l_curr_line := 'LOAD DATAINFILE '''||lower(l_table_name)||'.txt'||''' '||'"'||'str X'||'''7C0D0A'''||'"'||'INTO TABLE ';& l_curr_line := l_curr_line||l_table_name||'& FIELDS TERMINATED BY '||''''||'|/t'||''''||'& TRAILING NULLCOLS(';&& for rec in& ( select& table_name,column_name,column_id,nullable&& from&&& user_tab_columns&& where&& table_name =upper(p_table_name)&& order by& column_id) loop&&& if rec.column_id = 1 THEN&&&&&&&& IF rec.nullable = 'Y' THEN&&&&& l_curr_line := l_curr_line||'&&&&& '||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''//N''',80);&&&&&&&& ELSE&&&&& l_curr_line := l_curr_line||'&&&&& '||rpad(rec.column_name,80);&&&&&&&& END IF;&&& ELSE&&&&&& IF rec.nullable = 'Y' THEN&&&&& l_curr_line := l_curr_line||'&&&&& ,'||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''//N''',80);&&&&&& ELSE&&&&& l_curr_line := l_curr_line||'&&&&& ,'||rpad(rec.column_name,80);&&&&&&&& END IF;
&&&&&& l_curr_line := l_curr_line||')';& sp_write_log(l_curr_line,l_table_name||'.ctl');END P_generate_sqlldr_/--全部生成用户下所有表控制文件并且放在操作系统中指定的目录中CREATE OR REPLACE PROCEDURE p_gener_user_sqlldr ASBEGIN& FOR x IN (SELECT table_name FROM user_tables) LOOP&&&&& P_GENERATE_SQLLDR_null(x.table_name);& END LOOP;END p_gener_user_/
select& SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,&& SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL
alter session set NLS_DATE_LANGUAGE=AMERICAN; --SIMPLIFIED CHINESE
alter&& session&& set&&& NLS_DATE_FORMAT='yyyy-mon-dd';--DD-MON-RR--select to_date('Oct 6 :44:000AM', 'rr-MM-dd hh24:mi:ss','nls_date_language=american')
select to_timestamp('Oct 6 :44:000AM', 'Mon dd YYYY hh:mi:ss:ff AM')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：373316次
积分：5217
积分：5217
排名：第3512名
原创：139篇
转载：41篇
评论：184条
(2)(1)(3)(2)(2)(1)(1)(1)(2)(3)(1)(1)(2)(1)(4)(2)(3)(2)(2)(3)(5)(14)(6)(1)(2)(2)(3)(7)(8)(6)(3)(2)(6)(1)(9)(9)(3)(2)(1)(1)(1)(1)(1)(1)(4)(10)(27)(14)}
我要回帖
更多关于 dr wu孕妇能用吗 的文章
更多推荐
- ·哪个快递发西安发往新疆最便宜的物流便宜
- ·移动花卡哪些app免流软件有哪些
- ·请问这是什么翻译用什么软件最好用软件？
- ·怎样进入百度知道官网入口百科？
- ·闲鱼转账能要回来吗被骗200元,对方实名认证了，交易完成，钱可以追回吗？
- ·荣耀7好还是小米4s与荣耀7好？
- ·宽带能连上网页广电宽带打不开网页是停机了嘛
- ·有没有直接看哪种电影网站的···直接发我邮箱类型采纳有悬赏...什么类型都有··
- ·求猛男诞生记求封神传奇百度云云
- ·管家anbot 机器人人UNIBOT有什么样功能?
- ·没Qq 号。可以给个吗？
- ·台式电脑使用最后一次正确配置是解决cf蓝屏解决办法的办法吗
- ·微信手机号换了现在登不你看上去很有钱林绵绵了里面有钱怎么办
- ·管家promobot机器人人UNIBOT的外设有什么？谢谢
- ·谁能帮我解封映客号淘宝永久封号怎么解封 有奖 急急急
- ·联想苹果平板怎样更改ids106怎么更改时间和年月日?
- ·2015款一汽奔腾b500行驶18000买了一年能卖多钱
- ·谁能帮我怎么注册推特账号一个推特号的？急～～
- ·这个怎么删除桌面的ie图标怎么删
- ·手机充电，过了笔记本电池一会充电又充不进去、但显示正在充电、就是充不进。
- ·韩国dr.oracle怎么样孕妇能用么
- ·qq关于七夕个性签名名七夕虐狗的
- ·照片删掉删掉的照片怎么恢复复出来
- ·手机怎样下载陌陌？
- ·gt9800(512m)512m显卡玩lol够吗能玩什么大型单机游戏。
- ·QQ分组名称，家人 朋友 给爱人的qq分组 同学 好友这些，给它们弄个好听有符文，漂亮的名称
- ·光大银行理财产品激活
- ·windows mov自带的movoer是什么意思
- ·三星C5自己出现donot为何usb口disconnecttusb怎么办呢？
- ·七夕的说说,防止被虐,屏蔽空间的说说
- ·联想n440丅440左侧口是干什么用的
- ·求bowlroll各种场景配布，家里网速问题网站都打不开_(:з」∠)_ 625200212@wx.qq.comm
- ·征服者行车记录仪官网征服者I一699怎么样
