segment type on tablespace

segment type on tablespace
=============================================================================


-- segment_type_per_tbs.sql

set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from dba_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by owner, tablespace_name, segment_type
order by owner, tablespace_name, segment_type;

===================================
--- all the cdb 
set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select con_id,owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from cdb_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by  con_id,owner, tablespace_name, segment_type
order by con_id,owner, tablespace_name, segment_type;