Partitioned Tables Report for a Schema

This query -- once you adjust it for the schemas you’re interested in (and tables you’re not) -- will show pertinent information on the tables that are partitioned.

As you may know, large tables are often partitioned. A partitioned table is actually many small tables joined into a larger one so that any one part can be read for speed or the entire table can be read if required. Reading a partition improves query performance.

For example:

SELECT *
  FROM ods_prd.log_helpdesk_error PARTITION (P568266); 

You will usually need to find the latest partition needed for your query as they are constantly being added (and possibly dropped). This doesn’t necessarily apply to tables partitioned on dist_id or period_code.

WITH atp
     AS (SELECT table_owner
              , table_name
              , partition_name
              , partition_position
              , num_rows
              , blocks
              , last_analyzed
           FROM (SELECT ROW_NUMBER() OVER (PARTITION BY table_owner
                                                      , p.table_name
                                               ORDER BY partition_name DESC) AS row#
                      , p.*
                   FROM all_tab_partitions p
                  WHERE table_owner = 'ODS_PRD'
                    AND NOT REGEXP_LIKE(p.table_name, 'PROMO')
                    AND num_rows > 0
                )
          WHERE row# = 1 -- shown only the last one
        )
SELECT apt.owner
     , table_name
     , partitioning_type                     AS type
     , column_name
     , column_position                       AS col#
     , TO_CHAR(partition_count, '9,999,999') AS part#
     , status
     , partition_name                        AS last_name
     , TO_CHAR(num_rows, '999,999,999')      AS last_#_rows
     , TO_CHAR(last_analyzed, 'MM/DD/YYYY')  AS analyzed
  FROM all_part_tables apt
  JOIN all_part_key_columns apc
    ON apt.table_name = apc.name
   AND apt.owner = apc.owner
  JOIN atp
    ON apt.table_name = atp.table_name
   AND apt.owner = atp.table_owner
WHERE apt.owner  = 'ODS_PRD'
   AND apc.owner = 'ODS_PRD' 
   AND NOT REGEXP_LIKE(table_name, 'PROMO')
   AND partition_count > 1 -- don't show tables with 1 partition
   AND num_rows > 0 -- don't show empty tables
ORDER BY owner
        , table_name;