| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: freeing space after dropping a table
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
--
 |
 |