RE: ((RE)) Calculating LIOs
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Aug 2004 14:29:24 -0400
Also, here's a script that I call show_hwm.sql, which should relieve =
some of the tedium of working w/ DBMS_SPACE.
set serveroutput on size 200000
set verify off;
declare
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
l_file_id number;
l_used_block_id number;
l_used_block number;
cursor get_table
is
select table_name from user_tables where table_name =3D '&table_name';
get_table_rec get_table%ROWTYPE;
cursor get_index(h_table_name in char)
is
select index_name from user_indexes where table_name =3D h_table_name;
begin
for get_table_rec in get_table
loop
dbms_space.unused_space(user,get_table_rec.table_name,'TABLE' ,
total_blocks , total_bytes,unused_blocks,unused_bytes,l_file_id,
l_used_block_id , l_used_block );
dbms_output.put_line('=3D=3D=3D=3D=3D=3D=3D=3D=3D'||get_table_rec.table_n=
ame||'=3D=3D=3D=3D=3D');
dbms_output.put_line('Total blocks =
=3D=3D=3D=3D=3D=3D=3D=3D=3D>'||to_char(total_blocks));
dbms_output.put_line('Total bytes =
=3D=3D=3D=3D=3D=3D=3D=3D=3D>'||to_char(total_bytes));
dbms_output.put_line('Total unused blocks =
=3D=3D=3D>'||to_char(unused_blocks));
dbms_output.put_line('Total unused bytes =
=3D=3D=3D=3D>'||to_char(unused_bytes));
dbms_output.put_line('Blocks below HWM =
=3D=3D=3D=3D=3D=3D>'||to_char(total_blocks-unused_blo
cks));
for get_index_rec in get_index(get_table_rec.table_name)
loop
dbms_space.unused_space(USER,get_index_rec.index_name,'INDEX' ,
total_blocks , =
total_bytes,unused_blocks,unused_bytes,l_file_id,l_used_block_id
, l_used_block );
=
dbms_output.put_line('=3D=3D=3D=3D=3D=3D=3D=3D=3D'||get_index_rec.index_n=
ame||'=3D=3D=3D=3D=3D');
dbms_output.put_line('Total blocks =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>'||to_char(total_blocks));
dbms_output.put_line('Total bytes =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>'||to_char(total_bytes));
dbms_output.put_line('Total unused bytes =
=3D=3D=3D=3D>'||to_char(unused_bytes));
dbms_output.put_line('Total unused blocks =
=3D=3D=3D>'||to_char(unused_blocks));
dbms_output.put_line('Blocks below HWM =
=3D=3D=3D=3D=3D=3D>'||to_char(total_blocks-unu
sed_blocks));
end loop;
end loop;
end;
/
See if that doesn't make life a little easier.
-Mark
PS I believe credit for the script goes to Riyaj Shamsudeen....though =
it's been quite a while since he's worked here, I'm pretty sure he's on =
this list. Thanks Riyaj! ;-)
-----Original Message-----
From: Bobak, Mark=20
Sent: Friday, August 13, 2004 1:59 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: ((RE)) Calculating LIOs
Nope, cause it's not in the data dictionary....;-)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
ryan.gaffuri@xxxxxxxxxxx
Sent: Friday, August 13, 2004 1:56 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ((RE)) Calculating LIOs
anyway to get the HWM of indexes without dbms_space? Its a pain to pass =
all those parameters. I'm familiar with the package. anyway to grab it =
out of the data dictionary?=20
-------------- Original message --------------=20
> Try using DBMS_SPACE.UNUSED_SPACE. =3D20=20
>=20
> It should be pretty self-explanatory:=20
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
