大龙的博客

常用链接

统计

最新评论

linux配置oracle客户端,sqlplus、sqlldr、exp、imp --- 转

如果有错,谢谢指出!
一、配置环境

 需要在安装有oracle软件的相同操作系统上获取部分oracle的系统文件

二、配置目标

无需安装oracle服务器或客户端软件,达到能使用以下功能的目标: 

 oci、occi、jdbc的支持

 tnsping

 sqlplus

 sqlldr

 exp

 imp 

 tkprof

三、软件下载

http://www.oracle.com/technetwork/cn/database/features/instant-client/index-092699-zhs.html

instantclient-basic-linux.x64-11.2.0.3.0

instantclient-jdbc-linux.x64-11.2.0.3.0

instantclient-sqlplus-linux.x64-11.2.0.3.0

instantclient-sdk-linux.x64-11.2.0.3.0

四、配置步骤

配置各个功能都一样,关键是环境变量的配置,根据自己实际情况配置,这些基本上都是在网上找的,关键环境变量做了改动

1、配置sqlplus

root用户登录,创建目录:

# mkdir -p /opt/oracle

将所有软件包解压缩至/opt/oracle/下,所有文件将自动释放至目录/opt/oracle/instantclient_10_2/

创建文件/opt/oracle/instantclient_10_2/tnsnames.ora

格式如下:

CZJGBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 81.17.1.23)(PORT = 1521)) //需要连接的IP
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = czjgbs) //需要连接的实例
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
在需要使用oracle功能的测试用户环境变量中,添加如下配置,这里一定要注意,如果你失败了百分之八十都是这里的问题: 
export ORACLE_HOME=/opt/oracle
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_IC_HOME=/opt/oracle/instantclient_11_2
export ORACLE_HOME=$ORACLE_IC_HOME
export TNS_ADMIN=$ORACLE_IC_HOME
export PATH=$PATH:$HOME/bin:$ORACLE_IC_HOME
export LD_LIBRARY_PATH=$ORACLE_IC_HOME:/usr/lib
export ORACLE_SID=czjgbs //
(
需要连接的实例名称)

生效后,测试sqlplus

$ sqlplus scott/tiger@ora215

SQL*Plus: Release 10.2.0.4.0 - Production on 星期五 8 3 10:41:44 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL>  

Oksqlplus工具可以使用了;

2、配置tnsping

下面我们在此基础上增加tnsping工具

回到root用户,在其他装有Oracle的机器上获取必要的文件:

 

# cd /opt/oracle/instantclient_10_2/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/bin/tnsping ./          //这是从其他安装了oracle服务器的机子上拷贝文件过来,不一定非要通过这种方法,

root@192.168.21.16's password:

tnsping                                            100%   13KB  13.3KB/s   00:00   

# mkdir-p network/mesg

# cd network/mesg

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/network/mesg/tnsus.msb ./     

root@192.168.21.16's password:

tnsus.msb                                      100%   46KB  46.0KB/s   00:00   

#

 

下面测试一下tnsping工具

 

$ tnsping ora16

 

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 03-8 -2012 11:03:39

 

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.16)(PORT = 1521))) (CONNECT_DATA = (SID = neizxmdb1) (SERVER = DEDICATED)))

OK (0 msec)

$

 

Oktnsping能正确运行了!

3、配置sqlldr

Oracleinstanct_client没有自带sqlldr工具,也要单独获取相关的文件:

 

root用户登录:

# cd /opt/oracle/instantclient_10_2/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/bin/sqlldr ./

root@192.168.21.16's password:

sqlldr                                            100%  717KB 717.5KB/s   00:00   

# mkdir -p rdbms/mesg/

# cd rdbms/mesg/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/rdbms/mesg/ulus.msb ./

root@192.168.21.16's password:

ulus.msb                                         100%   37KB  37.0KB/s   00:00   

#

 

在测试用户中运行sqlldr

$ sqlldr

 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期五 8 3 11:20:04 2012

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

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

 

Valid Keywords:

……

Sqlldr工具可以运行了

4、配置impexp

root用户登录:

 

# cd /opt/Oracle/instantclient_10_2/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/bin/imp ./  

root@192.168.21.16's password:

imp                                               100%  348KB 348.3KB/s   00:00   

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/bin/exp ./

root@192.168.21.16's password:

exp                                               100%  697KB 697.1KB/s   00:00   

# cd rdbms/mesg/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/rdbms/mesg/impus.msb ./   

root@192.168.21.16's password:

impus.msb                                         100%   16KB  15.5KB/s   00:00   

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/rdbms/mesg/expus.msb ./ 

root@192.168.21.16's password:

expus.msb                                         100%   17KB  16.5KB/s   00:00   

#

测试用户下运行impexp

$ imp

 

Import: Release 10.2.0.4.0 - Production on 星期五 8 3 11:31:24 2012

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Username:

 

$ exp

 

Export: Release 10.2.0.4.0 - Production on 星期五 8 3 11:31:13 2012

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

Username:

 

Ok

 

 

5、配置tkprof

Tkprof是一个格式化sql trace文件的工具,在简单客户端中,我们也可以添加进来:

 

root用户登录:

# cd /opt/oracle/instantclient_10_2/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/bin/tkprof ./

root@192.168.21.16's password:

tkprof                                            100%  135KB 135.1KB/s   00:00   

# mkdir -p oracore/mesg/

# cd oracore/mesg/

# scp root@192.168.21.16:/oracle/orahome/product/10.2.0/oracore/mesg/lrmus.msb ./

root@192.168.21.16's password:

lrmus.msb                                        100% 4608     4.5KB/s   00:00   

#

 

测试用户下执行

$ tkprof

Usage: tkprof tracefile outputfile [explain= ] [table= ]

              [print= ] [insert= ] [sys= ] [sort= ]

  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

  print=integer    List only the first 'integer' SQL statements.

  aggregate=yes|no

  insert=filename  List SQL statements and data inside INSERT statements.

  sys=no           TKPROF does not list SQL statements run as user SYS.

  record=filename  Record non-recursive statements found in the trace file.

  waits=yes|no     Record summary for any wait events found in the trace file.

  sort=option      Set of zero or more of the following sort options:

prscnt  number of times parse was called

……

 

tkprof可以运行了

posted on 2013-12-02 16:55 大龙 阅读(5190) 评论(2)  编辑 收藏 引用

评论

# re: linux配置oracle客户端,sqlplus、sqlldr、exp、imp --- 转[未登录] 2014-08-19 17:56 seven

哪抄来的 上下文都不一致 版本都不统一  回复  更多评论   

# re: linux配置oracle客户端,sqlplus、sqlldr、exp、imp --- 转 2015-07-20 09:23 wangst

写的太好了
我这边项目需要sqlldr,网上又下不到,可否帮忙传一份给我
qq邮箱:514025541@qq.com  回复  更多评论   


只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理