This discussion is archived
4 Replies Latest reply: Oct 1, 2004 9:10 AM by 428551 RSS

Resize tablespace size.

428551 Newbie
Currently Being Moderated
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.
    30150 Newbie
    Currently Being Moderated
    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.
    428551 Newbie
    Currently Being Moderated
    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.
    user148120 Newbie
    Currently Being Moderated
    He just gave it to you:

    alter database datafile '<full_file_name>' resize <size>M;
  • 4. Re: Resize tablespace size.
    428551 Newbie
    Currently Being Moderated
    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). :(