Teach you to play with sqlplus and improve your work efficiency by 200%

Posted by Heatmizer20 on Sat, 08 Jan 2022 04:12:29 +0100


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@
##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@

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'

##Environment variable validation
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

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
select tbs_used_info.tablespace_name,
       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,
                                  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

select input_type,
               'yyyy-mm-dd hh24:mi:ss'),
               'yyyy-mm-dd hh24:mi:ss'),
  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