Oracle not using freed space 2

[asb555]

Hello,
I have an oracle database with 500M of space. Recently, I saw that I had only about 10M of space left on the tablespace. At that point, I decided to delete some older records which I no longer needed in order to free up some space. I deleted about 300,000 records. But I was suprised to see that the tablespace still only has 10M of space left. My question is, why is Oracle not recognizing the freed up space?

Thanks In Advance,
Amir

 

[stefanhei]

Hello.
To free the space you have to perform:
alter table yourTable dealocate unused
You can only free space above the highwater-mark. In pre 8i-DBs the usual way was export/import of the table.

Stefan

 

[carp]

Stefan is right. By deleting records, you are freeing up space within the table itself and this space will probably be reused (depending on how you have PCTUSED set). You won't see more free space in the tablespace until you actually change the segments themselves.
Likewise, if you define a table with a 10M initial extent, you will find that the table takes up 10M even though you haven't inserted any rows into it yet.

 

[Leon1977]

p.s. notice that when you create a tablespace and let's say its initial size is 30MB, even its empty its datafile is 30MB of size