Display free and used space for all or some tablespaces
Like tablespace : sys
Original Free Largest free Number Initial Allocation
Tablespace Management Status Contents (Mb) (Mb) % Free (Mb) Free Extent Type
---------- ---------- ------ -------- -------- ---- ------ ------------ -------- -------------- ----------
SYSAUX LOCAL ONLINE PERMANENT 4,330.00 240.56 5.56* 200.00 48 65,536.00 SYSTEM
SYSTEM LOCAL ONLINE PERMANENT 1,470.00 6.88 .47* 6.00 2 65,536.00 SYSTEM
Totals: 5,800.00 247.44 4.27
Totals (Permanent only): 5,800.00 247.44 4.27
PL/SQL procedure successfully completed.
prompt
spool f_sp.rpt
accept x_tspace prompt "Like tablespace : "
declare
ls_alert varchar(1);
li_counter integer;
ls_table_space varchar2( 30 );
ln_original number;
ln_free number;
ln_perc number;
ln_tot_ori number := 0;
ln_tot_free number := 0;
ln_num_ori number := 0;
ln_tot_perm_ori number := 0;
ln_tot_free_perm number := 0;
ln_num_perm number := 0;
cursor curs_tb_name is
select * from dba_tablespaces
where tablespace_name like upper( '%' || '&x_tspace' || '%' )
order by tablespace_name;
ls_status varchar2( 9 );
ls_contents varchar2( 9 );
cursor csr_max_fsp( xt_tablespace IN dba_tablespaces.tablespace_name%type ) is
select max( bytes )
from dba_free_space
where tablespace_name = xt_tablespace;
ln_biggest_free number;
ln_no_of_segs number;
begin
dbms_output.put_line( rpad( ' ', 25 ) ||
rpad( ' ', 12 ) ||
' ' ||
' ' ||
' Original' ||
' Free' ||
' ' ||
' Largest free' ||
' Number' ||
' Initial' ||
' Allocation' );
dbms_output.put_line( rpad( 'Tablespace', 25 ) ||
rpad( 'Management', 12 ) ||
'Status ' ||
'Contents ' ||
' (Mb)' ||
' (Mb)' ||
' % Free' ||
' (Mb)' ||
' Free' ||
' Extent' ||
' Type' );
dbms_output.put_line( rpad( '----------', 25 ) ||
rpad( '----------', 12 ) ||
'------ ' ||
'-------- ' ||
' --------' ||
' ----' ||
' ------' ||
' ------------' ||
' --------' ||
' --------------' ||
' ----------' ||
chr( 10 ) );
for li_counter in curs_tb_name loop
select nvl( sum( bytes ), 0 ) into ln_original from dba_data_files where tablespace_name = li_counter.tablespace_name;
select nvl( sum( bytes ), 0 ), nvl( count(*), 0 ) into ln_free, ln_no_of_segs from dba_free_space where tablespace_name = li_counter.tablespace_name;
select status, contents into ls_status, ls_contents from dba_tablespaces where tablespace_name = li_counter.tablespace_name;
ln_perc := 0;
if nvl( ln_original, 0 ) > 0
and nvl( ln_free, 0 ) > 0 then
ln_perc := ( ln_free / ln_original ) * 100;
end if;
ls_alert := ' ';
if ln_perc < 25 then
ls_alert := '*';
end if;
open csr_max_fsp( li_counter.tablespace_name );
fetch csr_max_fsp into ln_biggest_free;
close csr_max_fsp;
dbms_output.put_line( rpad( li_counter.tablespace_name, 25 ) ||
rpad( li_counter.extent_management, 12 ) ||
rpad( ls_status, 9 ) ||
rpad( ls_contents, 9 ) ||
to_char( nvl( ln_original, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( nvl( ln_free, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( ln_perc, '99,999.99' ) ||
ls_alert ||
to_char( nvl( ln_biggest_free, 0 )/(1024*1024), '9999,999,999.99' ) ||
to_char( nvl( ln_no_of_segs, 0 ), '99,999,999' ) ||
to_char( li_counter.initial_extent, '99999,999,999.99' ) || ' ' ||
li_counter.allocation_type ||
chr( 10 ) );
ln_tot_ori := ln_tot_ori + ln_original;
ln_tot_free := ln_tot_free + ln_free;
ln_num_ori := ln_num_ori + 1;
if ls_contents = 'PERMANENT' then
ln_num_perm := ln_num_perm + 1;
ln_tot_perm_ori := ln_tot_perm_ori + ln_original;
ln_tot_free_perm := ln_tot_free_perm + ln_free;
end if;
end loop;
-- Totals
if ln_num_ori > 1 and ln_tot_ori > 0 then
dbms_output.put_line( rpad( 'Totals:', 37 ) ||
rpad( ' ', 9 ) ||
rpad( ' ', 9 ) ||
to_char( nvl( ln_tot_ori, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( nvl( ln_tot_free, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( ( ln_tot_free/ln_tot_ori ) * 100, '99,999.99' ) ||
' ' ||
chr( 10 ) );
end if;
if ln_num_perm > 1 and ln_tot_perm_ori > 0 then
dbms_output.put_line( rpad( 'Totals (Permanent only):', 37 ) ||
rpad( ' ', 9 ) ||
rpad( ' ', 9 ) ||
to_char( nvl( ln_tot_perm_ori, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( nvl( ln_tot_free_perm, 0 )/(1024*1024), '9,999,999.99' ) ||
to_char( ( ln_tot_free_perm/ln_tot_perm_ori ) * 100, '99,999.99' ) ||
' ' ||
chr( 10 ) );
end if;
end;
/
spool off