Forum Stats

  • 3,782,584 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

10g: How to determine Local Partition Index Space UTILIZATION

735819
735819 Member Posts: 22
edited Mar 2, 2010 12:36PM in General Database Discussions
I'm trying to determine how much space is actually utilized by different local partition indexes. Some are b-tree and some are bitmap. So far when I query dba_segments and dba_part_indexes I just see space available, not the space utilized.

Does anybody know a way to know the actual space utilized by a local partition index?
Tagged:

Answers

  • Robert Geier
    Robert Geier Member Posts: 2,989
    utilized = total - available ?
  • KeshavM
    KeshavM Member Posts: 178
    You can try this - give the local index name

    SELECT TO_CHAR(SPACE_ALLOCATED / 1024 / 1024, 999990.99) , TO_CHAR(SPACE_USED / 1024 / 1024, 999990.99) NET_MB, TO_CHAR((SPACE_ALLOCATED - SPACE_USED) / 1024 / 1024, 999990.99) UNUSED_MB FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF(OwnerName, IndexName, 'INDEX', NULL ) ) ;
    KeshavM
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Does anybody know a way to know the actual space utilized by a local partition index?
    SUM(BYTES) from dba_extents
    SQL> desc dba_extents
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER						    VARCHAR2(30)
     SEGMENT_NAME					    VARCHAR2(81)
     PARTITION_NAME 				    VARCHAR2(30)
     SEGMENT_TYPE					    VARCHAR2(18)
     TABLESPACE_NAME				    VARCHAR2(30)
     EXTENT_ID					    NUMBER
     FILE_ID					    NUMBER
     BLOCK_ID					    NUMBER
     BYTES						    NUMBER
     BLOCKS 					    NUMBER
     RELATIVE_FNO					    NUMBER
    sb92075
  • 735819
    735819 Member Posts: 22
    Do you know the syntax for plugging in the partition. I'm using a partition index and that query needs to know partition specification
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Do you know the syntax for plugging in the partition. I'm using a partition index and that query needs to know partition specification
    You want query but have NOT provided any specifics about your index, partitions, tables, etc.

    It is a challenge to compose SQL when all details are unknown.
  • KeshavM
    KeshavM Member Posts: 178
    try in a pl/sql block

    you need to use cursor-
    first select the local index names from dba_segments with the help of index name and then pass the variable to the above query
    KeshavM
  • 735819
    735819 Member Posts: 22
    I found out this system is using Automatic segment space management (ASSM). Evidently the way utilized space is queried is different from non-ASSM Oracle. Does anybody know the method to find out how much space a local partition index is actually using for an ASSM implementation?
This discussion has been closed.