Rollback segment Porblem
I am facing Problem with Rollback segment tablespace.After shrinking all Rollback segment also,tablespaces was not get reduced.(used space same as previously).I tried for normal shutdown also,still the used space for Rollback segment tablespace was same.can anyone tell how to reduced the used space.
alter database datafile 'physicalfilename' resize xxx;
You can truncate the tablespace up to the point that the data blocks
have "free space". You cannot truncate space if there is a data segment (rollback segment) in the block... Look at the tablespace
and find the last data block with a data segment. You can truncate
the remainder of the file.
How to find the last block?
select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
from dba_data_files a,
( select file_id, max(block_id+blocks) hwm
group by file_id ) b
where a.file_id = b.file_id
Will tell u about the possible shrinkage in terms of blocks
Click Here to Expand Forum to Full Width