教你玩轉(zhuǎn) SQLPLUS，工作效率提升 200%
前言
經(jīng)常使用 Oracle 數(shù)據(jù)庫(kù)的朋友，應(yīng)該對(duì) sqlplus 這個(gè)命令行工具不會(huì)陌生?；旧厦刻旃ぷ鞫茧x不開(kāi)它，但是這個(gè)工具有些不太好用：
- Linux 系統(tǒng)下 sqlplus 無(wú)法上下文切換查看編輯歷史命令，敲錯(cuò)命令需要按住 CTRL 鍵才能刪除
- SQL 查詢(xún)，輸出結(jié)果格式錯(cuò)亂，每次都需要手動(dòng) SET 和 COL 調(diào)整列寬
- 當(dāng)前會(huì)話不顯示實(shí)例名和登錄用戶，容易誤操作用戶，用戶信息顯示不夠明顯
注意： 以上均為 sqlplus 默認(rèn)配置下存在的問(wèn)題。
那么問(wèn)題來(lái)了，這些都可以解決嗎?
當(dāng)然，我寫(xiě)這篇就是為了介紹如何優(yōu)化 sqlplus 命令行嘛!且聽(tīng)我慢慢道來(lái)哉~
演示配置
首先介紹下，主要分兩個(gè)部分：
- 上下文切換：rlwrap + readline
- 美化輸出格式：glogin.sql
一、上下文切換
相信大家在 Linux 主機(jī)使用 sqlplus 命令行工具時(shí)，經(jīng)常會(huì)遇到命令輸錯(cuò)不好回退，或者剛輸入的命令想再次執(zhí)行，無(wú)法通過(guò)鍵盤(pán)上下翻看的情況。
上面的情況曾經(jīng)也一直困惑著我，后來(lái)經(jīng)過(guò)研究，我發(fā)現(xiàn)了解決方案!這就來(lái)分享給大家，希望也能幫助到你。
通過(guò) rlwrap + readline 配合使用，可以完美解決這個(gè)問(wèn)題，接下來(lái)，我就來(lái)演示一下如何配置使用。
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 root@10.211.55.110:/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
通過(guò)以上步驟，我們已經(jīng)成功掛載系統(tǒng)鏡像，可以開(kāi)始安裝 redline。
2、安裝 readline 依賴(lài)包
- yum install -y readline*
如果沒(méi)有系統(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 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 的終端連接可以切換回退，所以無(wú)法演示，以下使用 XShell 來(lái)進(jìn)行演示。
未使用 rlwrap 時(shí)，無(wú)法回退和切換上下文：
使用 rlwrap 時(shí)，可任意切換回退：
通過(guò)上述演示，已經(jīng)可以輕松做到命令輸錯(cuò)無(wú)需按住 CTRL 鍵回退和上下文歷史命令切換，可以大大提升工作效率。
4、配置環(huán)境變量
為避免每次都需要輸入 rlwrap 來(lái)調(diào)用命令，我們通過(guò) alias 別名來(lái)配置環(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 工具就配置完成啦!
二、美化輸出格式 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ù)庫(kù)連接時(shí)，sqlplus 就會(huì)執(zhí)行此腳本
- 該腳本可以寫(xiě)入在 sqlplus 腳本中的任何內(nèi)容，例如系統(tǒng)變量設(shè)置或 DBA 想要實(shí)現(xiàn)的其他全局設(shè)置
1、未做配置時(shí)，默認(rèn)如下：
此時(shí)，我登錄SQL*PLUS并執(zhí)行sql查詢(xún)，看一下輸出結(jié)果格式。
未配置 glogin.sql 時(shí)，查詢(xún)結(jié)果輸出：
可以看到，查詢(xún)結(jié)果格式很亂，而且連進(jìn)去之后也看不到當(dāng)前實(shí)例名和用戶名。
2、配置 glogin.sql
- cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql
- --設(shè)置編輯器用vi打開(kāi)，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
- --查詢(xún)當(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 后，查詢(xún)結(jié)果輸出：
通過(guò)以上配置，sqlplus 連接后，明顯輸出格式更加好看，顯示更加人性化。
具體配置可根據(jù)個(gè)人常用進(jìn)行配置，比如可以將查詢(xún)表空間使用率配置進(jìn)去，每次打開(kāi)都可以看到表空間使用率，防止數(shù)據(jù)文件撐爆。
- --查詢(xún)表空間使用率
- 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;
- --查詢(xún)備份
- 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 已經(jīng)配置完成，歡迎食用👏🏻。
寫(xiě)在最后
glogin.sql 需要謹(jǐn)慎配置，沒(méi)有理解的命令盡量不要寫(xiě)入!
大名鼎鼎的比特幣勒索病毒，有一種方式就是通過(guò) glogin.sql 來(lái)進(jìn)行注入。
參考官方文檔：
Configuring SQL*Plus：https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#SQPUG012
