i'm working with 11g on windows 2003 server. i often issue this statement
alter tablespace temp shrink space
this command run successfully and if we have 5 tempfile in temp tablespace. 4 temfile shrink successfully but one always remain as it my question why this happen? All tempfile should be shrink.
when i tried to shrink that temfile seperatly by issuing it give me error ORA-03214: File Size specified is smaller than minimum required
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE 'E:\app\administrator\oradata\ORCL\DA
ALTER TABLESPACE TEMP SHRINK TEMPFILE 'E:\app\administrator\oradata\ORCL\DATAFIL
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
This clause is valid only for temporary tablespaces. It lets you reduce the amount of space the tablespace is taking. In the optional KEEP clause, the size_clause defines the lower bound that a tablespace can be shrunk to. It is the opposite of MAXSIZE for an autoextensible tablespace. If you omit the KEEP clause, then the database will attempt to shrink the tablespace as much as possible as long as other tablespace storage attributes are satisfied.
sir i have checked shrink with keep statement on another server but it behavior is same i issue following command
alter tablespace temp shrink space keep 40m
but after shrink i saw the 4 files shrink and strangly their size is 2mb instead 40m. if i omit keep statement it also shrink the size to 2mb. and one tempfile remain as it why it also shrink 4 files
i think shrink temfile also possible but not sure becoz it never run successfully