Shrinking the temporary tablespace

[oradba101]

Good morning, all

My temporary tablespace keeps growing. Right now, we are just using it to run a couple of complex procedures, views and queries but we only use it during working hours. Is there any way to shrink it as it does not seem to be shrinking on its own? I was under the impression that once the process using the space would automatically allocate.

Regards,

William Chadbourne
Oracle DBA

 

[Thargy]

William,

If memory serves, other threads in either this or the 9i forum mention the fact that the temporary tablespace doesn't shrink itself. I found one whopper up at 7 gigabytes, which was 70% of the total db requirements.

To answer your question specifically, use the following script, suitably modified for your particular installation.
The sample below knocks the beastie down from 1 gig to 500 Meg.

SQL> select file_name, tablespace_name, bytes from dba_temp_files;

FILE_NAME                                                                       
--------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES                                       
------------------------------ ----------                                       
E:\ORACLE\ORADATA\DEV_5\TEMP01.DBF                                              
TEMP                           1073741824                                       
                                                                                
SQL> ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\DEV_5\TEMP01.DBF' RESIZE 500M;

Database altered.

SQL> select file_name, tablespace_name, bytes from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES
------------------------------ ----------
E:\ORACLE\ORADATA\DEV_5\TEMP01.DBF
TEMP                            524288000

SQL>
/[code]

Sorry about the TGML, I must be having finger trouble.

Regards

Tharg

Grinding away at things Oracular

 

[markrem]

Performance is better if it does not shrink all the time. Why, because expanding is expensive and slows down the system. Let it get fat and stay that way, generally speaking but of course there are extremes and exceptions.

The problem with temp tbs NOT shrinking in 9i is related to improperly created files - user error. Make sure you create a locally managed temporary tablespace using temp files, and not a dictionary managed one that SMON cannot shrink. I've even seen 10g databases where the "DBA" created a temp tablespace using a permanent dictionary managed tablespace and datafiles, not tempfiles!


MarkRem
OCP 10g/9i