There is no way to shrink a TEMPFILE, although the documentation suggests that
it might be possible. Even if the tablespace does not contain an active sort
segment (as shown by V$SORT_SEGMENT) you will still get the ORA-3297 error (file
contains used data beyond requested RESIZE value) if you attempt to shrink it.
The solution is to drop and recreate the tablespace. Fortunately, that is no
great hardship with these tablespaces, because Oracle does not need to reformat
all the data blocks during tablespace creation. You can create a tempfile
tablespace in just seconds, regardless of its size.


-----Original Message-----
Sent: Friday, 25 August 2000 21:18


I have a temporary tablespace with locally managed extents feature. Because
of huge sorting the tablespace size has
grown from 250M to 2.6GB size. After the transaction has got committed the
temporary tablespace remains to 2.6GB.
When i issued the command 'Alter database tempfile 'filename' resize 250M'
It gave me the error :
ORA-03297: file contains used data beyond requested RESIZE value
I can't able to resize this file why?
But if the file is managed by Dictinory i can able to resize the temporary
tablespace.
Can you help to resolve the issue.