18) # "MATERIALIZEDS VIEWS DISABLEDS"

18) # "MATERIALIZEDS VIEWS DISABLEDS"

echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} MATERIALIZEDS VIEWS DISABLEDS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col owner for a20
col type for a20
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;
quit;
EOF