shrinking datafile

TIP #21: Shrink datafiles

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--- to shrink datafiles:

select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0;

$ cat resize_advisor.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize-1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
--(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;

-- resize advisor for one tablespace
cat resize_advisor_tbs.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize - 1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files where tablespace_name='&tbs_name') a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;



--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;