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

One comment

  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.

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 )

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

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