How to free up Table's extent

[feng11]

Can a table segment free up its extent after housekeeping?

I originally has a table using 1000 extents. after i delete more than 50% of the data. the table still holds on to the 1000 extents. is there anyway that i can free up the extents so that the extents be returned to the tablesapce?

[pando]

you cannot unless you truncate the table

or your High Water Mark is low, if that was the case you use alter table deallocate XXX

[feng11]

deallocate extents only frees up the block above th HWM. but the extent is not empty, so that is why it cannot be considered as a free extent and return space to the tablespace. Truncate for table is one way, but lost all data. But of course can export out and then import. Or can move table to another tablespace and then mpve it back.

But is there some command that can compact the extents and free up empty ones? I have a table at 1GB, and i delete more than 50%, but the TBS usage remains the same.

[aarroyob]

Yep, you could do:

alter table table_name move tablespace tablespace_name
storage (initial n next n pctincrease 0);

Rebuild you indexes

Cheers

Angel

[pando]

well you dont have to move from one tablespace to another, you can move it in the same tablespace

[aarroyob]

Yes I know but the sintax is exactly the same if you move to the same tablespace or another

Salu2

[feng11]

Let's say if i move the table to teh same tablespace, must my tablespace have that extra space first so that the table can be recreated and then the old one drop away?

I dont think it is posssible to perfomr the move table to same tablepsace if there is insufficient space

[aarroyob]

Originally posted by feng11
Let's say if i move the table to teh same tablespace, must my tablespace have that extra space first so that the table can be recreated and then the old one drop away?

I dont think it is posssible to perfomr the move table to same tablepsace if there is insufficient space

Of course, is the same when you rebuild indexes.

Cheers

Angel

[jomon_s]

hi...


Off course you can deallocate the unused extents.But I dont think that can give you optimal performance.So what I suggest you is

Take a export of the table.
truncate the table
import the table back using the option IGNORE=Y


I think this solves your problem.


Joe

[feng11]

export of table can only be done during maintainence or restricted mode, so that the data exported is consistent. But i still think move tablepsace is a better option. of course need to rebuild indexes as well.

thanks aarroyob and pando.