@@myheader.sql

/************************************
*  INPUT PARAMETERS
************************************/
DEFINE tab="&&1"
DEFINE col="&&2"

UNDEFINE owner
UNDEFINE table_name
UNDEFINE col_name

COLUMN  _owner              NEW_VALUE owner            NOPRINT
COLUMN  _table_name         NEW_VALUE table_name       NOPRINT
COLUMN  _col_name           NEW_VALUE col_name         NOPRINT

set term off
SELECT 
      upper(CASE 
                    WHEN INSTR('&&tab','.') > 0 THEN 
                        SUBSTR('&&tab',INSTR('&&tab','.')+1)
                    ELSE
                        '&tab'
            END
            )
              "_table_name"
    , upper(CASE 
                    WHEN INSTR('&&tab','.') > 0 THEN
                        UPPER(SUBSTR('&&tab',1,INSTR('&&tab','.')-1))
                     ELSE
                          user
            END
            )
                "_owner"
    , upper('&&col')
                "_col_name"
FROM DUAL;



set term on

PROMPT
PROMPT *************************************************
PROMPT *  Historical column histogram values           
PROMPT *                                               
PROMPT *  Input Parameters                             
PROMPT *  - owner               = '&&owner'            
PROMPT *  - table_name          = '&&table_name'       
PROMPT *  - col_name            = '&&col_name'         
PROMPT *************************************************

/* ----------------------------------------------------------------------------------|
|Author : Mohamed Houri                                                              |
|Date   : 03/07/2020                                                                 |
|Scope  : This script gives historical column histogram values                       |
|          -- I am using sys.WRI$_OPTSTAT_HISTHEAD_HISTORY for this purpose          |      
|          -- I am only able to say whether, previously, there was HISTOGRAM or not  |
|          -- I can't show the historical type of Histogram                          |
|                                                                                    |                                    |
-------------------------------------------------------------------------------------|*/

col object_name         format a25
col column_name         format a25
col last_analyzed       format a20
col prev_last_analyzed  format a20
col histogram           format a16
col prev_histogram      format a16
WITH sq AS
    (
     SELECT
          object_id
         ,object_name
         ,subobject_name
     FROM
         dba_objects
     WHERE
         object_name    = upper ('&&table_name')
     AND owner          = upper('&&owner')
     AND subobject_name IS NULL
     )
SELECT
     object_name
    ,column_name
    ,lead(prev_histogram,1,histogram) over (order by last_analyzed) histogram
    ,last_analyzed
    ,prev_histogram
    ,prev_last_analyzed
FROM
   (
     SELECT
         object_name
        ,column_name
        ,(select histogram from all_tab_col_statistics where owner = upper('&&owner') 
          and table_name = upper('&&table_name') and column_name = upper('&&col_name')) histogram
        ,last_analyzed
        ,stat_time prev_last_analyzed
        ,row_number() over (order by last_analyzed) rn
        ,case when round(derivedDensity,9)= round(density,9) then 'NONE' else 'HISTOGRAM' end prev_histogram
     FROM
        (
         SELECT
             object_name
            ,column_name
            ,to_char(savtime ,'yyyy-mm-dd hh24:mi:ss')     last_analyzed
            ,to_char(timestamp# ,'yyyy-mm-dd hh24:mi:ss') stat_time
            ,density
            ,1/distcnt derivedDensity
            ,row_number() over (order by savtime) rn
            ,lag(case when round(1/distcnt,9) = round(density,9) then 'NONE' else 'HISTOGRAM' end) over(order by savtime) hist_histogram
         FROM
            sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
            INNER JOIN sq ON object_id = obj#
            INNER JOIN (SELECT 
                            column_id
                           ,column_name
                        FROM
                            dba_tab_columns
                        WHERE
                            column_name = upper('&&col_name')
                        AND table_name  = upper('&&table_name') 
                        AND owner       = upper('&&owner')
                        ) ON intcol# = column_id
    )
WHERE
   rn >= 1 --exlcude/include the very first dbms_stat
   )
ORDER BY
    last_analyzed; 

@@myfooter.sql