S_TAB

 WITH S_TAB as (
select table_name s_table , owner s_owner
from dba_tables
where table_name =upper('S_ERROR_CODES') and owner =upper('GEAR_ADMIN'))
select attr "S_ERROR_CODES"
from (
select 'Columns : ' || count(*) attr , 1 ord
from dba_tab_columns, s_tab
where table_name =s_table and owner= s_owner UNION
select 'Indexes-Columns : ' || count(distinct index_name) || ' - ' || count(*) , 2
from dba_ind_columns, s_tab
where table_name =s_table and table_owner= s_owner UNION
select 'Constraints : ' || 'Total - '|| sum(ccount) ||' '|| listagg(constraint_type||'-'||ccount||' ') within group ( order by ccount), 3
from (
select constraint_type , count(*) ccount
from dba_constraints , s_tab
where table_name =s_table and owner= s_owner group by constraint_type ) a UNION
select 'Partitions : ' || count(*) , 4
from dba_tab_partitions , s_tab
where table_name =s_table and table_owner= s_owner UNION
select 'Indexes-Null Cols : ' || count(*), 5
from dba_tab_columns, s_tab
where table_name =s_table and owner= s_owner and column_name in (
select column_name
from dba_ind_columns
where table_name =s_table and table_owner =s_owner ) and nullable='Y' UNION
select 'Function Based : ' || count(*) , 6
from dba_ind_expressions , s_tab
where table_name =s_table and table_owner= s_owner UNION
select 'Stat History : ' || count(*) "daysHistoryAvl", 7
from dba_tab_stats_history , s_tab
where table_name =s_table and owner= s_owner UNION
select 'Triggers : ' || count(*) , 8
from dba_triggers a, s_tab
where table_name =s_table and owner= s_owner UNION
select 'Histogram : ' || 'YES' , 9
from dba_tab_histograms , s_tab
where table_name =s_table and owner= s_owner UNION
select /*+ rule */ 'Size : ' || round(sum(bytes)/(1048576),2) ||' - MB', 10
from dba_segments , s_tab
where segment_name =s_table and owner= s_owner UNION
select 'LOB Columns : ' || column_name , 11
from dba_lobs , s_tab
where table_name =s_table and owner= s_owner UNION
select 'INI TRANS : ' || ini_trans , 12
from dba_tables , s_tab
where table_name =s_table and owner= s_owner UNION
select 'Rows : ' || ltrim(to_char(num_rows,'999,999,999,999')) ||' - Analyzed : ' || to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') , 13
from dba_tables , s_tab
where table_name =s_table and owner = s_owner UNION
SELECT 'Referenced : ' || constraint_name ||'->' ||
(select table_name
from dba_constraints b
where b.constraint_name =a.r_constraint_name and a.owner = b.owner), 14
from dba_constraints a , s_tab
where table_name =s_table and owner = s_owner and constraint_type ='R' UNION
select 'Fragmentation : ' ||round ( wasted_space_kb/tblsize_kb * 100), 15 "Fragmentation"
from (
select table_name,round((blocks*8),0) tblsize_kb, round((num_rows*avg_row_len/1024),0) actual_data_kb, (round((blocks*8),2) - round((decode(num_rows,0,1,num_rows)*avg_row_len/1024),0)) wasted_space_kb
from dba_tables , s_tab
where table_name = s_table and owner = s_owner ) UNION
SELECT ' Child Table : ' || decode(
(select r_constraint_name
from dba_constraints , s_tab
where table_name = s_table and owner = s_owner and constraint_type ='R' ) ,null,'NO','YES') , 16
from dual ) order by ord