/*  Statistics details for table partitions/subpartitios

    Usage: @tspstat <owner> <table> <partition>

    vplesnila 2024-01-29: creation
*/

col owner                                                                               for a20                         head "Owner"
col table_name                                                                  for a25                     head "Table name"
col PARTITION_POSITION                          for 999             heading "P#"
col PARTITION_NAME                              for a25             head "Partition name"
col SUBPARTITION_NAME                           for a25             head "Subpartition name"
col last_DML                                    for a20             head "Last DML"
col last_analyzed                               for a20             head "Last Analyzed"
col STALE_STATS                                 for a7              head "Stale"
col NUM_ROWS                                                        head "#Rows"
col INSERTS                                                         head "Table|INS"
col UPDATES                                                         head "Table|UPD"
col DELETES                                                         head "Table|DEL"
col TRUNCATED                                                       head "Table|TRUN"

break on report skip 1
compute sum of INSERTS on report
compute sum of UPDATES on report
compute sum of DELETES on report

select
     tabstat.OWNER
    ,tabstat.TABLE_NAME
        ,tabstat.PARTITION_POSITION
    ,tabstat.PARTITION_NAME
    ,tabstat.SUBPARTITION_NAME
    ,tabstat.NUM_ROWS
    ,tabstat.STALE_STATS
    ,tabstat.LAST_ANALYZED
    ,tabmod.INSERTS
    ,tabmod.UPDATES
    ,tabmod.DELETES
    ,tabmod.TRUNCATED
    ,tabmod.TIMESTAMP last_DML
from
     dba_tab_statistics tabstat
    ,dba_tab_modifications tabmod
WHERE
    tabstat.owner = '&&1'
    AND tabstat.table_name = '&&2'
        AND (tabstat.partition_name like '%&&3%' or tabstat.partition_name is NULL)
        AND (tabstat.subpartition_name like '%&&4%' or tabstat.subpartition_name is NULL)
    AND tabstat.owner = tabmod.table_owner(+)
    AND tabstat.table_name = tabmod.table_name(+)
    AND tabstat.partition_name = tabmod.partition_name(+)
    AND tabstat.subpartition_name = tabmod.subpartition_name(+)
ORDER BY
    tabstat.owner asc,
    tabstat.table_name asc,
    tabstat.partition_position asc,
    tabstat.subpartition_position asc
/