4 Replies Latest reply: Oct 1, 2004 11:10 AM by 428551 RSS

    Resize tablespace size.

      My environment:
      - Oracle DB 10g Enterprise Edition Release
      - O/S: XPPro SP1

      Previously I have a huge table located in tablespace SYSTEM01, but then I decided to move that table into another tablespace (T1). The size of SYSTEM01 didn't change after that.
      I know there's an option - Segment Space Management, can be Automatic/Manual. I guess by default SYSTEM01 is manual.
      Anyone know how to make it AUTOMATIC? Or anyone know how shrink SYSTEM01 size and TEMP?

      I know my question might be discussed in this forum or ASKTom forum before, but I couldn't find the right answer.
      Any help will be much appreciated.

      Thanks and Regards,
        • 1. Re: Resize tablespace size.
          Hi Usman,

          The size of the tablespace wont change just because you have moved out a table. The free space in tablespace would have increased. Making a tablespace AUTOMATIC doesnt mean it will shrink automatically.

          You can shrink a datafile with the command
          alter database datafile '<full_file_name>' resize <size>M;

          Note that you can only shrink upto the high water mark.

          You can find out the space management of tablespace by the command
          select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

          • 2. Re: Resize tablespace size.
            Thanks for your reply.
            So, is there any other "easy" way to shrink the tablespace size?

            Thanks and Regards,
            Usman Tjiudri
            • 3. Re: Resize tablespace size.
              He just gave it to you:

              alter database datafile '<full_file_name>' resize <size>M;
              • 4. Re: Resize tablespace size.
                Thanks for the reply.
                I checked the % of use in Entriprise Manager, it said 50M (out of 5 GB). I reduced it to 100M using that command but it failed. The error message is something to do with the size (Sorry for the exact error message). :(