2 Replies Latest reply: May 15, 2009 1:02 AM by 650105 RSS

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

    650105
      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
          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
            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