This discussion is archived
2 Replies Latest reply: May 14, 2009 11:02 PM by 650105 RSS

ORA-03214: File Size specified is smaller than minimum required

650105 Newbie
Currently Being Moderated
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
TAFILE\TEMP02.DBF';

ALTER TABLESPACE TEMP SHRINK TEMPFILE 'E:\app\administrator\oradata\ORCL\DATAFIL
E\TEMP02.DBF'
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
  • 1. Re: ORA-03214: File Size specified is smaller than minimum required
    SatishKandi Guru
    Currently Being Moderated
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_3002.htm#i2093894

    <quote>
    SHRINK SPACE Clause

    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.
    <quote>

    If this holds true then you are looking at a bug.
  • 2. Re: ORA-03214: File Size specified is smaller than minimum required
    650105 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points