Table throwing extents when has empty blocks

[ssmith]

I have a table with excessive space. In fact it has 17669 empty blocks (db_block_size=8192). This table inserts and deletes continuously but it keeps thowing extents rather then insert into the empty blocks. At the moment this table has 48 extents and it is increasing 2 extents per month. The maximum amount of rows in this table are approx. 300. The avg_row_len is 2651, the pct_free is 20, pct_used is 70, the table desc:
SQL> desc output_table
Name Null? Type
---------------- -------- --------------
SESSIONID VARCHAR2(10)
SELECT_STRING LONG
SELECT_DATE DATE

What parameters should I change (and what to?) to remedy this problem? Thanks.

[denevge]

how are the inserts done.

If you insert using a 'direct' insert with sqlloader or with the
APPEND hint in an insert statement, you will always use new extents when you insert the data.

Hope this helps
Gert

[ssmith]

its just a simple insert, e.g.
INSERT INTO OUTPUT_TABLE ( SESSIONID,SELECT_STRING,SELECT_DATE ) VALUES ( :b1,:b2,SYSDATE )

and the delete is:
DELETE FROM OUTPUT_TABLE WHERE SESSIONID = :b1

[Zaggy]

Sounds like your freelists might be unbalanced. Are you doing large deletes? From your example, it appears that you are not.

Also, how did you determine your empty blocks?

[Edited by Zaggy on 07-17-2002 at 10:06 AM]

[ssmith]

To determine the empty blocks I analyzed the table and select empty_blocks from dba_tables. I also used the procedure dbms_space.unused_space and looked at total_blocks and unused_blocks. These are the results:
table Space statistics for am_app.output_table
Total Blocks: 14795
Total Bytes: 121200640
Unused Blocks: 14699
Unused Bytes: 120414208
Last Used File Id: 3
Last Used Block Id: 172912
Last Used Block: 96

PL/SQL procedure successfully completed.

[anandkl]

The error is due to the fact that ur initial_extent and next_extent is not sized properly and are not equi sized.Please check on this..

regards
anandkl

[ssmith]

The initial is 4m,next 1m, and pctincrease 0, extents is now at 50 and there are only 201 rows in the table. I don't understand why this would cause the problem. New inserts are throwing extents instead of inserting into the empty blocks.
Sorry, the data previosly was for the test database. This is the data for the table on production :
Total blocks : 18271
Unused blocks : 28
Free blocks : 18192

As you can see there are 18192 free blocks on the freelist. The HWM is high for this table, but I have heard rumours that tables with a LONG datatype will not insert into empty blocks below the HWM but only above, this is why it throws extents. Is this true?

[prakashs43]

Hi ,

Check , minextents and max extents , one more point to consider is u are using long datatypes in your table , may be it is occupying lot of space . also try to increase your initial extents , if outage permits. one more correction is u are having a problem in a single table and u are checking all free blocks for all the tables. just select number of free extents for that particular table .

Hope it is pointing to ur query .

[ssmith]

I am using the procedure dbms_space.unused and dbms_space.free_blocks for the specified table.
How can min_extents and max_extents settings cause this problem?

[Zaggy]

I know Oracle strongly recommends that anyone using Long should convert them to LOBs. I also noticed that you cannot use automatic segment-space management on a table with a Long.


http://otn.oracle.com/docs/products/...nts2.htm#54201

How many freelists do you have for this table?