Re: Strange problem about rollback segment size

On 22 Feb 2002 21:02:48 -0800, tho_pic_at_yahoo.com (Tho Nguyen) wrote:

>Hi, all!
>There is a tablespace named RBS containing 2 rollback segments : RB1,
>RB2 in my database.
>Today i check the sizes of these rollback segments by :
>select segment_name, max(block_id+blocks-1)*8/1024
>from dba_extents
>where segment_name like '%RB%'
>group by segment_name;
>Segment_name Max(Block_
>RB1 8.4453125
>RB2 3.796875
>But the size of the only datafile of RBS tablespace in the system is
>10M (2M smaller).
>What am I wrong here?
>Thank you very much.
>PS: The block size is 8K.

Your query is completely wrong.
You likely taking only the last extent into account, and the formula to convert from blocks to bytes to M looks lousy.

You should either use
select segment_name, sum(bytes)/1048576
from dba_extents
where segment_name like '%RB%'
group by segment_name

or better still
select segment_name, bytes/1048576
from dba_segments
where segment_name like '%RB%'

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Feb 23 2002 - 01:25:33 CST