statement |
select lower(c.name) database
, a.owner owner
, a.table_name table_name
, round((a.num_rows * a.avg_row_len) / (1024*1024),2) estimated_Mb
, round((a.blocks*value/(1024*1024)),2) current_MB
, a.blocks blocks
, a.num_rows num_rows
, to_char(a.last_analyzed,'yyyy-mon-dd') last_analyzed
from dba_tables a
, v$parameter b
, v$database c
where a.last_analyzed is not null
and b.name='db_block_size'
and a.owner <> 'SYS'
and a.blocks >= 10
and 100*round( (a.num_rows*a.avg_row_len)/(decode(a.blocks,0,1,a.blocks)*value),2) < 20
and a.table_name not in ( select table_name
from dba_tab_columns
where ( data_type like '%LOB%' or data_type like '%LONG%' )
and owner= a.owner
and table_name = a.table_name )
|