Sunday, August 16, 2015

Oracle : Script to evaluate space used in a tablespace

set pause off
set feed off
set verify off
set pagesize 55
set linesize 255
clear screen
col tn   format a10 heading 'Name' trunc
col fn   format a50 heading 'Located in file_ID + File Name'
col bts  format a10 heading 'Size'
col used format a10 heading 'Used'

col ex   format        9999 heading 'NrExt'
col rs   format 999,999,999 heading 'RBSsize'
col init format     999,999 heading 'Init'
col next format     999,999 heading 'Next'
col mi   format         999 heading 'Min'
col ma   format  9999999999 heading 'Max'
col pct  format         990 heading '%Inc'
col st   format          a4 heading 'Stat'
col sn   format         a15 heading 'Segm Name'
col ts   format         a15 heading 'In Table Space'

create or replace view free_view
as
select file_id, sum(bytes) free_bytes
from sys.dba_free_space
group by file_id;

clear screen

prompt Tablespace Datafiles
select d.tablespace_name tn,
       f.file_id||' '||file_name fn,
       to_char(f.bytes/1024,'999,999')||'K' bts,
       to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id(+)
order by d.tablespace_name;

prompt
prompt                  Tablespace definitions

define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
       d.initial_extent init,
       d.next_extent next,
       d.pct_increase pct,
       d.min_extents mi, max_extents ma,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
       rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on

No comments:

Post a Comment