Below are list of SQL*Plus commands that can be useful sometimes.
One major advantage of SQL*Plus is for testing, since this is not changed and is same from release to release, we can use as a tool to reproduce any testing.
Instead of executing below commands every time we login to sql*plus, we can add all below commands
into login.sql file (at product\11.2.0\dbhome_1\sqlplus\admin ) so that they are executed automatically everytime when a user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
Below settings are picked up from Tom Kyte's (http://www.facebook.com/pages/Tom-Kyte-Ask-Tom-fan-page/189391211108914) book "Effective Oracle by Design"
Just copy paste the below in login.sql /glogin.sql
REM turn off the terminal output - make it so SQLPLUS doesnot
REM print anything when we log in
set termout off
REM default your editor here. SQLPlus has many individual settings
REM This is one of the most important ones
define _editor=vi
REM serveroutput controls whether your DBMS_OUTPUT.PUT_LINE calls
REM go into the bit bucket (serveroutput off) or get displayed
REM on screen. The format wrapped elements causes
REM sqlplus to preserve leading white space - very useful
set serveroutput on size 1000000 format wrapped
REM Here I set some default column widths for commonly queried columns
column object_name format a30
column segment_name a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped
column plan_plus_exp format a100
REM by default, a spool file is a fixed width file with lots of
REM trailing blanks. Trimspool removes these trailing balnks
REM making the spool file significantly smaller
set trimspool on
REM LONG controls how much of a LONG or CLOB sqlplus displays by default.
REM It defaults to 80 characters which in general is far too small.
set long 5000
REM This sets the default width at which sqlplus wraps the output.
set linesize 131
REM sqlplus prints columns headings every N lines of output this defaults to 14 lines.
set pagesize 9999
REM here is how we set my signature prompt in sqlplus to
REM username@database> Here we use NEW_VALUE concept to format a nice prompt string
REM that defaults to IDLE
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot
from global_name);
set sqlprompt '&gname> '
REM and lastly, we'll put termout back on sqlplus prints
REM to the screen
set termout on
