curious case of the missing spool file
Bit of a ghostly theme today.
One great aspect of my job is that I write SQL scripts that are reviewed and executed by other people. This suits me just fine. The less work for me, the better.
Obviously, I never add 'spool off' to the tail of a SQL*Plus script. This is a whole ten characters to type (including newline). Multiply this by all the SQL scripts you might produce in a lifetime and just think of how much time that would waste. In any case, everyone knows that 'exit' implies a 'spool off' anyway.
A while ago, a friendly Oracle DBA ran my SQL script and called me to tell me it had finished. I asked him to email the spooled output for review. He said there was no spooled output generated. I asked him to check the current directory (no), %ORACLE_HOME%\BIN directory (no), the Recycle Bin (no), search all local hard drives for 'patch_prod.lst' (no).
I asked him if the SQL*Plus session was still open and, if so, to simply type 'spool off'. He replied that the SQL*Plus session had already been ended. I was puzzled until I asked 'Is this the GUI version of SQL*Plus on Windows ?' 'Yes', he replied. 'Did you end the session by hitting the 'X' button ?' 'Yes'.
So, it turns out that the spool file is lost when Windows SQL*Plus is terminated using the 'X' button. I didn't know this fact because I normally use 'exit' to close any SQL*Plus session on Unix, Dos Command Window or Cygwin.
I had a suspicion that this gentleman was privately quite enjoying this moment and my discomfort so I said 'Hmm - that's a shame. I really wanted to check the spool to ensure that I had deleted the correct 7.9 million invoices from production'.
Later on, I asked myself what type of mindless idio person uses 'X' to terminate a Windows SQL*Plus session.
Then the answer struck me. A lazy one. Like me.
Related posts
Raptor is out of the cage
Oracle have released Raptor (a freely available GUI SQL query tool) which may be of interest to those of you who use Toad or are frustrated with the terseness of the SQL*Plus interface. Raptor is primarily a cross platform, PL/SQL developer tool, written in Java (60 MB download). The SQL output is a scrollable grid (like Toad) and there is the standard schema browser, PL/SQL debugger, SQL pretty printer, DDL generator and a graphical interface to explain plan for query tuning. All in all, a very promising release which fills a surprising (to me) gap in Oracle's range of database ... Read moreAm I am an Oracle luddite ?
Jeff Moss' article about the commercial and free versions of Toad and the incredibly tenacious, persistent breed of salesperson bred by Quest Software got me thinking about the Oracle DBA tools I use.- People
- SQL*Plus
- Statspack
- putty ...
Tom Kyte makes mistake shock
London, near England - Wednesday 2 November 2005 The Oracle community was reeling yesterday from the revelation that the universally respected, internationally renown technical expert and long standing Oracle employee, Thomas Kyte, had made a mistake. The error was discovered by a Senior Oracle DBA based in Solihull in the UK, Mr. N. Brightside who explained:"I was dearly looking forward to attending the UK Oracle User Group in Birmingham and hoped to get my copy of Tom Kyte's latest book, (Expert Oracle - Database Architecture) signed by Tom personally. However, at the last minute, my manager told me ...Read more
Oracle newbies, wizards and gurus
I recently stumbled upon Lisa Dobson's Oracle Newbies blog. Firstly, although I have never met Lisa, I admire her for two reasons. Firstly, she is about to give a presentation at the UK Oracle User Group in Birmingham. I am a good deal older than Lisa and the prospect of presenting to a large number of 'grumpy old men' would fill me with absolute dread.Seondly, I really like Lisa's rather self deprecating, modest statement on her profile
I've been an Oracle DBA for 3 years, so still consider myself a'Newbie'!
In my various jobs, I have met several ... Read more