Thursday, May 21, 2009

Shrinking datafiles.

# PERMANENT + UNDO usage
select dt.contents, sum(bytes)
from dba_data_files ddf, dba_tablespaces dt
where ddf.tablespace_name = dt.tablespace_name
group by dt.contents;

# TEMP tablespace
select 'TEMP',sum(bytes) from dba_temp_files;

# PERM FREE
select 'PERM FREE',sum(bytes)
from dba_free_space dfs, dba_tablespaces dt
where dfs.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT';

# PERM USED
select 'PERM USED',sum(bytes)
from dba_segments ds, dba_tablespaces dt
where ds.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT';

# READ ONLY
select sum(bytes)
from dba_data_files ddf, dba_tablespaces dt
where ddf.tablespace_name = dt.tablespace_name
and dt.status = 'READ ONLY';

# shrink files with no fragmentation
select 'alter database datafile '''||file_name||''' resize '||sum((ddf.bytes-dfs.bytes)/1024)||'K;'
from dba_data_files ddf,
dba_free_space dfs
where ddf.file_id = dfs.file_id
and ddf.tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'PERMANENT')
group by ddf.file_name
having count(*) = 1
order by sum((ddf.bytes-dfs.bytes)/1024)+64 desc;

# shrink fragmented files down to the last block
create table max_block as select file_id, max(block_id+blocks-1) block from dba_extents group by file_id;

select 'alter database datafile '''||ddf.file_name||''' resize '||mb.block*dt.block_size/1024||'K;'
from
dba_data_files ddf,
dba_tablespaces dt,
max_block mb
where ddf.file_id = mb.file_id
and ddf.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT'
order by mb.block*dt.block_size/1024 desc;