How to check tablespace usage on oracle 12.2 database.

Solution:

Use below query to get all tablespace usage on oracle 12.2 database.

col used for 99,999,999

col total for 999,999,999

col percent for 999

col tablespace_name for a30

select tablespace_name, 

sum(used) used, sum(total) total, round(100*sum(used)/sum(total),0) percent

from 

(select tablespace_name

,round(sum(bytes)/1024/1024,0) used, 0 total

from dba_segments 

group by tablespace_name

union

select tablespace_name

,0 used

,round(sum(bytes)/1024/1024,0) total

from dba_data_files 

group by tablespace_name)

group by tablespace_name

order by tablespace_name

/

Output:

TABLESPACE_NAME                       USED        TOTAL PERCENT

------------------------------ ----------- ------------ -------

AUDIT_USER_DATA                          0          500       0

AUDIT_USER_INDX                          0          200       0

TEST_DATA                          222,437      245,760      91

TEST_INDX                                0          100       0

ABC_DATA                             1,584        3,072      52

ABC_INDX                                 0        1,024       0