Two cool features of SQLcl

Oracle SQLcl (Command Line interface) offers many developer friendly features. The detail about this utility and a complete list of features can be found at it’s documentation site . Among these, I find ‘alias’ and ‘repeat’ particularly useful as it allows me to easily build adhoc monitoring utilities that we so often need. And I think many DBA or developers who uses Linux for development or running Oracle Database will have similar opinion.
In this blog post, I will explain how to get SQLcl, connect to database and then use alias and repeat to build an adhoc utility to monitor FRA usage.

How to get it and Install

SQLcl is not included as part of Oracle database software and can be downloaded from it’s download page on OTN. Given it’s usefulness, hopefully in future releases Oracle will include it with base software !
Make sure that you have Java 1.8 or above installed.
Unzip the software, which will create a directory sqlcl and sub-directories bin and lib. Add the bin directory to the PATH and invoke it as
‘sql user@dbname’.

[oracle@MYDB01 bin]$ sql bipul@PDB1
SQLcl: Release 12.2.0.1.0 RC on Thu Aug 10 13:21:32 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Password? (**********?) **********
Last Successful login time: Thu Aug 10 2017 13:21:38 +00:00
Connected to:
Oracle Database 12c EE High Perf Release 12.2.0.1.0 – 64bit Production
SQL>

 

Storing Scripts: alias

Use alias command to save a SQL Statement or PL/SQL block in the library that can be recalled later. It is very easy to create a new alias as shown below and helps to organise the database sql scripts in one place.
Below I am creating an alias for a SQL statement I use to check the utilization of flash recovery area.

SQL> alias FRA=select space_limit/1024/1024 space_limit
2 , space_used/1024/1024 space_used
3 , SPACE_RECLAIMABLE/1024/1024 space_reclaimable
4 , NUMBER_OF_FILES
5 from V$RECOVERY_FILE_DEST
6 ;

And execute the script by simply recalling it on the SQL prompt, as shown below:

SQL> FRA
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
18432 14110.8032 9973.45947 310
SQL>

Use ‘help alias’ to explore other sub commands like listing SQL statements of an alias or dropping an alias.

Repeat Execution: repeat

Another great advantage of SQLcl is repeating the execution of a SQL script without either writing a loop in shell script or manually executing it every few minutes. I find it very useful especially in a situation where I am loading data and want to frequently check the utilization of FRA.

From help on repeat sub command:

“Repeats the current sql in the buffer the specified times with sleep intervals
Maximum sleep is 120s”

The following command will repeat the execution of above mentioned FRA script 10 times at 60 seconds interval.

SQL> FRA
SQL> repeat 10 60
Running 1 of 10 @ 2:44:2.849 with a delay of 60s
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
18432 14110.8032 9973.45947 310

 

Conclusion:

SQLcl has lot more subcommands that you may find useful. It is worth checking it’s homepage and a video by Jeff Smith.