Free space management [Q]

Hello All.

The question for oracle internals' guru.

We have cluster of 13 tables (don't blame me here). It has 2K key size as it was created, and in 95% cases all data with given key fit in the DB block (2K too) There are several small extents (we haven't had new datafile ready and had to decrease NEXT parameter for the cluster to have our
(OLTP) system keep working) as well as several large extents.
After all allowable space ( 1.9 GB) was filled up we begin to delete old data.

Later on we changed key size for this cluster to 960 bytes. With this key size in 75% cases data for two keys occupies one 2K DB block.
After a week of operating in this mode ~70M of data, ~50K keys we got a strange error message:

ORA-1655 - unable to extend cluster ...

which goes to alert log as

OER-1655 (not ORA-1655 !!!)

and was not fatal - after 7-9 of our 10 (at the same time) processes experienced this error it was able to find free space .

At the beginning this error happened twice a day. Then every 30 minutes. Now every 10 minutes.

We thought that changing key size back to 2K would help, but it didn't.

I think there is a problem with oracle free space allocation mechanism.
We cannot add new datafile as we must to remain within given space limits. We also cannot use export/import - we are a 7x24 system.

Any useful advice will be appreciated.

If it is helpful, 'pctfree' is 10,
 'freelists' - 1, 'freelists groups' - 1.

What is most surprising, we have another cluster with the same history
(2K then 960b key size, several small extents, 1.5 GB allocated, deleting
old data ) except there is less data for a key (it still fit in 960 in 99% cases). One big difference - freelists = 3. Another difference - everything is fine with it (so far ;-)

Please help - we don't know what to do and still wait answer from support.

--
Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena.rinet.ru

    aka <k-kivi_at_usa.net>, 2:5020/457.24_at_fidonet.org           Received on Mon Apr 20 1998 - 08:42:50 CDT