<jsp:directive.page pageEncoding="UTF-8" />
select 
  TABLE_OWNER,
  table_name,
  INDEX_NAME,
  ANALYZED_DAY,
  PARTITIONED,
  PARTITION_NAME
from
 (
  select
    dt.owner       TABLE_OWNER,
    dt.table_name  ,
    ''             PARTITION_NAME,
    ''             INDEX_NAME,
    'NO'           PARTITIONED,
    '1'            dummy,
    dt.last_analyzed,
    decode(trunc(dt.last_analyzed),trunc(sysdate),'0',NULL,'---',
      trunc(sysdate)-trunc(dt.last_analyzed)) ANALYZED_DAY
  from 
    sys.dba_tables dt
  where dt.owner not in 
        ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','OUTLN')
    and dt.owner like '{@OWNER}%'
    and dt.partitioned='NO'
  union
  select
    dtp.table_owner TABLE_OWNER,
    dtp.table_name,
    dtp.partition_name PARTITION_NAME,
    ''  INDEX_NAME,
    'YES'  PARTITIONED,
    '2'    dummy,
    dtp.last_analyzed,
    decode(trunc(dtp.last_analyzed),trunc(sysdate),'0',NULL,'---',
     trunc(sysdate)-trunc(dtp.last_analyzed)) ANALYZED_DAY
  from 
    sys.dba_tab_partitions dtp
  where dtp.table_owner not in 
        ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','OUTLN')
    and dtp.table_owner like '{@OWNER}%'
 union
 select
    dt.owner TABLE_OWNER,
    dt.table_name,
    ''  PARTITION_NAME,
    di.index_name  INDEX_NAME,
    'NO'  PARTITIONED,
    '3'    dummy,
    di.last_analyzed,
    decode(trunc(di.last_analyzed),trunc(sysdate),'0',NULL,'---',
     trunc(sysdate)-trunc(di.last_analyzed)) ANALYZED_DAY
  from 
    sys.dba_indexes di,
    sys.dba_tables dt
  where dt.owner not in 
        ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','OUTLN')
    and dt.owner like '{@OWNER}%'
    and di.partitioned='NO'
    and dt.owner = di.table_owner 
    and dt.table_name = di.table_name
union
 select
    dt.owner TABLE_OWNER,
    dt.table_name,
    dip.partition_name PARTITION_NAME,
    dip.index_name  INDEX_NAME,
    'YES'  PARTITIONED,
    '3'    dummy,
    dip.last_analyzed,
    decode(trunc(dip.last_analyzed),trunc(sysdate),'0',NULL,'---',
     trunc(sysdate)-trunc(dip.last_analyzed)) ANALYZED_DAY
  from 
    sys.dba_indexes di,   
    sys.dba_ind_partitions dip,
    sys.dba_tables dt
  where dt.owner not in 
         ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','OUTLN')
    and dt.owner like '{@OWNER}%'
    and di.partitioned='YES'
    and dt.owner = di.table_owner 
    and dt.table_name = di.table_name
    and di.owner= dip.index_owner
    and di.index_name = dip.index_name
)
order by TABLE_OWNER,table_name,dummy,INDEX_NAME,PARTITION_NAME
