Proracle’s Weblog

December 29, 2007

shrink datafile

Filed under: useful — proracle @ 2:52 pm
Tags:
set verify off pages 50000 lines 300
column file_name format a50 word_wrapped
col value form a5
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on tablespace_name on report
compute sum of savings on tablespace_name reportcolumn value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select tablespace_name, file_name, autoextensible, maxbytes/(1024*1024) as max,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
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(+)
order by 1,2,3
/
Advertisements

Blog at WordPress.com.