Blocks in DBA_TABLES and DBA_SEGMENTS (2)

From: Jeff Y. Y. <yuanjeff_at_yahoo.com>
Date: 3 May 2002 14:01:18 -0700
Message-ID: <a2bc9497.0205031301.7d54ab40@posting.google.com>

Thank you for your help. I wrote below script to get the Blocks in DBA_TABLES and DBA_SEGMENTS, and to see free blocks in each segment. Surprisingly, the difference is big and a lot of segments are full.

1. How can I know if extent will be allocated to those segments automatically?
2. Should I manually allocate extents to the "full" segments to improve the performance (using ALTER TABLE ...)?
3. For the segment with big number of extents (such as 6186), is there a way to reduce the number of extents without deleting the data? (because Oracle doesn't recommend large number of extents for a segment)
4. When will the database stop to allocate extent to a segment (until reaching MAXEXTENTS?)?
5. Where is the 251 blocks (dba_segments.blocks - dba_tables.blocks = 260 - 9) in the last segment?

I am using 8.1.7.0.0 on NT 2000. I like to know if anything wrong in my database.

Thank you very much again.

Jeff

_____________________________________________________________-

> select table_name, a.empty_blocks "empty",
  2 1-(empty_blocks/(empty_blocks+a.blocks)) "% Block Used",   3 a.blocks "t_block", b.blocks "s_block", b.extents,   4 b.bytes/1024 "KBytes"
  5 from dba_tables a, dba_segments b
  6 where a.owner = upper('&owner')
  7 and 1-(empty_blocks/(empty_blocks+a.blocks)) > .95   8 and a.owner = b.owner
  9 and a.table_name = b.segment_name   10 order by table_name;
Enter value for owner: prod

TABLE_NAME empty % Block Used t_block s_block EXTENTS KBytes ---------- ----- ------------ -------- -------- ------- --------

T42UI800       0            1       19       30       3      240
T4301          5   .996744792     1531     2087     211   16,696
T4311          0            1     8811    11703    1183   93,624
T43121         5   .999603363    12601    16866    1704  134,928
T43199         5   .999891658    46145    61200    6186  489,600
T4801          0            1        9      260      26    2,080

Received on Fri May 03 2002 - 16:01:18 CDT