教你玩转 SQLPLUS，工作效率提升 200% 工作输出结果格式错乱

2024-06-28 15:48:29 [百科] 来源：避面尹邢网

教你玩转 SQLPLUS，教玩工作效率提升 200%

作者：Lucifer 2021-12-10 08:39:50数据库其他数据库经常使用 Oracle 数据库的工作朋友，应该对 sqlplus 这个命令行工具不会陌生。效率基本上每天工作都离不开它，提升我写这篇就是教玩为了介绍如何优化 sqlplus 命令行嘛！且听我慢慢道来哉~

[[439756]]

前言

经常使用 Oracle 数据库的工作朋友，应该对 sqlplus 这个命令行工具不会陌生。效率基本上每天工作都离不开它，提升但是教玩这个工具有些不太好用：

Linux 系统下 sqlplus 无法上下文切换查看编辑历史命令，敲错命令需要按住 CTRL 键才能删除
SQL 查询，工作输出结果格式错乱，效率每次都需要手动 SET 和 COL 调整列宽
当前会话不显示实例名和登录用户，提升容易误操作用户，教玩用户信息显示不够明显

注意：以上均为 sqlplus 默认配置下存在的工作问题。

教你玩转 SQLPLUS，工作效率提升 200% 工作输出结果格式错乱

那么问题来了，效率这些都可以解决吗?

教你玩转 SQLPLUS，工作效率提升 200% 工作输出结果格式错乱

当然，我写这篇就是为了介绍如何优化 sqlplus 命令行嘛!且听我慢慢道来哉~

教你玩转 SQLPLUS，工作效率提升 200% 工作输出结果格式错乱

演示配置

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

上下文切换：rlwrap + readline
美化输出格式：glogin.sql

一、上下文切换

相信大家在 Linux 主机使用 sqlplus 命令行工具时，经常会遇到命令输错不好回退，或者刚输入的命令想再次执行，无法通过键盘上下翻看的情况。

上面的情况曾经也一直困惑着我，后来经过研究，我发现了解决方案!这就来分享给大家，希望也能帮助到你。

通过 rlwrap + readline 配合使用，可以完美解决这个问题，接下来，我就来演示一下如何配置使用。

1、Linux 主机配置 yum 源

由于需要使用 yum 安装 readline ，下面配置一下：

##查看系统版本 
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 安装包进行安装：

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

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

📢 注意：由于我使用的 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 工具就配置完成啦!

二、美化输出格式 glogin.sql

sqlplus 在启动时会自动运行脚本：glogin.sql 。

glogin.sql 存放在目录 $ORACLE_HOME/sqlplus/admin/ 下
每当用户启动 sqlplus 会话并成功建立 Oracle 数据库连接时，sqlplus 就会执行此脚本
该脚本可以写入在 sqlplus 脚本中的任何内容，例如系统变量设置或 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 后，查询结果输出：

通过以上配置，sqlplus 连接后，明显输出格式更加好看，显示更加人性化。

具体配置可根据个人常用进行配置，比如可以将查询表空间使用率配置进去，每次打开都可以看到表空间使用率，防止数据文件撑爆。

--查询表空间使用率 
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;