How to tell if a partitioned index is global or local?
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
where owner='USERTEST' and tablespace_name='USER_DATA_IX'
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!
elaine3839, Have you looked at any of the following views?
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;
Click Here to Expand Forum to Full Width