| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ((RE)) Calculating LIOs
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 =
dbms_output.put_line('Total unused blocks =
loop
dbms_space.unused_space(USER,get_index_rec.index_name,'INDEX' ,
total_blocks , =
=
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_at_freelists.org'
Subject: RE: ((RE)) Calculating LIOs
Nope, cause it's not in the data dictionary....;-)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
ryan.gaffuri_at_comcast.net
Sent: Friday, August 13, 2004 1:56 PM
To: oracle-l_at_freelists.org
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Aug 13 2004 - 13:25:22 CDT
 |
 |