Forum OpenACS Q&A: Response to Oracle temp tablespace is huge!

Posted by Sebastiano Pilla on
What's the version of Oracle you're using? If >= 8.1.5, you could use a locally-managed temporary tablespace with tempfiles, and you could avoid making physical backups of it.

It's possible that this particular datafile was created with the default pctincrease 50 and with autoextend on, but without a maxsize limitation. I suggest to drop and recreate the temporary tablespace, but before doing anything make sure that no permanent object has been created by accident in this tablespace.

To create a brand new temporary tablespace, use something like:
create temporary tablespace TEMP
  tempfile '/ora8/data/temp01.dbf'
  size 50M
  autoextend on
  next 10M
  maxsize 300M
  extent management local
  uniform size 64K;
A couple of notes: you have to use tempfile rather than datafile (and this file won't ever show up in dba_data_files), and the value of the uniform size should be equal to the value of the sort_area_size initialization parameter.