Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
titleExample of output
languagesql
USER_NAME             PARTITIONED      TABLESPACE_NAME       TABLE_NAME            PARTITIONED_TABLE     MAX(KB)          ALLOC(KB)        USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     CUSTOMER              -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     DEPARTMENT            -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     EMPLOYEE              -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     GOODS                 -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  USER_DATA             ORDERS                -                        2,097,152              256        .01
SYS                   PARTITIONED      PART_DATA             PART_T1               P1                         262,144              512        .20
SYS                   PARTITIONED      PART_DATA             PART_T1               P2                         262,144              512        .20
SYS                   PARTITIONED      PART_DATA             PART_T1               P3                         262,144           18,176       6.93
SYS                   PARTITIONED      PART_DATA             PART_T2               P201406                    262,144            9,472       3.61
SYS                   PARTITIONED      PART_DATA             PART_T2               P201407                    262,144           18,944       7.23
SYS                   PARTITIONED      PART_DATA             PART_T2               P201408                    262,144            4,864       1.86
SYS                   PARTITIONED      PART_DATA             PART_T2               P201512                    262,144            7,168       2.73
SYS                   PARTITIONED      PART_DATA_DEF         PART_T2               PMAX                       262,144            8,192       3.13
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                DEF                      2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q1_2014                  2,097,152            1,536        .07
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q2_2014                  2,097,152              768        .04
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q3_2014                  2,097,152            2,304        .11
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q4_2014                  2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           DEF                      2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q1_2014                  2,097,152              768        .04
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q2_2014                  2,097,152            1,536        .07
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q3_2014                  2,097,152            1,280        .06
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q4_2014                  2,097,152              768        .04
23 rows selected.

 

Disk Index

...

Code Block
languagesql
set linesize 1024
set colsize 20
SELECT U.USER_NAME USER_NAME                                                                                                   -- Database user  
     , I_LIST.TABLE_NAME                                                                                                       -- Table name
     , DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME                          -- Partitioned table name. If a non-partitioned, then NON-PARTITIONED
     , I_LIST.INDEX_NAME INDEX_NAME                                                                                            -- Index name
     , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX             -- Partitioned index name
     , TBS.NAME TBS_NAME                                                                                                       -- Tablespace to which the index belongs  
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)'                                                          -- Maximum size of table space
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)'               -- Total size allocated from the index 
     , TO_CHAR((((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/(D.MAX*TBS.PAGE_SIZE))*100), '99.99') 'USAGE(%)'   -- Percentage of utilization compared to the maximum size of the tablespace
  FROM (SELECT T.TABLE_NAME
             , PT.PARTITION_NAME
             , I.INDEX_NAME
             , PI.INDEX_PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
             , I.INDEX_ID
             , T.USER_ID
          FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST
     , V$SEGMENT SEG
     , V$INDEX I
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
 WHERE 1=1
   AND SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
   AND SEG.SPACE_ID = I_LIST.TBS_ID
   AND I_LIST.INDEX_ID = I.INDEX_ID
   AND I_LIST.TABLE_OID = I.TABLE_OID
   AND I_LIST.TBS_ID = TBS.ID
   AND D.SPACEID = I_LIST.TBS_ID
   AND U.USER_ID = I_LIST.USER_ID
 ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
; 

...