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).
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.
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.
Up and down arrows give access to command history inside SQL*Plus, and you can even hit F7 for a menu of recent commands.