【Oracle小技巧】手把手教你玩转SQL*Plus命令行，工作效率提升200%

内容阅读

在知识库中专注阅读，并随时返回相关工具与课程

大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。

前言

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

经常使用Oracle数据库的朋友，应该对SQL*Plus这个命令行工具不会陌生。每天工作都离不开它，但是这个工具有些缺点：

Linux系统下SQL*PLUS无法上下文查看历史命令，敲错命令需要按住Ctrl才能删除
SQL查询，输出结果格式错乱，每次都需要手动SET调整
当前会话不显示实例名和登录用户，提示不人性化

注意：以上问题均为SQLPlus默认配置下。

那么问题来了，这些都可以解决吗？当然，我写这篇就是为了介绍如何优化SQL*Plus命令行嘛！

首先介绍下，主要分两个部分：

上下文切换：rlwrap + readline

优化输出格式：glogin.sql

SQL*Plus优化

1 上下文切换 rlwrap

相信大家在Linux主机使用SQL*Plus命令行工具时，经常会遇到命令输错不好回退，或者刚输入的命令想再次执行，无法通过上下翻页切换的情况。
上面的情况曾经也一直困惑着我，后来我发现了解决方案，这就来分享给大家，希望能帮助到你。通过 rlwrap + readline 一起使用，可以完美解决这个问题，接下来，我就来演示一下如何配置使用。

1、Linux主机配置yum源

Bash

1##查看系统版本
2cat /etc/system-release
3##上传对应主机版本iso文件
4scp rhel-server-7.9-x86_64-dvd.iso root@10.211.55.110:/soft

在这里插入图片描述

Bash

