“This is the second day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”
preface
- For those of you who use Oracle databases regularly, SQL*Plus is no stranger to command-line tools. It is indispensable for daily work, but this tool has some disadvantages:
- In Linux, SQL*PLUS cannot view historical commands in context. You need to press Ctrl to delete an incorrect command
- SQL query, output result format disorder, each time need to manually SET adjustment
- The instance name and login user are not displayed in the current session
Note: The preceding problems are in the default SQLPlus configuration.
So the question is, can all of this be fixed? Of course, I wrote this article to show how to optimize the SQL*Plus command line.
First of all, there are two parts:
- Context switch: rlwrap + readline
- Optimized output format: glogin. SQL
SQL * Plus optimization
1 Context switch rlwrap
- I believe that when you use THE SQL*Plus command line tool on the Linux host, you will often encounter a command input error can not be rolled back, or just enter the command to execute again, can not switch through the up and down page.
- The above situation has puzzled me for a long time, but I have found a solution to it. I would like to share it with you in the hope that it will help you. Rlwrap + readline solves this problem perfectly, so I’ll show you how to configure it.
1. Configure the yum source on the Linux host
Check the system version
cat /etc/system-release
Upload the iso file of the corresponding host versionSCP rhel - server - 7.9 - x86_64 - DVD. Iso [email protected]: / softCopy the code
Mount the system ISO image sourceMount -o loop /soft/ Rhel-server-7.9-x86_64-DVD. iso/MNT## Configure the yum mirror source
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
Copy the code
2. Install the readline dependency package
yum install -y readline*
Copy the code- If the system ISO image source is not available, you can download the readline installation package to install the ISO image.
Download the Readline package:
Wget -c ftp://ftp.gnu.org/gnu/readline/readline-6.2.tar.gzCopy the code
SCP readline 6.2. Tar. Gz [email protected]: / softCopy the code
Tar - ZXVF readline 6.2. Tar. GzcdReadline-6.2./configure && make && make installCopy the code3. Install RLwrap
Tar - XVF rlwrap - 0.42. Tar. Gzcd/configure && make && make installCopy the codeDownload: github.com/hanslub42/r…
Note: since my macOS terminal connection can be switched back, I cannot demonstrate this, so I will use XShell to demonstrate this.
- When rlwrap is not used, there is no way to rollback and switch contexts:
- When using rLwrap, you can switch back at will:
4. Configure environment variables
- To avoid having to type rlwrap to invoke the command each time, we configure the environment variable implementation through the alias alias.
Configure the Oracle user environment variables
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
## Environment variables take effect
exit
su - oracle
Copy the code
2 optimize the output format of glogin. SQL
SQL*Plus automatically runs the script glogin. SQL on startup.
- Glogin. SQL is stored under $ORACLE_HOME/sqlplus/admin/.
- Whenever the user starts the SQLSQL Plus session and successfully establish Oracle database connectionPlus will execute the script.
- This script can write anything in the SQL*Plus script, such as system variable Settings or other global Settings that the DBA wants to implement.
1. If no configuration is performed, the default values are as follows: 
Demo: Query result output without glogin. SQL configuration: 
2. Configure glogin. SQL
cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql
-- Set editor to open with VI, Windows client can be replaced with NotePad
define _editor=vi
-- Set the dbMS_output output buffer size
set serveroutput on size 1000000
-- Set the output format
set long 200
set linesize 500
set pagesize 9999
Remove trailing Spaces per line of redirected output
set trimspool on
-- Set the name column length
col Name format a80
-- Queries the current instance name
set termout off
col global_name new_value gname
define gname=idle
column global_name new_value gname
select lower(user) || The '@' || 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> '
-- Set the session time format
ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
set termout on
EOF
Copy the code
Query tablespace usage
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 || The '%' 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;
SQL > alter database backup
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;
Copy the code
Write in the last
Glogin. SQL needs to be carefully configured. Do not write commands that you do not understand.
The famous bitcoin ransomware, one way is through glogin. SQL injection.
Refer to official documentation:
SQL * Plus:docs.oracle.com/cd/E11882_0 you…
This is the end of sharing ~
If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.