SQL*Plus hints and tips

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

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