[root@redhat ~]# su - oracle
Last login: Thu May 9 20:55:09 CST 2019 on pts/0
[oracle@redhat ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 9 22:12:53 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set linesize 1000 pagesize 500
SQL> col file_name for a150
SQL> col TABLESPACE_NAME for a30
SQL> with free_space as
2 (SELECT /*+ materialize /
3 tablespace_name, file_id, SUM(BYTES) BYTES
4 FROM dba_free_space
5 --where bytes > 1024 * 1024
6 GROUP BY tablespace_name, file_id)
7 SELECT df.tablespace_name,
8 COUNT() dnt,
9 ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
10 ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
11 ROUND(SUM(df.BYTES) / 1048576 / 1024 -
12 SUM(free.BYTES) / 1048576 / 1024,
13 2) used_gb,
14 100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
15 ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free
16 FROM dba_data_files df, free_space free
17 WHERE df.tablespace_name = free.tablespace_name(+)
18 AND df.file_id = free.file_id(+)
19 --and df.tablespace_name like '%UNDOTBS%'
20 --and df.tablespace_name in ('IISS_DAT')
21 GROUP BY df.tablespace_name
22 ORDER BY pct_free;
TABLESPACE_NAME DNT SIZE_GB FREE_GB USED_GB PCT_USED PCT_FREE
SYSTEM 1 .82 0 .82 99.87 .13
SYSAUX 1 2.01 .12 1.89 94.18 5.82
UNDOTBS1 1 .09 .05 .03 38.12 61.88
USERS 1 0 0 0 20 80
SQL>
