Re: segment fragmentation
- From: "Ian Cary" <Ian.Cary@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx, thump@xxxxxxxxxxxxxxxx
- Date: Thu, 9 Sep 2004 12:49:54 +0100
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
