How to tell if a partitioned index is global or local?

[elaine3839]

Ok, here is what I have --

select distinct(segment_name) from user_segments
where tablespace_name ='USER_DATA_IX' AND segment_type='INDEX PARTITION';

I got 16 rows back. But if I do

select index_name, partitioned
from dba_indexes
where owner='USERTEST' and tablespace_name='USER_DATA_IX'
AND PARTITIONED='YES';

There was nothing come back! Does that mean the 16 indexes returned back from first query were global partitioned index? Which table/view I can check to find out if a partitioned index is global or local?

Actually, what I wanted to accomplish is to shrink the tablespace from 10GB to 2.5GB, and I can see currently all the space used in that tablespace (including index and index partition) was only 200MB. But when I tried to size that tablespace to 2.5GB, I got error ORA-03297
file contains used data beyond requested RESIZE value. So does it mean I need to shrink the global partitioned index before I can shrink the tablespace? Any input (including syntax help) will be greatly appreciated!

[gandolf989]

elaine3839, Have you looked at any of the following views?

ALL_PART_INDEXES
ALL_PART_TABLES

USER_PART_INDEXES
USER_PART_TABLES

I don't think that partitioned indexes are in the dba_indexes view. Specifically look at the locality field of ALL_PART_INDEXES.

SQL> select LOCALITY, COUNT(*) FROM  ALL_PART_INDEXES GROUP BY LOCALITY;

LOCALI   COUNT(*)
------ ----------
LOCAL          11