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?

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

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

Regards,
Ritu

On 7 Apr 2004 13:49:07 -0700, rc0972@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

> 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.

TRUNCATE TABLE my_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.


HTH,
Brian




--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

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"

On 7 Apr 2004 13:49:07 -0700, Ritu <rc0972@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-
===========================================