Table and its Index(es) script..

From: Tony Adolph <tonyadolph_at_my-deja.com>
Date: Thu, 08 Jul 1999 08:31:47 GMT
Message-ID: <7m1nlj$5rb$1@nnrp1.deja.com>

Greetings All,

I have produced a small script that give a table name will return something like the following:

Owner.Table   Size (Mbytes)              Index Name     Size (Mbytes)
---------------------------     -------------------    -------------
PIN.TABLE_T       8,720.000         TABLE_EVENT__NO            4.004
                               I_TABLE_ITEM_OBJ__ID          876.855
                             I_TABLE__ACCTOBJ_END_T        1,034.033
                                     I_TABLE__END_T          996.973
                                        I_TABLE__ID          704.688
                             I_TABLE__SERVOBJ_END_T        1,205.176
***************************************************    -------------
sum                                                        4,821.729

If there are two or more tables named TABLE_T then the script will group these.

The problem is that if the index is owned by another user e.g. COMPANY_BESPOKE (to differentiate between Application stuff and our tuning) the script can't join the table name AND owner (because there obviously isn't a match).

Is there another way to join a table and its indexes other than via dba_indexes.owner, table_name and index_name which wont work if the index is owned by someone else?

For anyone interested, here's the script....

Thanks in advance,
Tony Adolph

get_size.sql ----------------------------------- set verify off set feedback off
- drop view v_tab_size_xyxyxy;
- drop view v_ind_size_xyxyxy;

accept table_name prompt "Enter Table Name : "

create view v_tab_size_xyxyxy as

select segment_name TABLE_NAME, e.owner, index_name, sum (bytes)/1024/1024 Size_Mbytes

	from dba_extents e, dba_indexes i
	where segment_name like upper('&&table_name') and
	segment_name = i.table_name (+) and
	e.owner = i.owner (+)
	group by segment_name, e.owner, index_name

create view v_ind_size_xyxyxy as

select segment_name INDEX_NAME, owner, sum(bytes)/1024/1024 Size_Mbytes

	from dba_extents
	where segment_name in
	(select index_name from dba_indexes
	where table_name like upper('&table_name'))
	group by segment_name, owner

select * from v_tab_size_xyxyxy;
select * from v_ind_size_xyxyxy;

break on table_and_size skip 1;
compute sum of INDEX_SIZE_MB on table_and_size;

column table_and_size format a40 heading 'Owner.Table     Size (Mbytes)'
column index_name format a24 heading 'Index Name'
column index_size_mb format 9,999.999 heading 'Size (Mbytes)'

select
	rpad(t.owner||'.'||t.table_name,30)||to_char
(t.Size_Mbytes,'9,999.999') table_and_size,
	nvl(i.index_name,' No Index on this table') Index_name,
	nvl(i.Size_Mbytes,0) Index_Size_MB
from
	v_tab_size_xyxyxy t, v_ind_size_xyxyxy i
where
	t.owner = i.owner (+) AND
	t.index_name = i.index_name (+)
order by
	t.owner, t.table_name, t.Size_Mbytes

that's it folks ---------------------

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 03:31:47 CDT