Partitioning -- 12 : Data Dictionary Queries

Here's a compilation of some useful data dictionary queries on the implementation of Partitioning.

REM  List all Partitioned Tables in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, table_name, partitioning_type, subpartitioning_type, partition_count, 
def_subpartition_count as default_subpart_count
from dba_part_tables
order by owner, table_name


REM List all Partitioned Indexes in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, index_name, table_name, partitioning_type, subpartitioning_type, partition_count, 
def_subpartition_count as default_subpart_count
from dba_part_indexes
order by owner, index_name


REM List Partition Key Columns for all Partitioned Tables 
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as Partition Key may consist of multiple columns
select owner, name, column_name
from dba_part_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Table SubPartitions 
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as SubPartition Key may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Index SubPartitions 
REM (or filter by OWNER or NAME (NAME is INDEX_NAME when object_type='INDEX'))
REM Need to order by column_position as SubPartition may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'INDEX'
order by owner, name, column_position


REM List all Table Partitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
REM Need to order by partition_position
select table_owner, table_name, partition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_partitions
order by table_owner, table_name, partition_position


REM List all Table SubPartitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
select table_owner, table_name, partition_name, subpartition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_subpartitions
order by table_owner, table_name, subpartition_position


REM List all Index Partitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
REM Need to order by partition_position
REM Note : For Table Names, you have to join back to dba_indexes 
select index_owner, index_name, partition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_partitions
order by index_owner, index_name, partition_position


REM List all Index SubPartitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
select index_owner, index_name, partition_name, subpartition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_subpartitions
order by index_owner, index_name, subpartition_position

I have listed only a few columns from the data dictionary views of interest.  You may extract more information by referencing other columns or joining to other data dictionary views.