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