(Picture from Oracle.com)

There are (more than) two things that I love with SQLcl : formatting and aliases.

I work with several Grid Infrastructure clusters, hosting hundreds of databases accross multiple nodes. I am very often typing the same queries, this is where SQLcl comes in handy. You can use a login.sql file with SQLcl, just like you would use it with SQL*Plus.
After some tests, I found out that SQLcl looks for a login.sql file in directory and subdirectories pointed by the environment variable SQLPATH.

I then inserted the following line in .bash_profile of all the servers (in an automated way, of course 😉 ) :

export SQLPATH=/path/of/my/choice

And created the following login.sql file in this directory :

set sqlformat ansiconsole

alias usr=select username, account_status, default_tablespace, created, profile from dba_users where oracle_maintained='N' order by username;
alias sess=select count(*), username, machine from v$session group by username, machine order by username, machine;
alias dblinks=select * from dba_db_links order by db_link;
alias registry=select comp_name, version, status from dba_registry;
alias invalid=select count(*), owner, object_type from dba_objects where status = 'INVALID' group by owner, object_type order by owner, object_type;

The first line is a formatting command. As described in this blog post by Jeff Smith, set sqlformat ansiconsole “‘smart’ formats the output to best fit the screen based on width of data per page of results and the width of your output panel”.

The 5 last lines are the definition of 5 aliases for the most frequently used queries. Defining an alias is very easy and those examples are quite self-explanatory.

• usr : basic information about users/schemas that are not Oracle-maintained
• sess : number of sessions by username and machine
• dblinks : all the database links
• registry : status of components loaded into the database (useful when you are have ongoing upgrades)
• invalid : number of invalid objects by owner and type (again, useful when you are have ongoing upgrades)

alias command lists the existing aliases (you should have pre-existing aliases) :

SQL> alias
dblinks
invalid
locks
registry
sess
sessions
tables
tables2
usr

alias list alias_name shows you the content of an alias :

SQL> alias list registry
registry
--------

select comp_name, version, status from dba_registry

And you can check the content of SQLPATH with : show SQLPATH