VIEWS MATERIALIZADAS DESATUALIZADAS

set pages 200 lines 200;
set timing on;
select
    owner
    ,type
    ,tablespace_name
    ,round(sum(mb)) as mb
from (select
        owner
        ,'mview' as type
        ,tablespace_name
        ,round(sum(bytes) / 1024/1024) as mb
    from dba_segments
    where (owner,segment_name)  in (select owner, mview_name from dba_mviews)
    group by owner, segment_type, tablespace_name
union
select
    owner
    ,'mview_log' as type
    ,tablespace_name
    ,round(sum(bytes) / 1024/1024) as mb
from dba_segments
where (owner,segment_name)  in (select log_owner, log_table    from dba_snapshot_logs)
group by owner, segment_type, tablespace_name
union
select
    owner
    ,'mview_index' as type
    ,tablespace_name
    ,round(sum(bytes) / 1024/1024) as mb
from dba_segments
where (owner,segment_name)  in (select owner, index_name from dba_indexes where (owner,table_name) in (select owner, mview_name from dba_mviews))
group by owner, segment_type, tablespace_name) t1
group by owner,type,tablespace_name;