SQL*Plus hints and tips

Posted by

I just posted a video on how I get the most out of my SQL*Plus usage.  You can check it out here

I reference various elements of my login.sql file throughout the video – so here’s login.sql file – feel free to utilise any parts of it for your benefit.

col column_name     format a30
col sql_text        format a64
col segment_name    format a30 trunc
col tablespace_name format a20 trunc
col program         format a30 trunc
col what            format a50 word_wrapped
col plan_plus_exp   format a100

set lines      130
set pages      99
set trimspool  on
set exitcommit OFF
set arraysize  100
REM set serverout on

set termout off

define gname = 'SQL'
column global_name new_value gname

define IS12 = ""
define NOT12 = ""
col x new_value IS12
col y new_value NOT12

with rel as 
( select substr(regexp_replace(banner,'^.*Release '),1,2) rel
  from v$version
  where rownum = 1 )
  case when rel = '12' then '--' end y,
  case when rel != '12' then '--' end x
from rel;

set verify off

select lower(user) || '@'||
&&IS12 case when sys_context('USERENV','CON_NAME') != 'CDB$ROOT' then lower(sys_context('USERENV','CON_NAME'))
&&IS12 else
&&IS12 end
chr(10)||'SQL' global_name
from v$instance;

set sqlprompt '&&gname> '
host title &&gname

set verify on
set termout on

col profile     format a30
col object_name format a30
col table_name  format a30
col view_name   format a30
col username    format a30
col user_name   format a30


  1. Thanks for sharing Connor.

    I have always wondered how you reproduce step-by-step commands that you ran and its output results at every step, when answering questions on AskTom. Do you spool the commands to a text file and then copy-paste onto the forum OR is there some other trick that you use?

    I would love to be able to reproduce what commands I ran and its results at every step so that I can share it with multiple team members or when giving a presentation.

  2. If you are doing DBA work then selects in your glogin.sql can cause ‘issues’.

    For example, you’ll be able to stop your database using Oracle Enterprise Manager Cloud Control, but you won’t be able to start it back up again using OEM. If you put them into a login.sql file you shouldn’t have issues.

      1. Basically what happens is this: If you connect to an instance that isn’t fully up, then your select statements in your login.sql and/or glogin.sql will generate an error (as would be expected).

        Unfortunately, some tools interpret this error as ‘something is broken and I should stop’.OEM CC is one of those tools. So, when you shut down your instance using the OEM CC Console, it works just fine. But, if you try to start up the instance using the agent deployed on a remote database server, OEM CC sees that error, and says “Something is wrong, I can’t start this database.” Now, that’s not true, but OEM CC doesn’t know that.

        Does that make it clearer? (I thought that is what I said before…)

  3. Hi Connor,
    Thanks for sharing the tips. Some of those tips would save a lot of time. Would you mind sharing the scripts you are using such as tab.sql, desc.sql etc on your github account so that we could leverage some of those scripts.


Got some thoughts? Leave a comment

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.