@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display LOCKED 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 *  S T A L E    S T A T I S T I C S
PROMPT *
PROMPT *  Input Parameters 
PROMPT *  - Table Owner       = '&&owner'
PROMPT *  - Table Name        = '&&table_name'
PROMPT *  - Partition Name    = '&&partition_name'
PROMPT *  - SubPartition Name = '&&subpartition_name'
PROMPT *  - Object Type       = '&&object_type'
PROMPT ***********************************************************************


PROMPT
PROMPT  ***********************************
PROMPT  *  Locked Table Statistics 
PROMPT  ***********************************
PROMPT

COLUMN owner                   HEADING "Owner"                    FORMAT a20
COLUMN table_name              HEADING "TableName"                FORMAT a60
COLUMN index_name              HEADING "IndexName"                FORMAT a60
COLUMN partition_name          HEADING "Partition"                FORMAT a25
COLUMN partition_position      HEADING "Part|Pos"                 FORMAT 999
COLUMN subpartition_name       HEADING "Sub-Parition"             FORMAT a25
COLUMN subpartition_position   HEADING "Sub|Part|Pos"             FORMAT 999
COLUMN last_analyzed           HEADING "LastAnalyzed"             FORMAT a18
COLUMN last_analyzed           HEADING "LastAnalyzed"             FORMAT a18
COLUMN stattype_locked         HEADING "Lock"                     FORMAT a5
COLUMN global_stats            HEADING "Global|Stat"              FORMAT a5
COLUMN user_stats              HEADING "User|Stat"                FORMAT a4
COLUMN stale_stats             HEADING "Stale|Stat"               FORMAT a5
COLUMN sample_size             HEADING "SampleSize"               FORMAT 999,999,999
COLUMN num_rows                HEADING "RowCount"                 FORMAT 99,999,999,999
COLUMN blocks                  HEADING "Blocks"                   FORMAT 999,999,999
COLUMN empty_blocks            HEADING "EmptyBlocks"              FORMAT 99,999,999
COLUMN avg_space               HEADING "AvgSpace"                 FORMAT 99,999,999
COLUMN chain_cnt               HEADING "ChainCount"               FORMAT 99,999,999
COLUMN avg_row_len             HEADING "AvgRowLength"             FORMAT 9999


select /*+ RULE */ 
       s.owner || '.' || s.table_name 
       || NVL2(s.partition_name,':' || s.partition_name, '')
       || NVL2(s.subpartition_name,':' || s.subpartition_name, '')
       table_name
     , stattype_locked
     , global_stats
     , user_stats
     , stale_stats
     , to_char(last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
     , sample_size
     , num_rows
     , blocks
     , empty_blocks
     , avg_space
     , chain_cnt
     , avg_row_len
 FROM dba_tab_statistics s
WHERE s.stattype_locked IS NOT NULL
  AND s.owner NOT IN ('SYS','XDB')     
  AND s.table_name NOT LIKE 'BIN$%'
  AND s.owner LIKE upper('&&owner')
  AND s.table_name LIKE upper('&&table_name')
  AND NVL(s.partition_name,'%')     LIKE upper('&&partition_name') ESCAPE '\'
  AND NVL(s.subpartition_name,'%')  LIKE upper('&&subpartition_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'
ORDER BY s.owner
       , s.table_name
--       , s.partition_position
--       , s.subpartition_position
/


PROMPT
PROMPT  ***********************************
PROMPT  *  Locked Index Statistics 
PROMPT  ***********************************

select /*+ RULE */ 
       s.owner || '.' || s.index_name 
       || NVL2(s.partition_name,':' || s.partition_name, '')
       || NVL2(s.subpartition_name,':' || s.subpartition_name, '')
       index_name
     , stattype_locked
     , global_stats
     , user_stats
     , stale_stats
     , to_char(last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
     , sample_size
     , blevel
     , num_rows
     , leaf_blocks
     , clustering_factor
 FROM dba_ind_statistics s
WHERE s.stattype_locked IS NOT NULL
  AND s.owner NOT IN ('SYS','XDB')     
  AND s.table_name NOT LIKE 'BIN$%'
  AND s.owner LIKE upper('&&owner')
  AND s.table_name LIKE upper('&&table_name')
  AND NVL(s.partition_name,'%')     LIKE upper('&&partition_name') ESCAPE '\'
  AND NVL(s.subpartition_name,'%')  LIKE upper('&&subpartition_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','INDEX PARTITION'
               ,'SUBPARTITION','INDEX SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'
ORDER BY owner
       , table_name
          , index_name
--       , partition_position
--       , subpartition_position
/


UNDEFINE owner
UNDEFINE table_name
UNDEFINE partition_name
UNDEFINE subpartition_name
UNDEFINE object_type


@@footer