Re: Query from Oracle Reports caused temp tablespace to be full

Try checking v$mystat to see if *you* are causing a lot of sorts on disk. I would expect it may be that other users (v$sysstat) are causing perhaps too many sorts on disk at the same time? Perhaps you could alter your session to use a bigger sort_area_size/sort_area_retained_size assuming you didn't want to increase for everyone.

select n.name,m.value from v$mystat m,
v$statname n
where n.name like '%sort%'
and n.statistic# = m.statistic#

The temp tablespace should always be full - the only time it shrinks is when you restart the instance. You are confusing extent allocation with 'full'.

"Zeyad S" <sweidanz_at_yahoo.com> wrote in message news:3AEE5F1B.824503A3_at_yahoo.com...
> Hi all,
> Has anyone had any similar problem with running a query from Oracle
> reports that cause the Temp tablespace to get full? The same query
> sometimes run in seconds without any problem and another time it caused
> the temp to be full with around 1GB. The records in the tables are
> really few and i dont expect any problem even if we have many joins.
>
> The error that we received is:
> ORA-01237 cannot extend datafile 4 '/data/...../temp01.dbf
> ORA-19502: write error on file '/data/...../temp01.dbf
>
> Any help will be appreciated.
>
>
>
>
Received on Wed May 02 2001 - 06:27:47 CDT