index size

[elaine3839]

I found my tablespace for index is full since I got error message 'Unable to create initial extent for segment in tablespace user_indx'. How can I find out how much space each index occupies?

Shall I check the user_indexes view? Which column should I look at? And what's the easiest way for me to find out which objects are sit in this tablespace?

[dknight]

Look at DBA_SEGMENTS. It has the tablespace_name, the segment_type (INDEX) and the bytes.

Good luck.

[elaine3839]

Thanks dknight!

However, I tried to describe dba_segments and Oracle said it's not a valid object! Could it be under a different name?

[giddy]

are you sure !!!!

SQL> desc dba_segments

Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)

[dknight]

Check to see if you are logged in with DBA privileges.

Good luck.

[elaine3839]

I did log in as SYS, and what's interesting is that I can see this view does exist by selecting from dictionary, and I can even select from dba_segments. However, when I tried to describe dba_segments, I got:

ORA-24372: invalid object for describe

Any idea why?

[elaine3839]

dknight,

By query against DBA_SEGMENTS, I got all info I needed. Thanks so much for your help!

Giddy -- Thank you too! Although I still can't describe this view for some reason...

[jmodic]

There was some bug in earlier releases of 8i preventing dictionary views to be described by SQL*Plus. I suppose you are using 8.1.5, no?

[elaine3839]

No --- I am using Oracle 8.1.7. And I can describe most of the dictionary views, although I didn't try all of them. And I can even describe this view on a different oracle instance on the same server!

Let's just assume this is a *random* oracle bug :-)