Re: Freeing Tablespace for active table

On 7 Apr 2004 13:49:07 -0700, rc0972_at_rediffmail.com (Ritu) 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.
>

Both assertions are not necessarily true. As soon as a block is less than PCTUSED occupied, it gets on the free list. However, you need to calculate appropiate values of PCTUSED as the inserted record needs to fit, or it won't be inserted.

You definitely don't have to drop the entire table in order to truncate. Truncate has been designed to avoid dropping! You can simply select the remainder of your table in a dummy table, truncate the original table and insert it back again. In order to reorganize the table without truncate use alter table <blah> move tablespace <current tablespace> provided there is no long in it.
If there is a long in it, use SQL*plus COPY to copy the remainder to a dummy table, truncate the original table, and use COPY insert to get the data back in.
>2. While we are truncating the table, what happens to the attempts to
>access this table?

There will be an exclusive DDL lock on the table, so other sessions will get ORA-0054
>
>Is there some way to selectively compress or free table space without
>stopping our operation?

alter table move

Or buy extra disk space of course ;-)

>
>I would be very glad if someone takes a few minutes off and helps us
>out.
>
>Regards,
>Ritu

--
Sybrand Bakker, Senior Oracle DBA