SQLcl, login.sql, format and alias


(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

alias list alias_name shows you the content of an alias :

SQL> alias list registry

select comp_name, version, status from dba_registry

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

4 thoughts on “SQLcl, login.sql, format and alias

  1. Pingback: Oracle SQLcl – Oracle and other

  2. Pingback: OGB Appreciation Day : SQLcl – Floo Bar

  3. mahlstro@iu.edu

    You are my hero! I got so tired of setting up my environment when I would log in. Or get frustrated when I’d forget to set the output to ansiconsole. Now I don’t have to worry about it.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s