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.