linux下安装oracle客户端，实现远程连接oracle库，导出数据表

最新推荐文章于 2022-06-16 09:05:48 发布

weixin_30602505

最新推荐文章于 2022-06-16 09:05:48 发布

阅读量431

点赞数

文章标签：数据库操作系统 java

原文链接：http://www.cnblogs.com/tyydevelop/p/7771176.html

版权

工作中用到不安装oracle服务端，只安装客户端。

一：首先要到下载rpm包，使用如下，命令安装

rpm -ivh oracle/oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm

//yum install glibc.i686 安装错版本包使用这个修复

/usr/lib/oracle/11.2/client64 oracle环境位置

如需要连接远程oracle，须配置TNS
#mkdir -p /usr/lib/oracle/11.2/client64/network/admin
#vi /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora

加入一个Oracle数据库tns信息
#tnsnames.ora Network Configuration File: /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora
orcl =
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.61.4.43)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
红色标示字符串各自对应当前服务器的IP地址与全局数据库名

vi ~/.bashrc 具体配置信息如下

export ORCL_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORCL_HOME/lib
export TNS_ADMIN=$ORCL_HOME/network/admin
export PATH=$PATH:$ORCL_HOME/bin

ORCL_HOME的具体路径以本地安装路径配置

#vi /etc/ld.so.conf 加入
/usr/lib/oracle/11.2/client/lib/
#/sbin/ldconfig

重启一下环境变量，或者用户logout/login一次
#source ~/.bashrc

这样重新运行
# sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 8 11:09:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
成功了

测试一下
#sqlplus myOracleUsername/password@PRODB2
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 8 11:11:56 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select * from tab;
成功。

如果其他用户需要使用sqlplus，需要在该用户的~/.bashrc文件中添加上面提到的3行环境变量

sqlplus64 imei3_dev/imei3_dev@10.61.4.43/orcl 登录命令

二：linux下导出数据LinuxOracle.sql脚本为

set colsep '|'
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set term off
set trims on
set heading off
set timing off
SET termout off
set trimout on
set trimspool on
SET linesize 2500
SPOOL root/ccc.txt
SELECT '2017-10-19 17:59:23','20171018', t.* from tyy t;
SPOOL OFF
quit

shell脚本为

#!/bin/sh　　sqlplus64 imei3_dev/imei3_dev@10.61.4.43/orcl　　@/root/LinuxOracle.sql

linux下执行shell脚本即可实现远程将oracle数据库表数据down到本地。

load data local infile 'F:\ccc.txt' into table out_table_030 character set 'utf8' fields terminated by ' ' (execute_time,batch_no,id,name,score) //导入数据

转载于:https://www.cnblogs.com/tyydevelop/p/7771176.html