select distinct a.owner as “用户”,
a.table_name as “表名”,
a.partitioning_type “一级分区类型”,
a.subpartitioning_type “二级分区类型”,
a.partition_count as “一级分区个数”,
b.column_name as “一级分区名”,
c.column_name as “二级分区名”,
count(distinct d.SUBPARTITION_NAME) as “二级分个数”
from DBA_PART_TABLES a
left join DBA_PART_KEY_COLUMNS b
on a.table_name=b.name
left join ALL_SUBPART_KEY_COLUMNS c
on a.table_name=c.name
left join DBA_TAB_SUBPARTITIONS d
on a.table_name=d.table_name
–where a.owner=‘SYSDBA’
group by a.owner,
a.table_name,
a.partitioning_type ,
a.subpartitioning_type ,
a.partition_count ,
b.column_name ,
c.column_name