Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Freeing Tablespace for active table

Re: Freeing Tablespace for active table

From: Brian Peasland <>
Date: Wed, 7 Apr 2004 21:21:56 GMT
Message-ID: <>

> 1. Mere deletion of records we have discovered does not free the table
> space. This only happens on Truncate. In order to use the Truncate
> command we have to drop the entire table rather than delete a select
> set of records as we need.

Actually, the TRUNCATE command does not have to drop the entire table. It will delete all records from that table, but the table will remain. As I write this, it occurs to me that this may just be semantics...but DROP means that the table will no longer exist (in Oracle-speak).

> 2. While we are truncating the table, what happens to the attempts to
> access this table?

When you issue a TRUNCATE command, a lock is temporary placed on the table. The duration of this lock can be very short.

> Is there some way to selectively compress or free table space without
> stopping our operation?

As you have seen, the TRUNCATE will have a short-lived lock, but even this short-lived lock can cause a problem with your application. And TRUNCATE is an all-or-nothing proposition. You can't selectively remove some rows and leave others. So what to do????

Since you are on Oracle 8i, then you will have to arrange some downtime for this operation. If you were using Oracle 9i, then you could do this reorg task online.

First, put your database in restricted mode so that your application will not be able to access the database. Then, the second thing to do is to move the data you want to keep to a temporary holding table. This can be done with the CREATE TABLE AS SELECT (CTAS) command:

CREATE TABLE my_table_temp AS SELECT * FROM my_table;

If you want, you can add a WHERE clause to further limit the rows you want to keep. Then, truncate the table.


Now the High Water Mark (HWM) has been reset and the table is empty. Move the records you want to keep back into the table and drop the temporary holding table:

INSERT INTO my_table SELECT * FROM my_table_temp; DROP TABLE my_table_temp;

All of your space has been reclaimed and you are ready to go on, so open the database to the application.

By now, you are probably asking yourself how you can keep from repeating this same procedure over and over again. Very good question.

You got yourself into a mess by letting this table grow with data that was no longer needed. You just needed aggregate data. Unfortunately, that data that you no longer need is very, very old. Let's assume that you just aggregated 5 years worth of data. That means that your table was holding 5 years worth of data! Instead of performing your aggregation every five years, put it on a schedule, i.e. monthly, quarterly or yearly. Once you do that, remove the old data. But don't worry about the empty space. It will be used up over the course of the next month, quarter or year! You only had to reclaim your space once because you aggregated and subsequently deleted more data than your newly scheduled aggregation would allow. If you aggregate on a monthly schedule, your table will grow to hold approximately one month's worth of data. When you delete that month's of data, the table will now hold the next month's worth of data. You had to perform the TRUNCATE to reclaim five years of space!!!

So the proper procedure would be something more like:

  1. Aggregate your old data.
  2. Remove that old data (lots of it).
  3. Reclaim all that space all of that data took up.
  4. Set up a periodic aggregation/deletion routine.
  5. Don't worry too much about the space because it will be reused in the next period.



Brian Peasland

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
 the three"
Received on Wed Apr 07 2004 - 16:21:56 CDT

Original text of this message