Optimizing work environment. Part II, command line

(the first part can be found here)

Now let’s consider command line. There is a lot of things that can be done to improve user experience here as well.

1) Maintain a local script repository on the machine(s) you’re using the most. To make sqlplus search there, define SQLPATH variable (using Control Panel => System => Advanced => Environment variables on Windows, or shell initialization scripts such as .profile or .bash_profile on Unix).

2) To facilitate synchronization of scripts among different environments, use web-repositories. One option here is to go with a plain http (or ftp) repository e.g. using any (free or paid) hosting service, or your own web server if you have any. The other option is to go with a code repository tool like git that utilizes the secure (https) protocol. Plain http has the advantage of you being able to run scripts directly from the online repository (see here for more detail). But if your internet connection requires proxy, this may prove difficult (setting HTTP_PROXY variable might help, but I myself had no luck with it).

3) Use login.sql (glogin.sql) script in SQLPATH directory to configure sqlplus the way you like. For example, you can use “set sqlprompt” to make the prompt display information about the current connection:

set sqlprompt "_user@_connect_identifier"

4) You can change terminal window title to display additional information. In Windows, you can simply do

host title &mytitle

where mytitle can be defined dynamically, from a SQL statement.

For example, I have a script to display details of current connection, loosely based on Tanel Poder’s me.sql:

set linesize 200
column me format a80 
set termout off
define me=''

select user || '@' || i.instance_name || ' session ' || s.sid || ':' || s.serial# || ' OS process ' || spid me
from v$instance i,
     v$session s,
     v$process p
where s.paddr = p.addr
and s.sid = userenv('sid');

set termout on

Once I run this script, I can do

host title &me

and the terminal window will show all connection details, including sid/serial#.

On Unix environments the terminal window title can be set with the echo command:

host echo -ne "\033]0; some title here 07"

This can be added to [g]login.sql to set the terminal window title automatically. Having connection details displayed in the terminal window is very convenient, because you immediately know the sid if you need to kill the session, send a signal to it, enable trace file etc. Similarly, if you need to do something with the process on the OS level, you immediately know the process id (the spid).

4) Don’t settle for the default editor unless you’re really confortable with it. Otherwise, use _EDITOR variable to set the editor of your choice. For me, the best sqlplus script editor for Windows is Notepad++, hands down, so my login.sql contains:

define _EDITOR=notepad++

Obviously, you need to be sure that the path to the editor’s executable is added to the PATH environment variable.

5) One of the great features of sqlplus is navigating through command history using up/down keys. Unfortunately, it doesn’t work properly in many Unix environments. This problem can be solved by installing rlwrap utility. Once installed, create an alias for ‘rlwrap sqlplus’ and add it to a shell initialization script.

6) A few very simple things specifically for performance oriented work:
– use “set timing on” if you need to measure elapsed time of a SQL statement
– it helps to output current time in the beginning of a script that may take a long time, so that you immediately know how long it has been hanging
– “set appinfo on” will use the name of the sqlplus script being executed to set the “module” information (useful e.g. for ASH mining)
– don’t forget to set arraysize to an appropriate value when testing SQL statement that return a large number of rows (the default value 15; the maximum value is 5000) or the elapsed time will be greatly inflated because of many network roundtrips.

SQLPLUS is a powerful tool. But with the arrival of the new tool, sdsql, even more can be done from the command line. You can find an overview of its features here so I’ll just list a few features that I enjoy the most:

– new “alias” command, which provides functionality similar to SQL Developer reports
– “history time” will show elapsed times for previously run SQL
– arrow keys can be used to edit multi-line commands
– cd command can be used to navigate to a different directory if you want to run scripts from there
– ddl command displays the DDL for database objects
– sqlformat keyword allows to set format for outputting data in many formats: INSERT scripts, XML, JSON, CSV. My favorite is “sqlformat ansiconsole” which formats the output using the actual column width (and not the one based on column definition as with sqlplus).

Since this is an early edition of the tool, expectedly it suffers from a few bugs (for example, sometimes arrow keys instead of moving the cursor start beeping for no apparent reason), but I think it has a great future ahead of it!

Hope you find this useful in your work. And as always, if you have any command line tricks of your own, please feel free to share them in the comments!

4 thoughts on “Optimizing work environment. Part II, command line”

  1. I recommend for windows also:
    1. Ansicon: https://github.com/adoxa/ansicon
    btw, with ansicon we can use same title.sql for windows as for *nix.
    2. Console2: http://sourceforge.net/projects/console
    3. colored rlwrap prompt: rlwrap -icpred
    4. cygwin

    With this set it is equally comfortable for me on windows as on linux.
    A couple screenshots:

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s