Re: Reusing free space in a table

A copy of this was sent to "Xavi Gabaldą" <xgabalda_at_ctv.es> (if that email address didn't require changing) On Wed, 22 Sep 1999 23:29:27 +0200, you wrote:

>Hi:
>
>I have a tablespace of 5 Gbytes. It has 10 datafiles of 500 Mbytes ( No
>autoextend ). I have 1 table of 4.5 Gbytes. This table has a lot of extends.
>I have deleted 1 Gbyte of this table but Oracle seems not to reuse this
>space.
>
>Is this 'normal' in Oracle ?
>Why ?
>Is there any command to reallocate free space of deleted rows ?
>
>Note: I want to avoid the classic way... ( I have 3.5 Gbytes in the table
>! )
>
>1) Export data
>2) Drop table
>3) Create table
>4) Import data
>

once space has been ALLOCATED and USED by a table -- it belongs to that table forever. New rows inserted into that table can make use of that but the space is not available for other objects.

space that has been ALLOCATED but never used can be released back into the free space.

You may have deleted a lot of rows but there is nothing to say that you completely emptied any one extent. You may have deleted 'every other row' in the table. Even if you delete every row in an extent (those rows whose rowid imply that they are in that extent) there still probably are other rows/row pieces in that extent from other extents (migrated and chained rows).

In Oracle8i, release 8.1, you can use "alter table T move tablespace Y" to rebuild a table (it'll take a while for a 3.5 gig table) instead of exp/imp.

>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 24 1999 - 06:56:46 CDT