It is possible to resize a tablespace datafile is there is some free space at the end of the datafile. This is the quick and easy method. If you have data at the end of the datafile, you will need to move the objects so that segment will be recreated.<\/p>\n\n\n\n
View tablespace size with free space<\/p>\n\n\n\n
select df.tablespace_name \"Tablespace\", df.totalspace \"Total MB\", totalusedspace \"Used MB\", (df.totalspace - tu.totalusedspace) \"Free MB\", round(100 * ( (df.totalspace - tu.totalusedspace)\/ df.totalspace)) \"Pct. Free\" from (select tablespace_name, round(sum(bytes) \/ 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)\/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by 2 desc ;<\/code><\/pre>\n\n\n\nGenerate command to reduce tablespace (specify block size and tablespace name)<\/p>\n\n\n\n
select 'alter database datafile '''||file_name||''' resize ' ||\r\nceil( (nvl(hwm,1)*&&blksize)\/1024\/1024 ) || 'm;' cmd\r\nfrom dba_data_files a,\r\n( select file_id, max(block_id+blocks-1) hwm\r\nfrom dba_extents where tablespace_name= '&&tbs_name' \r\ngroup by file_id ) b\r\nwhere a.file_id = b.file_id(+) and a.tablespace_name= '&&tbs_name'\r\nand ceil( blocks*&&blksize\/1024\/1024) -\r\nceil( (nvl(hwm,1)*&&blksize)\/1024\/1024 ) > 0;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"It is possible to resize a tablespace datafile is there is some free space at the end of the datafile. This is the quick and easy method. If you have data at the end of the datafile, you will need to move the objects so that segment will be recreated. View tablespace size with free … <\/p>\n