1##挂载系统iso镜像源
2mount -o loop /soft/rhel-server-7.9-x86_64-dvd.iso /mnt
3##配置yum镜像源
4mv /etc/yum.repos.d/* /tmp/
5echo "[local]" >> /etc/yum.repos.d/local.repo
6echo "name = local" >> /etc/yum.repos.d/local.repo
7echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/local.repo
8echo "enabled = 1" >> /etc/yum.repos.d/local.repo
9echo "gpgcheck = 0" >> /etc/yum.repos.d/local.repo

在这里插入图片描述

通过以上步骤，我们已经成功挂载系统镜像，可以开始安装redline。

2、安装readline依赖包

Bash

1yum install -y readline*

如果没有系统ISO镜像源，也可以直接在上直接下载readline安装包进行安装。

下载readline包：

Bash

1wget -c ftp://ftp.gnu.org/gnu/readline/readline-6.2.tar.gz

在这里插入图片描述

上传安装包：

Bash

1scp readline-6.2.tar.gz root@10.211.55.110:/soft

在这里插入图片描述

解压安装：

Bash

1tar -zxvf readline-6.2.tar.gz
2cd readline-6.2
3./configure && make && make install

3、rlwrap安装

Bash

1tar -xvf rlwrap-0.42.tar.gz
2 cd rlwrap-0.42
3./configure && make && make install

下载地址：https://github.com/hanslub42/rlwrap/releases/tag/v0.45.2

注意：由于我macOS的终端连接可以切换回退，所以无法演示，以下使用XShell来进行演示。

未使用rlwrap时，无法回退和切换上下文：

在这里插入图片描述

使用rlwrap时，可任意切换回退：

在这里插入图片描述

通过上述演示，已经可以轻松做到命令输错无需按住Ctrl键回退和上下文历史命令切换，可以大大提升工作效率。

4、配置环境变量

为避免每次都需要输入rlwrap来调用命令，我们通过alias别名来配置环境变量实现。

Bash

1##配置oracle用户环境变量
2cat <<EOF>>/home/oracle/.bash_profile
3alias sqlplus='rlwrap sqlplus'
4alias rman='rlwrap rman'
5alias lsnrctl='rlwrap lsnrctl'
6alias asmcmd='rlwrap asmcmd'
7alias adrci='rlwrap adrci'
8alias ggsci='rlwrap ggsci'
9alias dgmgrl='rlwrap dgmgrl'
10EOF
11
12##环境变量生效
13exit
14su - oracle

在这里插入图片描述

至此，rlwrap工具就配置完成啦！

2 优化输出格式 glogin.sql

SQL*Plus 在启动时会自动运行脚本：glogin.sql 。

glogin.sql 存放在目录$ORACLE_HOME/sqlplus/admin/下。

每当用户启动 SQLPlus 会话并成功建立 Oracle 数据库连接时，SQLPlus 就会执行此脚本。

该脚本可以写入在 SQL*Plus 脚本中的任何内容，例如系统变量设置或 DBA 想要实现的其他全局设置。

1、未做配置时，默认如下：

在这里插入图片描述

此时，我登录SQL*PLUS并执行sql查询，看一下输出结果格式。

演示：未配置glogin.sql时，查询结果输出：

在这里插入图片描述

可以看到，查询结果格式很乱，而且连进去之后也看不到当前实例名和用户名。

2、配置glogin.sql

SQL

1cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql
2--设置编辑器用vi打开，windows客户端可以换成NotePad
3define _editor=vi
4--设置dbms_output输出缓冲区大小
5set serveroutput on size 1000000
6--设置输出格式
7set long 200
8set linesize 500
9set pagesize 9999
10--去除重定向输出每行拖尾空格
11set trimspool on
12--设置name列长
13col Name format a80
14--查询当前实例名
15set termout off
16col global_name new_value gname
17define gname=idle
18column global_name new_value gname
19select lower(user) || '@' || substr( global_name, 1, decode( dot, 0,
20length(global_name), dot-1) ) global_name
21  from (select global_name, instr(global_name,'.') dot from global_name );
22set sqlprompt '&gname _DATE> '
23--设置session时间格式
24ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
25set termout on
26EOF

在这里插入图片描述

演示：配置完glogin.sql时，查询结果输出：

在这里插入图片描述

通过以上配置，SQL*PLUS连接后，明显输出格式更加好看，显示更加人性化。具体配置可根据个人常用进行配置，比如可以将查询表空间使用率配置进去，每次打开都可以看到表空间使用率，防止数据文件撑爆。

SQL

1--查询表空间使用率
2col TABLESPACE_NAME for a20
3select tbs_used_info.tablespace_name,
4       tbs_used_info.alloc_mb,
5       tbs_used_info.used_mb,
6       tbs_used_info.max_mb,
7       tbs_used_info.free_of_max_mb,
8       tbs_used_info.used_of_max || '%' used_of_max_pct
9  from (select a.tablespace_name,
10               round(a.bytes_alloc / 1024 / 1024) alloc_mb,
11               round((a.bytes_alloc - nvl(b.bytes_free,
12                                          0)) / 1024 / 1024) used_mb,
13               round((a.bytes_alloc - nvl(b.bytes_free,
14                                          0)) * 100 / a.maxbytes) used_of_max,
15               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
16                                                       0)) / 1048576) free_of_max_mb,
17               round(a.maxbytes / 1048576) max_mb
18          from (select f.tablespace_name,
19                       sum(f.bytes) bytes_alloc,
20                       sum(decode(f.autoextensible,
21                                  'YES',
22                                  f.maxbytes,
23                                  'NO',
24                                  f.bytes)) maxbytes
25                  from dba_data_files f
26                 group by tablespace_name) a,
27               (select f.tablespace_name,
28                       sum(f.bytes) bytes_free
29                  from dba_free_space f
30                 group by tablespace_name) b
31         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
32 order by tbs_used_info.used_of_max desc;
33
34--查询备份
35col status for a10
36col input_type for a20
37col INPUT_BYTES_DISPLAY for a10
38col OUTPUT_BYTES_DISPLAY for a10 
39col TIME_TAKEN_DISPLAY for a10
40
41select input_type,
42       status,
43       to_char(start_time,
44               'yyyy-mm-dd hh24:mi:ss'),
45       to_char(end_time,
46               'yyyy-mm-dd hh24:mi:ss'),
47       input_bytes_display,
48       output_bytes_display,
49       time_taken_display,
50       COMPRESSION_RATIO
51  from v$rman_backup_job_details
52 where start_time > date '2021-07-01'
53 order by 3 desc;

在这里插入图片描述

至此，glogin.sql已经配置完成，欢迎食用👏🏻。

写在最后

glogin.sql 需要谨慎配置，没有理解的命令尽量不要写入。

大名鼎鼎的比特币勒索病毒，有一种方式就是通过glogin.sql来进行注入。

参考官方文档：

Configuring SQL*Plus：https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#SQPUG012

往期精彩文章

Oracle 一键巡检自动生成 Word 报告
 Oracle 一键安装合集
 Oracle一键安装脚本的 21 个疑问与解答
 Oracle一键巡检脚本的 21 个疑问与解答
 全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
 优化 Oracle：最佳实践与开发规范
 DBA 必备：Linux 软件源配置全攻略
 Linux 一键配置时钟同步全攻略

感谢您的阅读，这里是 Lucifer三思而后行，欢迎点赞+关注，我会持续分享数据库知识、运维技巧。

【Oracle小技巧】手把手教你玩转SQL*Plus命令行，工作效率提升200%

DBA 实战课程

【Oracle小技巧】手把手教你玩转SQL*Plus命令行，工作效率提升200%

前言

SQL*Plus优化

1 上下文切换 rlwrap

2 优化输出格式 glogin.sql

写在最后

往期精彩文章

DBA 实战课程

前言

SQL*Plus优化

1 上下文切换 rlwrap

2 优化输出格式 glogin.sql

写在最后

往期精彩文章