CDB_TABLESPACE_USAGE_METRICS Not Showing Proper Tablespace Usage (Doc ID 2646484.1)

Last updated on APRIL 30, 2020

Applies to:

Symptoms

• View CDB_TABLESPACE_USAGE_METRICS not showing proper tablespace usage details:
  SYS:PCBE0013>sho con_name

  CON_NAME
  ------------------------------
  CDB$ROOT
  SYS:PCBE0013>sho pdbs

  CON_ID CON_NAME OPEN MODE RESTRICTED
  ---------- ------------------------------ ---------- ----------
  2 PDB$SEED READ ONLY NO
  3 FDXSEED READ WRITE NO
  4 PCBE001_PDB READ WRITE NO
  SYS:PCBE0013>set lines 200
  SYS:PCBE0013>col name for a20
  SYS:PCBE0013>select C.NAME,tablespace_name,ROUND((T.used_space*8192)/(1024*1024*1024),1) USED_GB,
  2 ROUND((T.tablespace_size*8192)/(1024*1024*1024),1) TBS_SIZE_GB,
  3 round(T.used_percent,1) USED_PERCENT
  4 from CDB_tablespace_usage_metrics T,V$CONTAINERS C where T.con_id=C.con_id AND T.used_percent>=80;

  NAME TABLESPACE_NAME USED_GB TBS_SIZE_GB USED_PERCENT
  -------------------- ------------------------------ ---------- ----------- ------------
  CDB$ROOT USERS 9.9 10.6 93.9

  SYS:PCBE0013>--But actual size 500mb only---

  SYS:PCBE0013>col FILE_NAME for a60;
  set linesize 132;
  select FILE_NAME,BYTES/1024/1024,MAXBYTES/1024/1024,USER_BYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tbs';
  SYS:PCBE0013>SYS:PCBE0013>Enter value for tbs: USERS
  old 1: select FILE_NAME,BYTES/1024/1024,MAXBYTES/1024/1024,USER_BYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tbs'
  new 1: select FILE_NAME,BYTES/1024/1024,MAXBYTES/1024/1024,USER_BYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='USERS'

  FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
  ------------------------------------------------------------ --------------- ------------------ -------------------- ---
  +DATA/PCBE001/DATAFILE/users.335.1025910019 100 500 99 YES

   

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.