Printable View
Dear Guys,
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.
babu
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.
HTH
Gregg
How to find the last block?
dba_extents
Try This
---------
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
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
/
Will tell u about the possible shrinkage in terms of blocks