【SQL】Check the tablesapce usage in PDB and CDB

 --- check out the tbs's usage in PDB and CDB , run in CDB

set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (GB)" format 9,999,990.00
column "ALLOC (GB)" format 9,999,990.00
column "USED (GB)" format 9,999,990.00
column "PERC_USED" format 99.00
select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/power(2,30) "MAXSIZE (GB)", nvl(a.physical_bytes,0)/power(2,30) "ALLOC (GB)" ,nvl(b.tot_used,0)/power(2,30) "USED (GB)" ,(nvl(b.tot_used,0)/a.bytes_alloc)100 "PERC_USED"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_data_files group by con_id,tablespace_name ) a,
(select con_id,tablespace_name, sum(bytes) tot_used from cdb_segments group by con_id,tablespace_name ) b,
(select name,con_id from v$containers) c
where a.con_id= b.con_id and a.con_id = c.con_id and a.tablespace_name = b.tablespace_name (+)
order by 1,3;