Resizing rollback segment datafile?

Hi -

I'm trying to reduce the size of the rollback segment tablespace datafile in a test db from 1GB to 500M (using 'alter database datafile 'filename' resize 500M'), but I get the 'file contains xxx blocks of data beyond the requested RESIZE value' error. This is confusing since the total space used by my online rollback segments is less than 10MB. Any ideas?

I'm running 8.0.6 on HPUX 11.0.

Thanks -

Tim

Because rollback segments can extend.

Shutdown and restart, then and can shrink.

Hi -

I should have mentioned that I've already tried the following:

- alter system enable restricted session (to make sure I'm the only one on)
- commit (to ensure all transactions are commited, and rollback can be released)
- alter system switch logfile (to ensure all buffers are flushed)
- shutdown and restart (since nothing else worked ;-)

The only action that had any affect was dropping and recreating the rollback segments with smaller initial and next extents (256K and 256K), and fewer min extents (4). I still get the same error, but fewer blocks are reported. With 3MB used in these segments (verified by querying dba_segments) and less than 1MB used by the SYSTEM rb segemnt, it seems that a 500MB datafile would be more than enough.

Any other ideas?

Thanks -

Tim

Since shrinkable extents can be anywhere in the datafile, the best way is to drop and recreate rollback segments.

1.) You are mentioning SYSTEM rollback segment when talking about the space used in your rollback tablespace. You must understand that SYSTEM rbs is located in SYSTEM tablespace, not in your rollback TS.

2.) Each RB segment is comprised of at least two extents and those extents need not to be adjastent to each other - they can be scatered all ower the tablespace. For example, the first extent of a particular RB segment can be located at the begining of a datafile, while the second extend can be at the very end of the file. And maybe the third extent can ocupy blocks somewhere in the middle of the file....And between those extants there might be tons of free space, but you won't be able to shrink your datafile even by a single block, because shrinking of the datafile allways works from the end of the datafile towards its beginning.

With the following query you can get the picture of how extents of your RB segments are layed out over the tablespace:

SELECT segment_name, extent_id, file_id,
block_id as block_start,
block_id+blocks-1 as block_end
FROM dba_extents
WHERE tablespace_name = 'RBS' /* supposing your TS name is RBS */
ORDER BY file_id, block_id;

By the corresponding block_start and block_end you will be able to determine which segment is above your intended 500MB limit. Simply drop all the segments abowe that limit (you will not be able to do so if there is any active transaction using that RBS), resize the datafile and recreate all the RB segments you have dropped in previous step.

HTH,

Jurij -

Your information was exactly what I needed.

Thanks to everyone for their responses.

Tim