Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: segment fragmentation

Re: segment fragmentation

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Thu, 9 Sep 2004 12:49:54 +0100
Message-ID: <OFC09FF1CA.D573189F-ON80256F0A.0040A248-80256F0A.0040DFB8@ons.gov.uk>

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;

  cursor get_tab_dets is
    select owner,segment_name,segment_type,extents,partition_name     from dba_segments
    where owner like upper('&objowner')     and segment_name like upper('&objname')     and (segment_type like 'TABLE%' or

            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(' ');
  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(' ');
  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



Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications

Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics


This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email
--
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US