Drop some segments of table ?

[trandt]

Hi Guru,

A permanent object (table) is made up of many extents, so how can i drop some segments of the table ?

Thanks

Trandt

[ligang]

You suppose not to go these way because a table can not be
delete rows based on physical unit . :D

[trandt]

Yes, so infact i lost a datafile hence a table on this one is lost also so in the dictionary still keep infomation about it, for this reason i want to drop directly some extents of that (update table uet$ & fet$ ) ?? and i don't want to ex-import this table (90G)

Thanks.

[jmodic]

Depends on the version of the Oracle you are on.

If it's 8.i you can rebuild your table online with new storage parameters. If it's realy big (90G, as you say) then I suppose finding sufficient free space for this will be a problem, I suppose. For such a big table partitioning is recomended anyway, if not for performance then definitively for managability - tasks like this you are facing now.

If you are not on 8i yet (or even if you are) and you have a lot of free space above the highwattermark of this table (that is, a lot of unused space at *the end*) of that segment you can free this space with ALTER TABLE ... DEALOCATE UNUSED.

Otherwise you have no other choice than to rebuild with exp/imp.

HTH,

[jackli]

jmodic's Idea is perfect !