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

前言

• 经常使用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源

##查看系统版本

cat /etc/system-release

##上传对应主机版本iso文件

scp rhel-server-7.9-x86_64-dvd.iso root@10.211.55.110:/soft

##挂载系统iso镜像源

mount -o loop /soft/rhel-server-7.9-x86_64-dvd.iso /mnt

##配置yum镜像源

mv /etc/yum.repos.d/* /tmp/

echo "[local]" >> /etc/yum.repos.d/local.repo

echo "name = local" >> /etc/yum.repos.d/local.repo

echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/local.repo

echo "enabled = 1" >> /etc/yum.repos.d/local.repo

echo "gpgcheck = 0" >> /etc/yum.repos.d/local.repo

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

2、安装readline依赖包

yum install -y readline* 复制代码

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

下载readline包：

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

 

上传安装包：

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

 

解压安装：

tar -zxvf readline-6.2.tar.gz

cd readline-6.2

./configure && make && make install

3、rlwrap安装

tar -xvf rlwrap-0.42.tar.gz

 cd rlwrap-0.42

./configure && make && make install

下载地址：github.com/hanslub42/r…

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

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

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

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

4、配置环境变量

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

##配置oracle用户环境变量

cat <<EOF>>/home/oracle/.bash_profile

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

alias lsnrctl='rlwrap lsnrctl'

alias asmcmd='rlwrap asmcmd'

alias adrci='rlwrap adrci'

alias ggsci='rlwrap ggsci'

alias dgmgrl='rlwrap dgmgrl'

EOF

##环境变量生效

exit

su - 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

cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql

--设置编辑器用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 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 = 'HH24:MI:SS';

set termout on

EOF

 

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

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

--查询表空间使用率

col TABLESPACE_NAME for a20

select tbs_used_info.tablespace_name,

       tbs_used_info.alloc_mb,

       tbs_used_info.used_mb,

       tbs_used_info.max_mb,

       tbs_used_info.free_of_max_mb,

       tbs_used_info.used_of_max || '%' used_of_max_pct

  from (select a.tablespace_name,

               round(a.bytes_alloc / 1024 / 1024) alloc_mb,

               round((a.bytes_alloc - nvl(b.bytes_free,

                                          0)) / 1024 / 1024) used_mb,

               round((a.bytes_alloc - nvl(b.bytes_free,

                                          0)) * 100 / a.maxbytes) used_of_max,

               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,

                                                       0)) / 1048576) free_of_max_mb,

               round(a.maxbytes / 1048576) max_mb

          from (select f.tablespace_name,

                       sum(f.bytes) bytes_alloc,

                       sum(decode(f.autoextensible,

                                  'YES',

                                  f.maxbytes,

                                  'NO',

                                  f.bytes)) maxbytes

                  from dba_data_files f

                 group by tablespace_name) a,

               (select f.tablespace_name,

                       sum(f.bytes) bytes_free

                  from dba_free_space f

                 group by tablespace_name) b

         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info

 order by tbs_used_info.used_of_max desc;

--查询备份

col status for a10

col input_type for a20

col INPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_DISPLAY for a10 

col TIME_TAKEN_DISPLAY for a10

select input_type,

       status,

       to_char(start_time,

               'yyyy-mm-dd hh24:mi:ss'),

       to_char(end_time,

               'yyyy-mm-dd hh24:mi:ss'),

       input_bytes_display,

       output_bytes_display,

       time_taken_display,

       COMPRESSION_RATIO

  from v$rman_backup_job_details

 where start_time > date '2021-07-01'

 order by 3 desc;

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

写在最后

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

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

参考官方文档：

Configuring SQL*Plus：docs.oracle.com/cd/E11882_0…

---

本次分享到此结束啦~

作者：Lucifer三思而后行
链接：https://juejin.cn/post/7025790244536451103
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。