Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: freeing space after dropping a table

Re: freeing space after dropping a table

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Fri, 22 Jan 1999 09:58:29 +0800
Message-ID: <36A7DB45.4F0C@bhp.com.au>


Elizabeth Jones wrote:
>
> I have an oracle query that I want to run on a production
> system where we can't afford to have miscellaneous dropped
> tables taking up space. The query that I want to run takes
> a couple of hours if I use subqueries but if I stuff the
> data into temporary tables and join the tables it runs
> in about 5 minutes. The way that I am running the query
> is I create the tables, stuff them, truncate them and
> then drop them, and I'm not doing any commits. However,
> am I running a risk whenever I run this query that the
> space is not getting released when I truncate/drop? also,
> would it be better if I threw in a commit after the
> truncate/drop to try to ensure that these take effect?
>
> --
> Elizabeth Jones ************************** bajones_at_sky.net
> **********************************************************
> Need a brittany? http://www.geocities.com/~brittanyrescue
> **********************************************************

truncate and drop both issue implicit commits before and after they execute (as does any DDL command).

The important thing in terms of used/reused/freed space is the sizes of your objects....

For example, (in the ideal world) where every object had an extent size of (say) 1m - then evey time 1m became free (from truncate or drop) then a different object could reuse it...

Limit the number of "different" extent sizes to a smaller number and space re-use becomes more efficient than every object having a unique size...

HTH
--



Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad" Received on Thu Jan 21 1999 - 19:58:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US