OraFAQ Forum: Server Administration » How to find the size of an index

Home » RDBMS Server » Server Administration » How to find the size of an index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to find the size of an index [message #61258]

Thu, 08 April 2004 00:30

James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)

Member

We have a tablespace which contains user tables and indexes. In the table space there are 1148 indexes. I want to move the indexes into their own new tablespace. How can i find out what the current size of an index is so that i can estimate a size for a new tablespace? (oracle 8i on a Sun Solaris 8 unix platform).

Thanks.�

Report message to a moderator

Re: How to find the size of an index [message #61259 is a reply to message #61258]

Thu, 08 April 2004 01:05

ora9iDBA
Messages: 31
Registered: March 2003

Member

select segment_name,sum(bytes/1024/1024) SIZE_MB
from all_segments where segment_type = 'INDEX'

Report message to a moderator

Re: How to find the size of an index [message #61261 is a reply to message #61259]

Thu, 08 April 2004 03:05

Thiru
Messages: 1089
Registered: May 2002

Senior Member

All_segments doesnt exist. Try this

thiru@9.2.0:SQL>select sum(bytes) from dba_segments where tablespace_name='USERS' and
2 segment_type='INDEX';

SUM(BYTES)
----------
5505024

to get the size of all indexes of all owners in a given tablespace. User_segments can be used to find out ,what you own.

-Thiru

Report message to a moderator