Re: DK15789: How do a reclaim space after deleting table data in a datafile

I'm sorry if I was confusing, let me clarify. I have three data files,
each one is a gig. Your are correct of course they do not grow or shrink
(autoextend is off).

     My problem is exactly as you describe in your second paragraph. I am deleting and truncating the tables, but the tablespace usage is not being altered (it is not decreasing as I would hope). I do not want the datafile space to continue to be allocated to any particular table. I guess my question would then be, how do a remove the extents that are committed to my tables? Although, I'm not sure that is the correct question either. All I really want to do is have the tablespace show how much space is being used by the tables, not how much has been allocated for the tables.

     Just to clarify the rest... Yes I meant I dropped the tables, not deleted. On the import the options I specified were,

1. list just table data <no>
2. import grants <yes>
3. ignore errors <yes>
4. Import table data <yes> 5 Import entire database <yes>

And yes I realize "that Oracle 7.3 is now exhibiting in the Museum of Ancient Computer History."

I hope this helps you help me.
Thanks again for your help.

Ed Stevens <nospam_at_noway.nohow> wrote in message
>
> You appear to be comparing apples and oranges. You delete the
> contents of the tables, then "view how much space is being
> >used by the datafiles". Data files do not grow and shrink with tables. They are pre-allocated to a given size.
>
> Now, if when you say 'datafile' you really mean 'tablespace' . . . I
> belive that deleting rows has no effect on the tablespace usage
> figure. If I'm thinking correctly, deleting rows frees up blocks in
> the extents already allocated for that table but the extent reamains
> commited to the table. Thus, the tablespace usage remains the same.
>
> Next you said you deleted the tables (I assume you meant "drop") and
> the tablespace usage dropped to zero. That squares with my previous
> comment. When the table is dropped -- not just deleting rows from the
> table -- its extents would be released.
>
> Next, you said you "import the data back in (just the empty tables) it
> jumps back up to two gigs." What are the options did you specify on
> the import?
>
> And you realize, of course, that Oracle 7.3 is now exhibiting in the
> Museum of Ancient Computer History.
Received on Thu Mar 20 2003 - 20:28:27 CST