【原】教你玩轉(zhuǎn) SQLPLUS，工作效率提升 200%

LuciferLiu 2021-12-10

展開全文

前言

經(jīng)常使用 Oracle 數(shù)據(jù)庫的朋友,，應(yīng)該對(duì) sqlplus 這個(gè)命令行工具不會(huì)陌生,。基本上每天工作都離不開它,，但是這個(gè)工具有些不太好用：

Linux 系統(tǒng)下 sqlplus 無法上下文切換查看編輯歷史命令,，敲錯(cuò)命令需要按住 CTRL 鍵才能刪除
SQL 查詢，輸出結(jié)果格式錯(cuò)亂,，每次都需要手動(dòng) SET 和 COL 調(diào)整列寬
當(dāng)前會(huì)話不顯示實(shí)例名和登錄用戶,，容易誤操作用戶，用戶信息顯示不夠明顯

?? 注意： 以上均為 sqlplus 默認(rèn)配置下存在的問題,。

那么問題來了,，這些都可以解決嗎？

?? 當(dāng)然,，我寫這篇就是為了介紹如何優(yōu)化 sqlplus 命令行嘛,！且聽我慢慢道來哉~

演示配置

首先介紹下，主要分兩個(gè)部分：

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

1,、上下文切換

相信大家在 Linux 主機(jī)使用 sqlplus 命令行工具時(shí),，經(jīng)常會(huì)遇到命令輸錯(cuò)不好回退，或者剛輸入的命令想再次執(zhí)行,，無法通過鍵盤上下翻看的情況,。

上面的情況曾經(jīng)也一直困惑著我,，后來經(jīng)過研究,，我發(fā)現(xiàn)了解決方案！這就來分享給大家,，希望也能幫助到你,。

通過 rlwrap + readline 配合使用,，可以完美解決這個(gè)問題，接下來,，我就來演示一下如何配置使用,。

1、Linux 主機(jī)配置 yum 源

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

##查看系統(tǒng)版本
cat /etc/system-release
##上傳對(duì)應(yīng)主機(jī)版本iso文件
scp rhel-server-7.9-x86_64-dvd.iso [email protected]:/soft

##掛載系統(tǒng)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

通過以上步驟,，我們已經(jīng)成功掛載系統(tǒng)鏡像，可以開始安裝 redline,。

2,、安裝 readline 依賴包

yum install -y readline*

如果沒有系統(tǒng) ISO 鏡像源，也可以直接在網(wǎng)上直接下載 readline 安裝包進(jìn)行安裝：

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

上傳安裝包：

scp readline-6.2.tar.gz [email protected]:/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 來進(jìn)行演示,。

未使用 rlwrap 時(shí),，無法回退和切換上下文：

使用 rlwrap 時(shí)，可任意切換回退：

通過上述演示,，已經(jīng)可以輕松做到命令輸錯(cuò)無需按住 CTRL 鍵回退和上下文歷史命令切換,，可以大大提升工作效率。

4,、配置環(huán)境變量

為避免每次都需要輸入 rlwrap 來調(diào)用命令,，我們通過 alias 別名來配置環(huán)境變量實(shí)現(xiàn)。

##配置oracle用戶環(huán)境變量
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

##環(huán)境變量生效
exit
su - oracle

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

2、美化輸出格式 glogin.sql

sqlplus 在啟動(dòng)時(shí)會(huì)自動(dòng)運(yùn)行腳本：glogin.sql ,。

glogin.sql 存放在目錄 $ORACLE_HOME/sqlplus/admin/ 下
每當(dāng)用戶啟動(dòng) sqlplus 會(huì)話并成功建立 Oracle 數(shù)據(jù)庫連接時(shí),，sqlplus 就會(huì)執(zhí)行此腳本
該腳本可以寫入在 sqlplus 腳本中的任何內(nèi)容，例如系統(tǒng)變量設(shè)置或 DBA 想要實(shí)現(xiàn)的其他全局設(shè)置

1,、未做配置時(shí),，默認(rèn)如下：

此時(shí)，我登錄SQL*PLUS并執(zhí)行sql查詢,，看一下輸出結(jié)果格式,。

未配置 glogin.sql 時(shí)，查詢結(jié)果輸出：

可以看到,，查詢結(jié)果格式很亂,，而且連進(jìn)去之后也看不到當(dāng)前實(shí)例名和用戶名。

2、配置 glogin.sql

cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql
--設(shè)置編輯器用vi打開,，windows客戶端可以換成NotePad
define _editor=vi
--設(shè)置dbms_output輸出緩沖區(qū)大小
set serveroutput on size 1000000
--設(shè)置輸出格式
set long 200
set linesize 500
set pagesize 9999
--去除重定向輸出每行拖尾空格
set trimspool on
--設(shè)置name列長(zhǎng)
col Name format a80
--查詢當(dāng)前實(shí)例名
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> '
--設(shè)置session時(shí)間格式
ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
set termout on
EOF

配置完 glogin.sql 后,，查詢結(jié)果輸出：

通過以上配置，sqlplus 連接后,，明顯輸出格式更加好看,，顯示更加人性化。

具體配置可根據(jù)個(gè)人常用進(jìn)行配置,，比如可以將查詢表空間使用率配置進(jìn)去,，每次打開都可以看到表空間使用率，防止數(shù)據(jù)文件撐爆,。

--查詢表空間使用率
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;