Oracle temporary file space problem (was FW: QUIZ truncation vs varchar)

[fernando.olmos at hpa.com.au]

Well, the cat is finally out of the bag!

Darren, Joe, etc;

The problem with the temporary file space is [1] it's not big enough
-OR- [2] the sort complex is too big for the existing temporary file
space.

Unfortunately we don't have a HPA, so we have to refer Oracle db matters
directly to Oracle for help - which as some of you know is perhaps not
worth the salt!

So I tinkered, tinkered, and I finally got it.

I simply removed the sort from the cursor - and of course try and get a
smaller range of data.

So without further adieu, the cursor links work great (as I expected)
and I have reduced a sample month's worth of data from 45 minutes to
under 7 seconds!

The temporary space issue is still an issue, but never the less, there
is always a work around and persistence eventually dug it out of the
woodwork.

Like Joe said, SQL cursors are more akin to a cat - ie: there are many
ways to skin it!!

So in the end I managed to get a subfile variable declared with two date
ranges (start and end date) and linked that into a sql cursor with
variables in the WHERE clause. The trick here is TO NOT SORT THE DATA IN
THE CURSOR, but to leave the sort for Powerhouse to manage because it
creates a temporary swap file in our disc, which is more than big enough
to accommodate the sort.

Again, as always I am debited to Joe, Darren and co for helping me
through these issues.

Cheers


-----Original Message-----
From: fernando.olmos at hpa.com.au [mailto:fernando.olmos at hpa.com.au] 
Sent: 07 September 2005 23:36
To: darren.reely at latticesemi.com
Cc: atla38 at dsl.pipex.com
Subject: RE: QUIZ truncation vs varchar

Thanks Darren. That's a neat one.

Whilst I've got you (and you too Joe), can you tell me what this error
means? I am again playing with cursors for another performance issue
(same tables as the original emails I posted to the forum).

*E* Data access error. (CRS_INVOICED_MSTDET)
*E* DMS-E-GENERAL, A general exception has occurred during operation
'asynchronous open'. (CRS_INVOICED_MSTDET)
*E* ORA-25153: Temporary Tablespace is Empty

 (CRS_INVOICED_MSTDET)

Records selected:  0

<SNIP>