column owner format a20
column table_name format a25
column partition_name format a25
column subpartition_name format a25
column MB format 999999
column MB_EST format 999999
column temporary format a5

set ver off

SET FEED OFF
set HEADING off
SELECT '---------TABLA + SEGMENTOS--------' FROM DUAL;
SET FEED ON
set HEADING on

select t.owner,t.table_name,min(t.pct_free) pct_free, min(t.AVG_ROW_LEN) avg_row,min(t.num_rows) num_rows,
t.tablespace_name,sum(s.bytes)/1024/1024 MB,sum(s.blocks) blocks,to_char(min(t.last_analyzed),'ddmmyyyy hh24:mi:ss') last_analyzed
from dba_tables t, dba_segments s
where t.owner = s.owner
and t.table_name = s.segment_name
and t.owner like upper('&1')
and t.table_name like upper('&2')
group by t.owner,t.table_name,t.tablespace_name
order by t.owner,t.table_name,t.tablespace_name;

/*
select t.owner,t.table_name,s.partition_name,s.bytes/1024/1024 MB,s.blocks
from dba_tables t, dba_segments s
where t.owner = s.owner
and t.table_name = s.segment_name
and t.owner like upper('&1')
and t.table_name like upper('&2')
order by t.owner,t.table_name,s.partition_name;
*/

SET FEED OFF
set HEADING off
SELECT '---------PARTICION--------' FROM DUAL;
SET FEED ON
set HEADING on

--select t.owner,t.table_name,p.partition_name,t.num_rows t_numrows,to_char(t.last_analyzed,'ddmmyyyy hh24:mi:ss') t_last_analyzed,p.num_rows P_numrows,to_char(p.last_analyzed,'ddmmyyyy hh24:mi:ss') p_last_analyzed
select t.owner,t.table_name,p.partition_name,trunc(s.bytes/1024/1024) MB,s.blocks,p.num_rows P_numrows,to_char(p.last_analyzed,'ddmmyyyy hh24:mi:ss') p_last_analyzed
from dba_tables t, dba_tab_partitions p, dba_segments s
where t.owner = p.table_owner
and t.table_name = p.table_name
and s.owner = p.table_owner
and s.segment_name = p.table_name
and s.partition_name = p.partition_name
and t.owner like upper('&1')
and t.table_name like upper('&2')
order by t.owner,t.table_name,p.partition_position;

SET FEED OFF
set HEADING off
SELECT '---------SUBPARTICION--------' FROM DUAL;
SET FEED ON
set HEADING on

select t.owner,t.table_name,p.subpartition_name,t.num_rows t_numrows,to_char(t.last_analyzed,'ddmmyyyy hh24:mi:ss') t_last_analyzed,p.num_rows P_numrows,to_char(p.last_analyzed,'ddmmyyyy hh24:mi:ss') p_last_analyzed
from dba_tables t, dba_tab_subpartitions p
where t.owner = p.table_owner
and t.table_name = p.table_name
and t.owner like upper('&1')
and t.table_name like upper('&2')
order by t.owner,t.table_name,p.SUBPARTITION_name;