Re: Freeing Tablespace for active table

On 7 Apr 2004 13:49:07 -0700, Ritu <rc0972_at_rediffmail.com> wrote:

> Hello Folks,
>
> I chanced upon this newsgroup in my search for a solution to a problem
> I am facing. I hope someone can give me a pointer.
>
> I have a table in an Oracle 8i database that performs the function of
> a log. It has a hit of approximately 2 records a second. Such a large
> volume of data eats into our tablespace and we have decided to
> summarize the data in this table. So we have a function that
> summarizes the data, inserts it into a new summary table and then
> frees space on this table.
>
> The problem we face are as follows
>
> 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.
>
> 2. While we are truncating the table, what happens to the attempts to
> access this table?

Truncate acquires an exclusive table lock, so access to the table for regular DML will hang. But the entire point of a truncate is that it is a swift data dictionary operation, and the table lock should be taken for a very brief period of time, rather than long minutes or even hours associated with individual row deletes.

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

Not as such. But I don't really see the issue. Fair enough that your table has grown enormous and you want that space back. Fair enough too that the only thing that will force the table to relinquish the extents it has already acquired is a drop or a truncate. But in future, if you create your summary on a fairly regular basis, and then delete specific rows, then although that doesn't free up any extents, it certainly frees up space within the table, such that the next set of insertions will be able to re-use that vacated space, and thus the original table will not need to acquire any new extents. If you are using dictionary managed tablespace, you could even guarantee that your original table will not acquire additional extents by setting MAXEXTENTS to some suitable value when you re-create it. In other words, you are faced with a bad locking/hanging situation only the first time you do the re-organisation, but if you do it regularly thereafter, the problem should be containable.

The other option you have is perhaps to investigate partitioning, and use the exchange partition command to swap a partition out to become a populated standalone table and an empty table in as a new partition. Your users can then be busy filling up the new empty partition whilst you are busy summarising and truncating the new standalone table. Partitioning costs money, though, so if you don't already have it as an option (Enterprise Edition required, and then the partitioning option on top of that), it probably isn't a flyer.

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================