@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display STALE Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
*  Parameters : 1 - OWNER
*               2 - Table Name
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  16-Mar-12  Vishal Gupta  Intial version
*
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE table_name
UNDEFINE partition_name
UNDEFINE subpartition_name
UNDEFINE object_type

DEFINE owner="&&1"
DEFINE table_name="&&2"
DEFINE partition_name="&&3"
DEFINE subpartition_name="&&4"
DEFINE object_type="&&5"

COLUMN  _owner             NEW_VALUE owner             NOPRINT
COLUMN  _table_name        NEW_VALUE table_name        NOPRINT
COLUMN  _partition_name    NEW_VALUE partition_name    NOPRINT
COLUMN  _subpartition_name NEW_VALUE subpartition_name NOPRINT
COLUMN  _object_type       NEW_VALUE object_type       NOPRINT

set term off
SELECT CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1) 
           ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner')) 
       END                                                          "_owner"
     , CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) 
           ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name')) 
       END                                                          "_table_name"
     , DECODE('&&partition_name','','%','&&partition_name')         "_partition_name"
     , DECODE('&&subpartition_name','','%','&&subpartition_name')   "_subpartition_name"
     , DECODE('&&object_type','','%','&&object_type')               "_object_type"
FROM DUAL;
set term on

PROMPT
PROMPT ***************************************************
PROMPT *  Table Modifications
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - Owner             = '&&owner'
PROMPT *  - Table Name        = '&&table_name'
PROMPT *  - Partition Name    = '&&partition_name'
PROMPT *  - SubPartition Name = '&&subpartition_name'
PROMPT *  - Object Type       = '&&object_type'
PROMPT ***************************************************
PROMPT

COLUMN table_owner             HEADING "Owner"                    FORMAT a20
COLUMN table_name              HEADING "TableName"                FORMAT a60
COLUMN partition_name          HEADING "Partition"                FORMAT a25
COLUMN subpartition_name       HEADING "Sub-Parition"             FORMAT a25
COLUMN last_analyzed           HEADING "LastAnalyzed"             FORMAT a18
COLUMN last_modification       HEADING "LastModification"         FORMAT a18
COLUMN num_rows                HEADING "NumberOfRows"             FORMAT 999,999,999,999
COLUMN percent_modification    HEADING "Modification|(%)"         FORMAT 999,999,999.99
COLUMN modifications_total     HEADING "Modification|Total|(#)"   FORMAT 999,999,999,999
COLUMN inserts                 HEADING "Inserts|(#)"              FORMAT 99,999,999
COLUMN updates                 HEADING "Updates|(#)"              FORMAT 99,999,999
COLUMN deletes                 HEADING "Deletes|(#)"              FORMAT 999,999,999,999
COLUMN truncated               HEADING "Truncated"                FORMAT a5
COLUMN drop_segments           HEADING "Drop|Segments|(#)"        FORMAT 999



SELECT 
       m.table_owner || '.' || m.table_name 
       || NVL2(m.partition_name,':' || m.partition_name, '')
       || NVL2(m.subpartition_name,':' || m.subpartition_name, '')
       table_name
     --m.table_owner
     --, m.table_name
     --, m.partition_name
     --, m.subpartition_name
     , TO_CHAR(NVL(s.last_analyzed,NVL(p.last_analyzed,t.last_analyzed)),'DD-MON-YY HH24:MI:SS') last_analyzed
     , TO_CHAR(m.timestamp,'DD-MON-YY HH24:MI:SS') last_modification
     , NVL(s.num_rows,NVL(p.num_rows,t.num_rows)) num_rows
     , ROUND((m.inserts + m.updates + m.deletes) 
              / DECODE(NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,0)))
                      , 0 ,m.inserts + m.updates + m.deletes
                      , DECODE(NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,1)))
                              , 0 , 1
                              , NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,1)))
                              )
                      ) 
              * 100 
             , 2) percent_modification
     , m.inserts + m.updates + m.deletes modifications_total
     , m.inserts
     , m.updates
     , m.deletes
     , m.truncated
     , m.drop_segments
  FROM dba_tab_modifications m
       LEFT OUTER JOIN dba_tables t ON  t.owner = m.table_owner AND t.table_name  = m.table_name
       LEFT OUTER JOIN dba_tab_partitions p ON p.table_owner = m.table_owner AND p.table_name  = m.table_name AND p.partition_name = m.partition_name
       LEFT OUTER JOIN dba_tab_subpartitions s ON s.table_owner = m.table_owner AND s.table_name  = m.table_name 
                   AND s.partition_name = m.partition_name AND s.subpartition_name = m.subpartition_name
WHERE m.inserts + m.updates + m.deletes > 0
  AND m.table_owner                 LIKE '&&owner'
  AND m.table_name                  LIKE '&&table_name'
  AND NVL(m.partition_name,'%')     LIKE '&&partition_name' --ESCAPE '\'
  AND NVL(m.subpartition_name,'%')  LIKE '&&subpartition_name' --ESCAPE '\'
ORDER BY percent_modification desc nulls last
;



@@footer