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
I then inserted the following line in
.bash_profile of all the servers (in an automated way, of course 😉 ) :
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 :