1、监控表空间使用率：
select a.tablespace_name,
       round((a.maxbytes / 1024 / 1024 / 1024), 2) "sum G",
       round((a.bytes / 1024 / 1024 / 1024), 2) "datafile G",
       round(((a.bytes - b.bytes) / 1024 / 1024 / 1024), 2) "used G",
       round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024 / 1024), 2) "free G",
       round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used(%)"
  from (select tablespace_name, sum(sumnow) bytes, sum(summax) maxbytes
          from (select t1.tablespace_name,
                       sum(t1.bytes) sumnow,
                       sum(t1.maxbytes) summax
                  from dba_data_files t1
                 where t1.maxbytes <> 0
                 group by t1.tablespace_name
                union all
                select t2.tablespace_name,
                       sum(t2.bytes) sumnow,
                       sum(t2.bytes) summax
                  from dba_data_files t2
                 where t2.maxbytes = 0
                 group by t2.tablespace_name)
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.maxbytes) desc;

2、查看数据文件状态
select t.tablespace_name,
       t.file_id,
       t.file_name,
       t.autoextensible,
       ROUND(t.maxbytes / 1024 / 1024 / 1024, 2) || 'G' AS MAXBYTES,
       ROUND(t.bytes / 1024 / 1024 / 1024, 2) || 'G' AS BYTES,
       ROUND(t.user_bytes / 1024 / 1024 / 1024, 2) || 'G' AS USER_BYTES
  from dba_data_files t;