venom24

Oracle 的安装部署以及 sqlplus 的使用

 713  4 mins

由于网络原因，无法通过本地客户端连接服务器上的 Oracle，只能登上跳板机或者其他服务器，通过命令行的方式连接 Oracle。

要通过命令行连接 Oracle 就得先有一个客户端，然后通过 Oracle 提供的 sqlplus 工具进行远程连接。

Oracle 不同的版本会有一定的区别，这里使用的是 11g 的版本，12c 的版本待补充。

使用 docker 快速安装 Oracle

为了提高容错，这里使用 docker 快速部署

1
2

# 阿里云镜像
docker pull registry.cn-hangzhou.aliyuncs.com/qida/oracle-xe-11g

1

docker run -d -p 1521:1521 --name oracle11 registry.cn-hangzhou.aliyuncs.com/qida/oracle-xe-11g

本地验证

先登录本地的 Oracle 试下部署是否成功

先进入容器

1

docker exec -it oracle11 bash

使用 sqlplus 登录

1
2
3
4

# 密码默认为 oracle
sqlplus / as sysdba
Enter user-name: system
Enter password: [oracle]

创建新用户

1

create user root identified by root123456;

给新用户授权 dba

1

grant connect,resource,dba to root;

设置密码有效期为无限制

1

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

修改数据库最大连接数

1

alter system set processes=2000 scope=spfile;

登录成功后就可以退出了

添加远程 Oracle 信息

修改 Oracle 的 tnsname.ora 文件

1
2

cd $ORACLE_HOME/network/admin/
vi tnsnames.ora

增加配置：

XXX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Oracle-IP)(PORT = Oracle-PORT))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xxx)
    )
)

这个是即时生效的，不用重启

tnsping 测试连通性

使用 tnsping 测试是否网络是否可以连通

1
2
3
4

tnsping XXX
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Oracle-IP)(PORT = Oracle-PORT))) (CONNECT_DATA = (SERVICE_NAME = XXX)))
OK (10 msec)

如果出现 🆗，说明网络是通的

sqlplus 远程登录

使用 sqlplus 登录服务端的 Oracle

1
2

# XXX 为前面增加的配置
sqlplus user/password@XXX

sqlplus 初始化脚本

在 $ORACLE_HOME/sqlplus/admin/ 下有一个 glogin.sql 脚本，这个脚本会在每次初始化时自动调用

通用配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 设置编辑器用vi打开，windows客户端可以换成NotePad 
define _editor=vi 
-- 设置dbms_output输出缓冲区大小 
set serveroutput on size 1000000 
-- 设置输出格式 
set long 200 
set linesize 500 
set pagesize 9999 
-- 去除重定向输出每行拖尾空格 
set trimspool on 
-- 设置name列长 
col Name format a80 
-- 输出分隔符
set colsep '|'
-- 显示脚本中的命令的执行结果
set termout off
-- 查询当前实例名 
col global_name new_value gname 
define gname=idle 
column global_name new_value gname 
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, 
length(global_name), dot-1) ) global_name 
  from (select global_name, instr(global_name,'.') dot from global_name ); 
set sqlprompt '&gname _DATE> ' 
-- 设置session时间格式 
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; 
set termout on

中文乱码

查看 Oracle 的字符集

1
2
3
4
5
6
7
8

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

查看系统的字符集

1
2

echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

修改本次会话字符集

1

export NLS_LANG=AMERICAN_AMERICA.UTF8

日期格式化

1
2

export NLS_DATE_LANGUAGE="SIMPLEIFIED CHINESE"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

或者修改 /etc/profile 文件

格式化输出

指定行的长度

1
2
3

SQL> show linesize;
linesize 80
SQL> set linesize 150;

指定页的行数

1
2
3

SQL> show pagesize;
pagesize 14
SQL> set pagesize 50;

设置字段长度

1
2
3
4

-- 将列 FIELD1（字符型）显示最大宽度调整为 20 个字符
SQL> col FIELD1 format a20;
-- 将列 FIELD2（num型）显示最大宽度调整为 5 个字符
SQL> col FIELD2 format 99999;

设置合适的宽度后，可以放到 glogin.sql 中，这样下次登录时就不需要重新设置了

删除/移动光标/历史命令

删除命令可以使用：Ctrl+Backspace

左右移动光标和选择历史命令需要安装第三方工具包

客户端连接 Oracle

默认情况下，Oracle 的监听服务可能没启，或者就是监听着本地的 host

如果需要使用客户端连接 Oracle，需要使 Oracle 监听服务端 ip

查看监听服务状态

1

lsnrctl status

如果出现：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

root@3abc9992f092:/# lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 03-AUG-2023 07:52:59

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=3abc9992f092)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

数据没有启动监听，而且监听的 HOST 需要修改

先修改，监听配置文件：$ORACLE_HOME/network/admin/listener.ora

然后启动监听

1

lsnrctl start

如果出现了：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 03-AUG-2023 07:54:41

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                03-AUG-2023 07:53:15
Uptime                    0 days 0 hr. 1 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/3abc9992f092/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=3abc9992f092)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=3abc9992f092)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

说明启动监听了

然后就可以使用客户端连接 Oracle

Load Comments?