Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: segment fragmentation
Following on from Jared's email you might find this script useful.
Cheers,
Ian
declare
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number; last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; curowner varchar2(30) := 'xxx'; user_total_blocks number := 0; user_total_bytes number := 0; user_unused_blocks number := 0; user_unused_bytes number := 0; tot_total_blocks number := 0; tot_total_bytes number := 0; tot_unused_blocks number := 0; tot_unused_bytes number := 0;
segment_type like
decode(upper('&indexes'),'Y','INDEX%','NONE'))
order by owner,segment_name,partition_name;
begin
dbms_output.enable(1000000);
for tabrec in get_tab_dets loop
if curowner != tabrec.owner then if curowner != 'xxx' then dbms_output.put_line(' '); dbms_output.put_line(rpad('-----------',25)|| lpad('----------------',16)|| lpad('-----------',14)|| lpad('-----------',14)); dbms_output.put_line(rpad('User Total',25)|| lpad(user_total_bytes/1024,16)|| lpad((user_total_bytes-user_unused_bytes)/1024,14)|| lpad(user_unused_bytes/1024,14)); user_total_bytes := 0; user_unused_bytes := 0; end if; dbms_output.put_line(' '); dbms_output.put_line('Space Used by: '||tabrec.owner); dbms_output.put_line(' '); dbms_output.put_line(rpad('Object Name',25)|| lpad('KBytes Allocated',16)|| lpad('KBytes Used',14)|| lpad('KBytes Free',14)|| lpad('Extents',9)); dbms_output.put_line(rpad('-----------',25)|| lpad('----------------',16)|| lpad('-----------',14)|| lpad('-----------',14)|| lpad('-------',9)); curowner := tabrec.owner; end if; dbms_space.unused_space(tabrec.owner, tabrec.segment_name, tabrec.segment_type, total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_extent_file_id, last_used_extent_block_id, last_used_block, tabrec.partition_name); dbms_output.put_line(rpad(tabrec.partition_name||' '||tabrec.segment_name,25)|| lpad(total_bytes/1024,16)|| lpad((total_bytes-unused_bytes)/1024,14)|| lpad(unused_bytes/1024,14)|| lpad(tabrec.extents,9)); user_total_bytes := user_total_bytes + total_bytes; user_unused_bytes := user_unused_bytes + unused_bytes; tot_total_bytes := tot_total_bytes + total_bytes; tot_unused_bytes := tot_unused_bytes + unused_bytes;end loop;
dbms_output.put_line(rpad('-----------',25)|| lpad('----------------',16)|| lpad('-----------',14)|| lpad('-----------',14)); dbms_output.put_line(rpad('User Total',25)|| lpad(user_total_bytes/1024,16)|| lpad((user_total_bytes-user_unused_bytes)/1024,14)|| lpad(user_unused_bytes/1024,14)); dbms_output.put_line(' ');
dbms_output.put_line(rpad('-----------',25)|| lpad('----------------',16)|| lpad('-----------',14)|| lpad('-----------',14)); dbms_output.put_line(rpad('Overall Total',25)|| lpad(tot_total_bytes/1024,16)|| lpad((tot_total_bytes-tot_unused_bytes)/1024,14)|| lpad(tot_unused_bytes/1024,14));end;
For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk
-- To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/Received on Thu Sep 09 2004 - 06:44:12 CDT