Tuesday, April 6, 2010

Running Your Oracle Query In The Background

I hate Oracle.  That given, sometimes you have to run a query that takes FOREVER, and you want to go home with your laptop.  If you have access to a server with SQL*Plus, you can run the query there in the backgrund.  Here's how:

First, create a text file containing your query.

Always start with
SET PAGESIZE 50000 SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF SPOOL output.html
Then, place your query under that.  Then, end with
; SPOOL OFF quit;
To run the query, ssh into your server,
nohup sqlplus username/password@oracleServer @nameOfTheQuery.sql &

Once the query is complete, you should be able to open the resulting HTML file with OpenOffice Sheet to view as a spreadsheet.  I like to paste the query into the second sheet for reference, reformat the columns to the proper space, and re-save as an XLS for a customer or an OpenDocument workbook for myself (because it's smaller and open).

More information

1 comment:

  1. This is a very helpful to run the query in background.. Thanks...

    ReplyDelete

AddThis

Bookmark and Share