preface
Friends who often use Oracle database should be familiar with the command-line tool sqlplus. Basically, you can't work without it every day, but this tool is not easy to use:
- sqlplus under Linux system cannot switch the context to view and edit the history command. The wrong command can be deleted only by pressing CTRL
- For SQL query, the output result format is disordered. You need to manually SET and COL to adjust the column width each time
- The instance name and login user are not displayed in the current session, which is easy to misoperate the user, and the display of user information is not obvious enough
📢 Note: the above problems are existing in the default configuration of sqlplus.
So here comes the problem. Can all these be solved?
🎉 Of course, I wrote this article to introduce how to optimize the sqlplus command line! Listen to me slowly~
Demo configuration
Firstly, it is divided into two parts:
- Context switching: rlwrap + readline
- Beautify output format: globin sql
1. Context switching
I believe that when you use sqlplus command-line tool on Linux host, you will often encounter the situation that it is difficult to return the wrong command, or the newly entered command wants to be executed again and cannot be viewed up and down through the keyboard.
The above situation has always puzzled me. Later, after research, I found a solution! Let's share it with you. I hope it can also help you.
Using rlwrap + readline together can perfectly solve this problem. Next, I'll demonstrate how to configure and use it.
1. Linux host configuration yum source
Since you need to install readline using yum, configure the following:
##View system version cat /etc/system-release ##Upload the corresponding host version iso file scp rhel-server-7.9-x86_64-dvd.iso root@10.211.55.110:/soft
##Mount system iso image source mount -o loop /soft/rhel-server-7.9-x86_64-dvd.iso /mnt ##Configure 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
Through the above steps, we have successfully mounted the system image and can start installing redline.
2. Install readline dependency package
yum install -y readline*
If there is no system ISO image source, you can also download the readline installation package directly on the Internet for installation:
wget -c ftp://ftp.gnu.org/gnu/readline/readline-6.2.tar.gz
Upload installation package:
scp readline-6.2.tar.gz root@10.211.55.110:/soft
Unzip installation:
tar -zxvf readline-6.2.tar.gz cd readline-6.2 ./configure && make && make install
3. rlwrap installation
tar -xvf rlwrap-0.42.tar.gz cd rlwrap-0.42 ./configure && make && make install
Download address: https://github.com/hanslub42/rlwrap/releases/tag/v0.45.2
📢 Note: because the terminal connection of the Mac OS I use can be switched back, I can't demonstrate it. The following is a demonstration using XShell.
Cannot fallback and switch context without rlwrap:
When using rlwrap, you can switch back at will:
Through the above demonstration, it is easy to enter the wrong command without holding down the CTRL key to go back and switch the context history command, which can greatly improve the work efficiency.
4. Configure environment variables
In order to avoid the need to enter rlwrap to call the command every time, we configure the environment variable through the alias alias.
##Configuring 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 variable validation exit su - oracle
At this point, the rlwrap tool is configured!
2. Beautify the output format: globin sql
sqlplus automatically runs the script when it starts: globin sql .
- glogin. The SQL is stored in the directory $ORACLE_HOME/sqlplus/admin /
- Sqlplus executes this script whenever a user starts an sqlplus session and successfully establishes an Oracle database connection
- The script can write anything in the sqlplus script, such as system variable settings or other global settings that the DBA wants to implement
1. If it is not configured, the default is as follows:
At this point, I log in to SQL*PLUS and execute the sql query to see the output result format.
Glogin. Is not configured When using SQL, query results are output:
You can see that the format of the query result is very messy, and you can't see the current instance name and user name after connecting it.
2. Configure globin sql
cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql --Settings editor vi Open, windows The client can be replaced with NotePad define _editor=vi --set up dbms_output Output buffer size set serveroutput on size 1000000 --Format output set long 200 set linesize 500 set pagesize 9999 --Remove trailing spaces per line of redirected output set trimspool on --set up name Column length col Name format a80 --Query current instance name 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> ' --set up session Time format ALTER SESSION SET nls_date_format = 'HH24:MI:SS'; set termout on EOF
After configuring globin After SQL, the query result is output:
Through the above configuration, after sqlplus connection, the output format is obviously more beautiful and the display is more humanized.
The specific configuration can be configured according to personal common. For example, you can configure the query table space utilization rate. You can see the table space utilization rate every time you open it to prevent the explosion of data files.
--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 || '%' 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;
--Query 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;
So far, globin SQL has been configured. Welcome 👏🏻.
Write at the end
glogin.sql needs to be configured carefully. Do not write commands that you do not understand!
One way of the famous bitcoin blackmail virus is through globin SQL for injection.
Refer to official documents:
Configuring SQL*Plus: https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#SQPUG012