From Oramosswiki
SELECT *
--******************************************************************************
--*
--* Description
--* ***********
--* Goes through the dictionary to find any indexes where the compression
--* attribute value does not match between the TABLE, PARTITION and / or
--* SUBPARTITION levels.
--* ----------------------------------------------------------------------------
--* Date Author Description
--* =========== ================= ============================================
--* 21-APR-2009 Jeff Moss Created
--******************************************************************************
FROM (
SELECT di.owner
, di.index_name
, dic.count_of_index_columns
, di.compression
, dip.min_ptn_compression
, dip.max_ptn_compression
, NVL(dis.min_subptn_compression,di.compression) min_subptn_compression
, NVL(dis.max_subptn_compression,di.compression) max_subptn_compression
FROM dba_indexes di
, (SELECT index_owner
, index_name
, COUNT(*) count_of_index_columns
FROM dba_ind_columns
GROUP BY index_owner
, index_name
) dic
, (SELECT index_owner
, index_name
, MIN(compression) min_ptn_compression
, MAX(compression) max_ptn_compression
FROM dba_ind_partitions
GROUP BY index_owner
, index_name) dip
, (SELECT index_owner
, index_name
, MIN(compression) min_subptn_compression
, MAX(compression) max_subptn_compression
FROM dba_ind_subpartitions
GROUP BY index_owner
, index_name) dis
WHERE di.owner = dip.index_owner
AND di.index_name = dip.index_name
AND di.owner = dic.index_owner
AND di.index_name = dic.index_name
AND di.owner = dis.index_owner(+)
AND di.index_name = dis.index_name(+)
)
WHERE compression != min_ptn_compression
OR compression != max_ptn_compression
OR compression != min_subptn_compression
OR compression != max_subptn_compression
ORDER BY owner,index_name
/
