<jsp:directive.page pageEncoding="UTF-8" />
select 
  TABLE_OWNER,
  TABLE_NAME,
  INDEX_NAME,
  ANALYZED,
  LAST_ANALYZED,
  PARTITIONED,
  PARTITION_NAME
from
 (
  select
    dt.owner TABLE_OWNER,
    dt.table_name  ,
    '' PARTITION_NAME,
    '' INDEX_NAME,
    'NO' PARTITIONED,
    '1' dummy,
    dt.last_analyzed,
    decode(nvl(dt.num_rows,999),999,'X','O') ANALYZED
  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(nvl(dtp.num_rows,999),999,'X','O') TABLE_ANALYZE_STAT
  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(nvl(di.num_rows,999),999,'X','O') TABLE_ANALYZE_STAT
  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(nvl(dip.num_rows,999),999,'X','O') TABLE_ANALYZE_STAT
  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
