Re: Problem with table size

Ian Butt wrote:
>
> Dear Oracle Users,
>
> PROBLEM
> ~~~~~~~
> I have a problem with a table that continues to increase in size. Rows
> are deleted and new rows are inserted keeping the total number of rows
> about the same but the space occupied by the table continues to
> increase. I think the problem relates to my use of a long datatype in
> the table. If you think you can help please read the details below.
>
> DETAILS
> ~~~~~~~
> I am using Oracle7 Server Release 7.3.2.3.0 - Production Release on an
> IBM RS/6000 running AIX 4.1.
>
> A number of tables have this problem and all include a long datatype.
> An example of the structure of one of these table follows:
>
> SQL> desc text_tbl;
> Name Null? Type
> ------------------------------- -------- ----
> MESSAGE_ID NOT NULL CHAR(20)
> MESSAGE_TYPE NOT NULL NUMBER(4)
> ORIGINATOR CHAR(40)
> STORED_DT NOT NULL DATE
> MESSAGE_TEXT NOT NULL LONG
> VERSION NOT NULL NUMBER(3)
>
> The table is modified by a C application using embedded SQL.
>
> The number of rows per MESSAGE_ID is controlled by the VERSION column,
> the maximum value allowed for VERSION is held in a separate table. When
> VERSION reaches the maximum number it returns to 1, deletes the current
> row for MESSAGE_ID with VERSION equal to 1 and the inserts the new row
> and the MESSAGE_TEXT associated with it. VERSION numbers continue to be
> incremented from that value.
>
> The application updating this table does not seem to have any problems
> nor do the applications that read the message text fields.
>
> An analysis of the table shows that is has:
>
> Rows: 29014
> Minimum MESSAGE_TEXT: 1 byte
> Maximum MESSAGE_TEXT: 14660 bytes
> Total MESSAGE_TEXT: 2885853 bytes
>
> For this application the long is expected to handle only up to
> 30k bytes. The table is updated frequently (about 27500 times in 24
> hours) and because the maximum number of versions has been reached for
> most MESSAGE_ID's involves the delete/insert mentioned above.
>
> Despite the fact that the actual text contained in the table is 2885853
> bytes the DBA reports the following statistics for the table:
>
> Owner Type Name
> Bytes Blocks Extents Max Extents Initial Extent Next Extent Pct
> -------------------- ---------- ------------------------------
> ----------- -------- -------- ------------ -------------- ------------
> ----
> PROD TABLE TEXT_TBL
> 468828160 114460 216 249 26357760 20807680
> 5
>
> Number of rows in table "TEXT_TBL" is: 29014
>
> The table actually occupies 468828160 bytes and continues to grow!
> Eventually it reaches the maximum extent allocated and something has to
> be done. At the moment the DBA is just increasing the extents available.
>
> QUESTIONS
> ~~~~~~~~~
>
> Is this normal for an Oracle table containing a long datatype?
>
> If this in not normal what is causing it?
>
> How can I fix it or avoid it?
>
> Thank you for any responses.
>
> Ian Butt
> Bureau of Meteorology, Australia
> I.Butt_at_BoM.GOV.AU

You don't indicate the block size or pctfree and pctused params on the table. If rows are deleted from blocks but the space still occupied does not fall below pctused the block will not be made available for insertion of new rows. From the maxextents parm of 249 I assume block size is 4096 or 4k. try to analyze the table and look at dba_tables for the table to see what the chain count is and the avg free space. You may have to export the table, truncate the table then import back in to use the space efficiently. It probably wouldn't be a bad idea to have a tablespace just for this table to make it easier to monitor and update periodically.

Hope this helps.

Dick Allie
dallie_at_ionet.net Received on Thu May 22 1997 - 00:00:00 CDT