Linux安装sqlplus及Oracle连接配置

一、安装及配置

1、下载rpm包

Instant Client for Linux x86-64 (64-bit)

首先创建sqlplus目录，再通过网址下载rpm包，上传到sqlplus目录下。

mkdir /home/sqlplus

cd /home/sqlplus

rz 


[root@promote sqlplus]# ll
总用量 56396
-rw-r--r--. 1 root root 56355344 6月   8 15:37 oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root root   667308 6月   8 15:59 oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root root   721868 6月   8 15:59 oracle-instantclient-sqlplus-21.1.0.0.0-1.x86_64.rpm

 2、安装rpm包

rpm -ivh oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm --nodeps --force
rpm -ivh oracle-instantclient-sqlplus-21.1.0.0.0-1.x86_64.rpm --nodeps --force
rpm -ivh oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm --nodeps --force

3、配置Oracle连接

创建目录及tnsnames.ora文件，并写入Oracle配置

#创建目录
mkdir -p /usr/lib/oracle/11.2/client64/network/admin

#创建文件并写入
vim /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora


[root@promote sqlplus]# cat /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora
TPADCTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle2)
    )
  )
[root@promote sqlplus]# 

修改.bashrc文件,在末尾增加如下几行配置;

修改完.bashrc文件后，使用source ~/.bashrc(或者.~/.bashrc)就可以立刻加载修改后的设置，使之生效。

vi ~/.bashrc

#末尾添加
export  ORACLE_HOME=/usr/lib/oracle/11.2/client64
export  TNS_ADMIN=$ORACLE_HOME/network/admin
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export  LANG=zh_CN.UTF-8
export  NLS_LANG=AMERICAN_AMERICA.utf8

#修改完刷新生效
source ~/.bashrc

二、Oracle操作

1、运行sqlplus

sqlplus username/password@远端ip:端口号/服务名

[root@promote sqlplus]# sqlplus username/myPassword@192.168.1.105:1521/oracle2

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jun 8 17:25:49 2021
Version 21.1.0.0.0

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

Last Successful login time: Tue Jun 08 2021 17:24:59 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

2、sqlplus上运行存储过程

末尾需加上“/”

SQL> create or replace procedure DSM_GET_ALLDATA_BF as
  2    v_count     number;
  3    sum_count   number;
  4    v_sql       varchar2(2000);
  5    v_tablename varchar2(100);
  6  
  7    cursor table_loop is
  8      select table_name from user_tables;
  9  begin
 10    sum_count := 0;
 11    open table_loop;
 12    loop
 13      fetch table_loop
 14        into v_tablename;
 15      exit when table_loop %notfound;
 16      v_sql := 'select count(1) from ' || v_tablename;
 17      execute immediate v_sql
 18        into v_count;
 19      sum_count := sum_count + v_count;
 20    end loop;
 21    close table_loop;
 22  
 23    insert into dsm_allData
 24    values
 25      (to_char(sysdate - 1, 'yyyy-MM-dd'), sum_count);
 26    commit;
 27  end DSM_GET_ALLDATA_BF;
 28  /

Procedure created.