Cluster growing

Dear very wise sirs, I have a big problem and would be extremely grateful if you could help me.

We have a cluster with two clustered tables, as described below. To our big surprise, despite daily purges, on site databases grow slowly but continuously.

In one of these databases, the cluster contains approximately 58000 data blocs. Of these, only 20000 contains rows. The others are fully empty.

I performed massive inserts and updates (no deletes) in a single session. These statements consumed 10000 new blocks. To my surprise, Oracle added an extent. Actually, 1765 blocks were added instead of being reused.

It would be very very helpful to me if

• Somebody could point me to why this happens and how I can ensure that all empty blocks are reused before a new extent is allocated.
• Somebody could explain or give a reference to some text which explains precisely how Oracle finds free space when it has to insert a new row in a clustered table.

Thanks very much in advance

• Raoul

Database is Personal oracle 7.3.4, block size is 8K, default PCTUSED is 40, tablespace HIST_TABLE has initial extent = next extent = 10240K, percent increase 0. Cluster and tables (names changed) are :

CREATE CLUSTER TRCluster (R NUMBER) SIZE 16384   TABLESPACE HIST_TABLE INITRANS 3 STORAGE (FREELISTS 4) PCTFREE 20
;

CREATE INDEX TRCluster ON CLUSTER TRCluster TABLESPACE HIST_INDEX
;

CREATE TABLE TR
(
  R NUMBER NOT NULL,
  ...
)
CLUSTER TRCluster(R)
;

CREATE TABLE ATR
(
  R NUMBER NOT NULL DEFAULT 1,
)
CLUSTER TRCluster(R)
;
Received on Sat Nov 06 1999 - 04:42:26 CST