spool seg1
set lines 141
set serveroutput on
execute dbms_output.enable(1000000);
declare
tmp varchar2(1);
v_tbl_name varchar2(100);
v_tot_blocks number;
v_tot_bytes number;
v_unused_blocks number;
v_unused_bytes number;
tot_mb number;
unused_mb number;
v_fileid number;
v_blockid number;
v_block number;
cursor c1 is
select OWNER,
segment_name,
segment_type,
bytes/(1024 *1024) USED_MB,
(extents * initial_extent)/(1024 *1024) Allocated_MB,
Tablespace_name,
extents
from dba_segments
where owner in ( ‘CS’, ‘SCSEDB’, ‘WEBSITEDB’, ‘MCIF’, ‘CIF’ )
and segment_type in (‘TABLE’, ‘INDEX’)
order by owner, tablespace_name, segment_name;
begin
dbms_output.put_line(rpad(‘OWNER’, 15, ‘ ‘) || ‘ ‘ || rpad(‘SEGMENT NAME’, 30, ‘ ‘) || ‘ ‘ ||
rpad(‘TYPE’, 10, ‘ ‘) || ‘ ‘ || rpad(‘ALLOC MB’, 8, ‘ ‘) || ‘ ‘ ||
rpad(‘UNUSD MB’, 8, ‘ ‘) || ‘ ‘ || rpad(‘TS NAME’, 27, ‘ ‘) || ‘ ‘ ||
rpad(‘EXTENTS’, 5, ‘ ‘) || ‘ ‘ || rpad(‘TABLENM’, 30, ‘ ‘) );
dbms_output.put_line(rpad(‘=’, 15, ‘=’) || ‘ ‘ || rpad(‘=’, 30, ‘=’) || ‘ ‘ ||
rpad(‘=’, 10, ‘=’) || ‘ ‘ || rpad(‘=’, 8, ‘=’) || ‘ ‘ ||
rpad(‘=’, 8, ‘=’) || ‘ ‘ || rpad(‘=’, 27, ‘=’) || ‘ ‘ ||
rpad(‘=’, 5, ‘=’) || ‘ ‘ || rpad(‘=’, 30, ‘=’) );
for cur in c1 loop
if cur.segment_type = ‘INDEX’ then
begin
select table_name into v_tbl_name from dba_indexes
where owner = cur.owner
and index_name = cur.segment_name;
exception
when no_data_found then
v_tbl_name := ‘???TBL NOT FOUND???’;
end;
else
v_tbl_name := ‘N/A’;
end if;
dbms_space.unused_space(cur.owner, cur.segment_name, cur.segment_type,
v_tot_blocks, v_tot_bytes, v_unused_blocks, v_unused_bytes,
v_fileid, v_blockid, v_block, null);
tot_mb := v_tot_bytes/(1024 * 1024);
unused_mb := round(v_unused_bytes/(1024 * 1024), 2);
dbms_output.put_line(rpad(cur.owner, 15, ‘ ‘) || ‘ ‘ || rpad(cur.segment_name, 30, ‘ ‘) || ‘ ‘ ||
rpad(cur.segment_type, 10, ‘ ‘) || ‘ ‘ || rpad(tot_mb, 8, ‘ ‘) || ‘ ‘ ||
rpad(unused_mb, 8, ‘ ‘) || ‘ ‘ || rpad(cur.tablespace_name, 27, ‘ ‘) || ‘ ‘ ||
rpad(cur.extents, 5, ‘ ‘) || ‘ ‘ || v_tbl_name );
end loop;
end ;
/
spool off