You can use this script to check tablespace space details in 12c Multitenant database. It will provide information of both root CDB and PDB.

SET LINES 132 PAGES 100
COL con_name FORM A15 HEAD "Container|Name"
COL tablespace_name FORM A15
COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg."
--
COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
--
WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
FROM cdb_free_space cf1
,v$containers c1
WHERE cf1.con_id = c1.con_id
GROUP BY c1.con_id, cf1.tablespace_name),
y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm
FROM cdb_data_files cd
,v$containers c2
WHERE cd.con_id = c2.con_id
GROUP BY c2.con_id
,cd.tablespace_name)
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm
FROM x, y, v$containers v
WHERE x.con_id = y.con_id
AND x.tablespace_name = y.tablespace_name
AND v.con_id = y.con_id
UNION
SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name, tf.tablespace_name
ORDER BY 1, 2;

OUTPUT

Container Free Alloc
CON_ID Name TABLESPACE_NAME Space Meg. Space Meg.
---------- --------------- --------------- ---------------- ----------------
1 CDB$ROOT CHARSETCONVERSI 199 200
ON

PRODUCING 99 100
SYSAUX 382 920
SYSTEM 3 810
TEMP 197
TEST_ENCRY 2,047 2,048
UNDOTBS1 360 405
USERS 4 5
5 PROD_MN SYSAUX 253 570
SYSTEM 1 270
TEMP 20
********** *************** *************** ---------------- ----------------
sum 3,347 5,545

11 rows selected.

 

 

1.2  更多详细信息如下:

 

sqlplus 格式化输出,方便查看表空间信息,
SQL> set linesize 999
SQL> set feed off
SQL> column tbspce format A30
SQL> col container for a30

执行如下sql:
SQL>
select substr(f.tablespace_name,1,30) tbspce,
round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)",
round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)",
round((nvl(s.segbytes,0)/f.tsbytes)*100,2) "use_%",
round((nvl(s.segbytes,0)/f.txmaxbytes)*100,2) "maxuse_%",
lower(vc.name) as container
from
(select con_id,tablespace_name,sum(bytes) tsbytes,sum(MAXBYTES) txmaxbytes from cdb_data_files group by con_id,tablespace_name) f,
(select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
v$containers vc
where f.con_id=s.con_id(+)
and f.tablespace_name=s.tablespace_name(+)
and f.con_id=vc.con_id
order by container, tbspce;

 

 

2.https://www.thegeekdiary.com/script-to-get-tablespace-utilization-in-oracle-database-12c/

Script To Get Tablespace Utilization In Oracle Database 12c

 

This is a script to get the tablespace utilization in Oracle Database 12c. You can use this script to get the tablespace utilization ALLOCATED(MB), USED(MB) and Used parentage for all containers tablespaces.

sqlplus -s / as sysdba

set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30
select substr(f.tablespace_name,1,30) tbspce,
     round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)",
     round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)",
     round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT,
     lower(vc.name) as container
