<jsp:directive.page pageEncoding="UTF-8" />
select 
  TABLE_OWNER,
  TABLE_NAME,
  IND_OWNER,
  INDEX_NAME,
  PARTITIONED,
  PARTITION_NAME,
  I_TS_NAME
from
 (
 select
    dt.owner TABLE_OWNER,
    dt.table_name,
    di.owner IND_OWNER,
    di.index_name  INDEX_NAME,
    '---'  PARTITION_NAME,
    'NO'  PARTITIONED,
    di.tablespace_name I_TS_NAME
  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.INDEX_OWNER INDEX_OWNER,
    dip.index_name  INDEX_NAME,
    dip.partition_name PARTITION_NAME,
    'YES'  PARTITIONED,
    dip.tablespace_name I_TS_NAME
  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,INDEX_NAME,PARTITION_NAME
