Oracle Index Partitions Report

This script displays partition information for a specified index or for all indexes in a schema. It queries the DBA_IND_PARTITIONS view to show partition names, tablespaces, extent settings, and row counts, helping DBAs analyze partitioned index structure and storage distribution.

oraclesqlindexing-statisticsv1.0.0

0 stars0 downloads13 views0 comments

By OracleDba • Created 2/23/2026

Code

(29 lines)

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/index_partitions.sql
-- Author       : Tim Hall
-- Description  : Displays partition information for the specified index, or all indexes.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @index_patitions (index_name or all) (schema-name)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF

SELECT a.index_name,
       a.partition_name,
       a.tablespace_name,
       a.initial_extent,
       a.next_extent,
       a.pct_increase,
       a.num_rows
FROM   dba_ind_partitions a
WHERE  a.index_name  = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
AND    a.index_owner = Upper('&&2')
ORDER BY a.index_name, a.partition_name
/

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

Oracle Index Partitions Report

Code

General Comments(0)