Oracle Clustering Problem

[maxcrook]

Using Oracle 7.3.4 I have a table with 37 million records within it the size is 18gb and I only have 300 mb left in it.

A job is run that deletes data out of the table that is 60 days or older.

However this does not seem to be freeing up space to be reused. I am told this is due to the cluster keys.

How can you free up that space to enable the table to use that space as free space ?

Thanks

 

[karluk]

First, a disclaimer. I've never had to deal with an Oracle system that had such a large table. As a result, I may be missing a lot of issues that you are already familiar with.

Nevertheless, your description of what's happening is consistent with how I understand Oracle manages free space. Please read my post in thread186-39931 for a description of the table parameters pctfree and pctused. You could easily have a table where data blocks have space available which can't be used: inserts cause the block to become full, and removed from the free list. When your job goes through the table and deletes rows, the block is no longer full. Unfortunately, if the space used doesn't decrease below the pctused threshold, the block isn't added back to the free list. As a result the available space isn't available for new inserts.

If this is the situation, you may want to modify pctused so that the blocks go back on the free list sooner. Exporting and importing the table is normally another possibility. It will reallocate space so that the data is more evenly distributed. In your case, however, I suspect that the large size of your table may make this impossible.

You should also look at migrating to Oracle 8i. Partitioning looks ideal for your situation.

 

[maxcrook]

Karluk

Thanks for the reply - very helpful - we identified the problem - another table has been put on the same cluster as the larger table.

For info:

The table is so big as it contains calldata to be billed - as you can imagine this increases everyday as we load a lot of calls daily.

Is oracle 7.3.4 still supported ?

 

[karluk]

Active support for 7.3.4 ended at the beginning of this year. That means that Oracle will no longer backport fixes to any version 7 products. I.e. if you have a problem that requires an Oracle patch, you're out of luck unless it's already in a patch that was released last year.

Fortunately, Oracle 7.3.4 is rather stable and most issues don't require patches. Oracle is still offering extended support, for another three years, I think.

 

[maxcrook]

That Karl is more worrying in reality than on paper !!

Thanks

for your help.