How to tell if a partitioned index is global or local?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy 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
    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!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

    Code:
    SQL> select LOCALITY, COUNT(*) FROM  ALL_PART_INDEXES GROUP BY LOCALITY;
    
    LOCALI   COUNT(*)
    ------ ----------
    LOCAL          11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width