Get partitioning and subpartitioning info

From Oramosswiki

--
-- Purpose:
--  Shows partitioning and subpartitioning information for all tables.
--
-- Change History
--
-- Date         Author             Description
-- ===========  =================  ================================================
-- 04-FEB-2010  Jeff Moss          Created
--
WITH partition_columns AS
(
SELECT dpt.owner
,      dpt.table_name
,      dpt.partitioning_type
,      MAX(CASE WHEN dpkc.column_position = 1 THEN dpkc.column_name END) ptn_col1
,      MAX(CASE WHEN dpkc.column_position = 2 THEN dpkc.column_name END) ptn_col2
,      MAX(CASE WHEN dpkc.column_position = 3 THEN dpkc.column_name END) ptn_col3
,      MAX(CASE WHEN dpkc.column_position = 4 THEN dpkc.column_name END) ptn_col4
,      MAX(CASE WHEN dpkc.column_position = 5 THEN dpkc.column_name END) ptn_col5
,      MAX(CASE WHEN dpkc.column_position = 6 THEN dpkc.column_name END) ptn_col6
,      MAX(CASE WHEN dpkc.column_position = 7 THEN dpkc.column_name END) ptn_col7
,      MAX(CASE WHEN dpkc.column_position = 8 THEN dpkc.column_name END) ptn_col8
FROM   dba_part_key_columns dpkc
,      dba_part_tables dpt
WHERE  dpkc.owner = dpt.owner
AND    dpkc.name = dpt.table_name
GROUP BY dpt.owner, dpt.table_name, dpt.partitioning_type
)
, subpartition_columns AS
(
SELECT dpt.owner
,      dpt.table_name
,      dpt.subpartitioning_type
,      MAX(CASE WHEN dskc.column_position = 1 THEN dskc.column_name END) sub_ptn_col1
,      MAX(CASE WHEN dskc.column_position = 2 THEN dskc.column_name END) sub_ptn_col2
,      MAX(CASE WHEN dskc.column_position = 3 THEN dskc.column_name END) sub_ptn_col3
,      MAX(CASE WHEN dskc.column_position = 4 THEN dskc.column_name END) sub_ptn_col4
,      MAX(CASE WHEN dskc.column_position = 5 THEN dskc.column_name END) sub_ptn_col5
,      MAX(CASE WHEN dskc.column_position = 6 THEN dskc.column_name END) sub_ptn_col6
,      MAX(CASE WHEN dskc.column_position = 7 THEN dskc.column_name END) sub_ptn_col7
,      MAX(CASE WHEN dskc.column_position = 8 THEN dskc.column_name END) sub_ptn_col8
FROM   dba_subpart_key_columns dskc
,      dba_part_tables dpt
WHERE  dskc.owner = dpt.owner
AND    dskc.name = dpt.table_name
GROUP BY dpt.owner, dpt.table_name, dpt.subpartitioning_type
)
SELECT tab.owner
,      tab.table_name
,      pc.partitioning_type
,      REGEXP_REPLACE(REPLACE(pc.ptn_col1||','||pc.ptn_col2||','||pc.ptn_col3||','||pc.ptn_col4||','||pc.ptn_col5||','||pc.ptn_col6||','||pc.ptn_col7||','||pc.ptn_col8
                             ,',,')
                     ,',$') ptn_column_list
,      sc.subpartitioning_type
,      REGEXP_REPLACE(REPLACE(sc.sub_ptn_col1||','||sc.sub_ptn_col2||','||sc.sub_ptn_col3||','||sc.sub_ptn_col4||','||sc.sub_ptn_col5||','||sc.sub_ptn_col6||','||sc.sub_ptn_col7||','||sc.sub_ptn_col8
                             ,',,')
                     ,',$') sub_ptn_column_list
FROM   dba_tables tab
,      partition_columns pc
,      subpartition_columns sc
WHERE  tab.owner = pc.owner(+)
AND    tab.table_name = pc.table_name(+)
AND    pc.owner = sc.owner(+)
AND    pc.table_name = sc.table_name(+)
ORDER BY 1,2
/

Which gives output similar to:

FRED                TABLE1               RANGE    COL1    HASH    COL1,COL2
WILMA                TABLE2               LIST    COL1