If your TEMP tablespace is extending up to 18 GB and you try to lock it down to a smaller size, aren 't you setting yourself up for ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment by XXX in tablespace TEMP errors? You might want to fix what is causing it to grow rather than limiting growth.
I 've be very interested in finding out what SQL is causing your TEMP tablespace to grow so much. Must be a lot of sorting and grouping going on. I 'd be looking for Union queries also.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle@(protected)
618-622-4145
> -- --Original Message-- --
> From: David Boyd [SMTP:davidb158@(protected)]
>
> Hi List,
>
> Our TEMP tablespace took Oracle default setting that was auto extending up
> to 32 GB. Last week the tablespace had grown to 18 GB. I tried to drop the
> tablespace after I removed any body 's temporary tablespace to a newly
> created temp tablespace with much smaller size. The dropping process is
> very slow. I started last Friday. The used size in the old temp tablespace
> had been decreased from 18 GB to 3.8 GB yesterday afternoon. I issued the
> drop command through TOAD job scheduler. There was one failure with the job
> when I checked this morning. The dropping process seemed stopped. I had to
> kill the job. Now the used size in the old temp tablespace is coming back
> slowly even through I issued another dropping command in SQLPLUS. I was
> wondering if any one has any way to drop the temp tablespace quickly.
> Thanks in advance.
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
