Re: tracking temp tablespace use in pl/sql

ssharma_at_clearnet.com wrote:
>
> any suggestions appreciated. if you don't have time to give a full
> explanation, please list a reference that might be useful. I'll do the
> legwork.
>
> I need to keep track of how much temp tablespace a program is using,
> ideally logging max use of each sql statement just after the statement
> finishes up.
>
> Any of the following would be useful
> - a function that returns max temp tablespace used by the most recent SQL
> - a wrapper that encapsulates an SQL and provides this same info.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
You can find the free space available in the temporary tablesace before the start of the query. Note this as value as VALUE1.

Just after the query has executed find the total free space available in the temporary tablespace. Store this value as VALUE2.

The difference betweeen these two values (VALUE1 - VALUE2) should be the amount of the space that the program has used.

Note : This assumes that only one program is doing the sort.

You can use this query to find free space available in the tablespace.
Select sum(bytes)/(1024*1024) value
From dba_free_space
Where tablespace_name = <temp tablespace name>;

Hope this helps.

Cheers

-- 
Sanjay T. Mathew (alsm1_at_micro.lucent.com)
Oracle DBA
Lucent Technologies,
555 Union BLVD,
Allentown 
PA 18103

Phone Number  Office 610 712 5967