- Oracle DB 10g Enterprise Edition Release 10.1.0.2.0
- 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,
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;
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). :(