【SQL】Check the tablesapce usage in PDB and CDB

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--- 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;