show_table_info

set linesize 200 pagesize 1000
col column_name for a40
col segment_name new_value table_name noprint
col analyzed                      format a16
col owner                         format a16                                                                   
col partition_name                format a18
col index_name                    format a20
col column_name                   format a24
col segment_name                  format a24
col table_name                    format a24
col table_owner                   format a18
prompt "-------------------------"
prompt "segment_size"
prompt "-------------------------"
select owner,segment_name,sum(bytes)/1024/1024 size_m 
from dba_segments 
where segment_name =upper( '&segment_name') 
group by owner, segment_name;
prompt
prompt
prompt "-------------------------"
prompt "table_stats"
prompt "-------------------------"
select owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len,
       partitioned,
       to_char(last_analyzed,'yyyymmdd hh24:mi:ss') as analyzed ,
       num_rows * avg_row_len / 1024 /1024 / 0.9 est_M 
from dba_tables where table_name = '&table_name'
order by 1,2;

prompt
prompt
prompt "----------------"
prompt "Indexes of table"
prompt "----------------"
col index_name format a36
col index_type format a12
col uniqueness format a12
col analyzed format a18
select owner, index_name,index_type,uniqueness,num_rows,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,
       status,partitioned,distinct_keys 
from dba_indexes 
where table_name = upper('&table_name')
order by 1,2;

prompt
prompt
prompt "----------------"
prompt "index columns"
prompt "----------------"

col index_owner format a18 
select index_owner, 
       index_name,
       column_name,
       column_position 
from dba_ind_columns 
where table_name = upper('&table_name')
order by 1,2,4;

prompt
prompt
prompt "------------------"
prompt "Index partition analyzed"
prompt "------------------"
select index_owner,
       index_name,
       partition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi:ss') as analyzed,
       distinct_keys,
       num_rows,
       status
from dba_ind_partitions
where index_name  in (select index_name from dba_part_indexes where table_name  = upper('&table_name'))
order by 1,2,3;


prompt
prompt "----------------"
prompt "index statistics"
prompt "----------------"
select owner,column_name,
       num_distinct,
       histogram,num_distinct,
       num_nulls,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed 
from dba_tab_col_statistics 
where table_name = upper('&table_name' )
order by 1,2;

prompt
prompt "----------------"
prompt "Related objects created"
prompt "----------------"
select * from (select owner,subobject_name,object_type,to_char(created,'yyyymmdd hh24:mi') as created from dba_objects where object_name= upper('&table_name') order by 2) where rownum<101;
prompt
prompt
prompt "-------------------------"
prompt "partition_type"
prompt "-------------------------"
select owner,PARTITIONING_TYPE,SUBPARTITIONING_TYPE 
from dba_part_tables 
where table_name = upper('&table_name')
order by 1,2;
                                                           
prompt "-------------------------"
prompt "partition column"
prompt "-------------------------"
select 'part' type,a.* from dba_part_key_columns a  where name=upper('&table_name' )      
union all                                                                          
select 'subpart' type,a.* from dba_subpart_key_columns a  where name= upper('&table_name' );

prompt
prompt "-------------------------"
prompt "partition_stats"
prompt "-------------------------"
select table_owner,
       partition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,
       num_rows,round(num_rows*avg_row_len/1024/1024) as size_mb 
from dba_tab_partitions 
where table_name = upper('&table_name' )
order by table_owner,partition_name;

prompt
prompt
prompt "-------------------------"
prompt "sub partition_stats"
prompt "-------------------------"
select table_owner,
       partition_name,
       subpartition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,num_rows 
from dba_tab_subpartitions 
where table_name = upper('&table_name' )
order by 1,2,3;
prompt
prompt "-------------------------"
prompt "Table analyze history"
col owner format a12
col object_name format a24
col analyzed format a18
col rowcnt format 9999999999
col blkcnt format 99999999
select a.owner,
       a.object_name,
       to_char(b.ANALYZETIME,'yyyymmdd hh24:mi:ss') as analyzed,
       b.rowcnt,
       b.blkcnt
  from dba_objects a,sys.wri$_optstat_tab_history b
 where a.object_type = 'TABLE'
   and a.object_name  = upper('&table_name')
   and a.object_id = b.obj#
  order by 1,2,3;

undefine segment_name
undefine table_name