from
   (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f,
   (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
   v$containers vc
where f.con_id=s.con_id(+)
  and f.tablespace_name=s.tablespace_name(+)
  and f.con_id=vc.con_id
order by container, tbspce;


3.https://orahow.com/tablespace-utilization-in-oracle/

Tablespace Utilization In Oracle Multitenant Database

 

You must be familiar with checking tablespace utilization in Oracle 11g, here we will discuss about monitoring tablespace in CDB and PDB.

Oracle introduced multitenant architecture from Oracle 12c and in other higher versions like 19c which contains container and pluggable databases CDB and PDB.

 

There are some new views introduced in 12c multitenant architecture to check size of datafiles and tempfiles tablespace used in CDB and PDB in Oracle database. We have used these views in below below script for monitoring tablespace usage in Oracle.

  • cdb_data_files
  • cdb_segments
  • v$containers

Script to Monitor Tablespace Utilization in Oracle 12c, 19c and other higher versions.

 
set pages 80
 set lin 120
 set echo off
 set feed off
 column PCT format 999.99
 column tablespace format A30
 col container for a30
 select substr(t.tablespace_name,1,30) tablespace,
      round(t.tsbytes/(10241024),0) "ALLOCATED(MB)",      round(nvl(s.segbytes,0)/(10241024),0) "USED(MB)",
      round((nvl(s.segbytes,0)/t.tsbytes)*100,2) PCT,
      lower(vc.name) as container
 from
    (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) t,
    (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
    v$containers vc
 where t.con_id=s.con_id(+)
   and t.tablespace_name=s.tablespace_name(+)
   and t.con_id=vc.con_id
 order by container, tablespace;

TABLESPACE                     ALLOCATED(MB)   USED(MB)     PCT CONTAINER
 -------------                           -------           ------     --------
 SYSAUX                                  1630       1268   77.78 cdb$root
 SYSTEM                                  1250       1244   99.55 cdb$root
 UNDOTBS1                                 545         17    3.18 cdb$root
 USERS                                      8          6   78.33 cdb$root
 APPS_TS_ARCHIVE                         5986        489    8.17 tst31ut
 APPS_TS_INTERFACE                       1041        431   41.43 tst31ut
 APPS_TS_MEDIA                          40394      38294   94.80 tst31ut
 

Check Tablespace Usage Percentage in Oracle PDB and CDB Database.

 
set line 200 pages 999
 column name for a10
 column tablespace_name for a15
 column "MAXSIZE (MB)" format 9,999,990.00
 column "ALLOC (MB)" format 9,999,990.00
 column "USED (MB)" format 9,999,990.00
 column "PERC_USED" format 99.00
 select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(10241024) "MAXSIZE (MB)", nvl(a.physical_bytes,0)/(10241024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(10241024) "USED (MB)" ,(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;
CON_ID NAME       TABLESPACE_NAME  MAXSIZE (MB)    ALLOC (MB)     USED (MB) PERC_USED
---------------  ------------   -----------   ----------------   --------------
1 CDB$ROOT   SYSAUX              32,767.98      1,630.00      1,274.00      3.89
          1 CDB$ROOT   SYSTEM              32,767.98      1,250.00      1,244.38      3.80
          1 CDB$ROOT   UNDOTBS1            32,767.98        545.00         17.25       .05
          1 CDB$ROOT   USERS               32,767.98          7.50          5.88       .02
          3 tst31ut    APPS_TS_ARCHIVE     21,346.00      5,986.00        489.25      2.29
          3 tst31ut    APPS_TS_INTERFA      1,041.00      1,041.00        431.25     41.43

To Check how much Space Used in Oracle

 
select sum(BYTES)/1024/1024/1024 from cdb_segments;
SUM(BYTES)/1024/1024/1024
-----------------
310.454269  

select CON_ID,sum(BYTES)/1024/1024/1024 from cdb_segments group by CON_ID;
CON_ID SUM(BYTES)/1024/1024/1024
-------    --------------
1                2.47595215    -CDB Size
 3                307.978317    -PDB Size
 

To Check Datafile Size in Oracle

 col FILE_NAME for a55
 set lines 200
 select FILE_NAME,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from cdb_data_files where TABLESPACE_NAME='APPS_TS_MEDIA';
 FILE_NAME                                               BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024 AUT
 
 /scratch/u01/E-BIZ/db/apps_st/data/a_media01.dbf                  1.41210938                       0 NO
 /scratch/u01/E-BIZ/db/apps_st/data/a_media03.dbf                  18.8696289                      20 YES
 /scratch/u01/E-BIZ/db/apps_st/data/a_media02.dbf                  19.1652832                      30 YES
 

Conclusion: Above script is used to check tablespace details in Oracle including tablespace size, freespace, percentage used etc.

 

 

 

 

 #############sample 2

强制打开所有pdb 数据库的触发器

create or replace trigger sys.open_all_pdbs after startup on database begin execute immediate 'alter pluggable database all open'; end; /