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

On 19 Mar 2003 18:17:03 -0800, kwall7904_at_hotmail.com (DK15798) wrote:

>I am running Oracle 7.3 server. I have three data files roughly
>around a gig each. I have 200 tables inside the datafiles (in no
>particular order). These tables are using around two gigs. I deleted
>all contents (not the tables themselves) of the tables (while leaving
>the actual empty tables there). When I view how much space is being
>used by the datafiles, it is still two gigs. I can't figure out why.
>I have deleted all the tables so that the datafiles report zero space
>used, but when I import the data back in (just the empty tables) it
>jumps back up to two gigs. note: I just delete all the tables(with a
>script) I have not yet dropped the tablespace(and I would rather not
>if possible). Can anybody help me with this, I would greatly
>appreciate it. Thank you in advance.

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 - 07:43:55 CST