Make the most out of that dusty old DOS prompt!  (This page is for Windows 7 – things are a bit different in Windows 10).

Create I a file called S+.BAT that contains this:

@echo off
cd /D C:\TEMP
set "SQLPLUS_UID=%~nx0 %~1 %time::=.%"
set "SQLPLUS_UID=%SQLPLUS_UID: =_%"
set ORACLE_SID=%1%
set SQLPATH=D:\DBAScripts\%2%sql
cmd /k sqlplus / as sysdba
del /f %TEMP%\%SQLPLUS_UID%.sql
exit /B

In this example, I store my SQL scripts (short cuts for DBA reports and commands) in D:\DBAScripts.   (This might be a network share instead, for ease of management).  There is a subfolder for each major release, eg SQL scripts for version 11.2 in D:\DBAScripts\112sql .

The SQLPLUS_UID variable is used to replace the default SQL*Plus buffer file afiedt.buf with a filename generated from the ORACLE_SID and time.  (Implemented in login.sql).  The buffer file will be removed if the session ends cleanly (batch script not terminated).  It avoids afiedt.buf files being scattered around the place and avoids file permission problems or contention if more than one DBA is using SQL*Plus at the same time.

In the SQLPATH is a login.sql script that looks something like this:

clear col bre comp
set feedback off termout off echo off verify off
set linesize 168 pagesize 1000 arraysize 100 long 999999 longchunksize 672 trimspool on trimout on tab off
SET SERVEROUTPUT ON

set sqlprompt "&_user@&_connect_identifier SQL> "
set time on

-- SQLPLUS_UID should be set in s+.bat to a name that would usually be unique 
set editfile %TEMP%\%SQLPLUS_UID%.sql
define _editor=C:\WINDOWS\system32\notepad.exe
--define _editor="C:\Program Files\Notepad++\notepad++.exe"

set appinfo on
set appinfo 'S+'
exec dbms_application_info.set_client_info('Me');

alter session set "_adjust_literal_replacement"=true;

@global
set heading on feedback on

The file global.sql makes it easy to ensure I’ve logged into the correct instance.  It contains:

clear col bre comp
set feedback off termout off
def globalname = '';
def editionname = '';
col global_name new_value globalname noprint
select global_name from global_name;
col edition_name new_value editionname noprint
select sys_context('USERENV', 'SESSION_EDITION_NAME') edition_name from dual;
set termout on heading off
select
'NAME: '||name||'    '||
case when upper(name)<>upper(instance_name) then 'INSTANCE: '|| instance_name end ||'   '||
case when upper(name)<>upper(db_unique_name) then 'UNIQUE: '|| db_unique_name end ||'   '||
case when upper(name)<>upper('&&globalname') then 'GLOBAL: '|| '&&globalname' end ||'   '||
case when '&editionname'<>'ORA$BASE' then 'EDITION: '|| '&editionname' end names
from v$database, v$instance;
undefine globalname editionname
clear col bre comp
set feedback on termout on

Next, I create a shortcut for S+.BAT named “S+ ORA_SID” that has the ORACLE_SID and version as arguments, eg

  • “D:\DBAScripts\s+.bat” ORCL 112
  • Configure the “Start in” field to “C:\TEMP”.  (Create it if it doesn’t exist).
  • Configure the “Run” field to “Normal Window”.

Switch to the Options tab, enable QuickEdit mode and increase the buffer size (number of commands) and number of buffers (one for each concurrent SQL*Plus window).

s+ options tab

Switch to the Font tab and select Lucida Console, which is the best standard mono-spaced font for database work.  I use 14 point for 1360 x 768 resolution.

Switch to the Layout tab, and set the window to the appropriate dimensions and to enable the maximum history for scrolling back through SQL statement output.

s+ Layout tab

I have designed my scripts for 168 columns, but with some tables having many columns (eg v$session) you might prefer to set the maximum width and scroll horizontally when necessary, rather than have rows wrap around.  Be warned, if you set the buffer width to 9999, then 200MB of memory will be allocated when you open this window.  (Which could impact performance on small virtual machines).

If necessary, I shrink the scrollbars, title bars and borders to maximise the font space visible with 168 columns.  (Eg, Control Panel/Personalization/Window Color/ click ‘advanced appearance settings’, set Item drop box to ‘Scrollbars”).

Still Not as Good as PuTTy

Even though this is a good start to working efficiently and almost makes a UNIX DBA feel at home, there are a few imperfections.

  • When I use PuTTy, I configure it so it automatically logs everything I do to a file named by host and date/time.  This way, I have a complete working history that is very useful.  Unfortunately, the CMD window doesn’t have this feature.
  • Ctrl-C often exits out of SQL*Plus.  I am used to interrupting long running queries when running SQL*Plus in UNIX/Linux, but doing so in Windows can often lead to ending SQL*Plus.  I have attempted to mitigate this risk by reducing the array size and pausing output (Ctrl-S) before using Ctrl-C.  SQL*Plus is started with cmd /k to prevent the window from closing and losing the history when Ctrl-C is hit.
  • Copy and paste is  a little more awkward than PuTTy or other Windows Apps, especially when shifting focus to a remote desktop session on another monitor which can accidentally result in overwriting the clipboard, just by left clicking on a CMD window.

On the Bright Side

HTML reports are quick to view on Windows.

For example, on UNIX/Linux I have to copy and paste the SQL Monitor reports from PuTTy into a good Windows text editor before I can read the output properly.  On Windows, I can run a script that automatically displays the report in HTML.

SET MARKUP HTML ON SPOOL ON 
spool C:\temp\sql_monitor_report.html
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&SQL_ID',  type => 'HTML', report_level=>'ALL') AS report FROM dual;
spool off
SET MARKUP HTML OFF 
host C:\temp\sql_monitor_report.html

The same applies to other things like AWR reports, although they are not too bad to read in vi.

Also…

Up and down arrows give access to command history inside SQL*Plus, and you can even hit F7 for a menu of recent commands.

 

Advertisements

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