其中SYSTEM为需要查找的表空间名称，需要替换为实际的表空间名称。http://www.cndba.cn/hbhe0316/article/106595http://www.cndba.cn/hbhe0316/article/106595

SQL> col file_name for a70
SQL> SELECT *
      FROM (SELECT /*+ ordered use_hash(a,b,c) */
             a.file_id,
             a.file_name,
             a.filesize,
             b.freesize,
             (a.filesize - b.freesize) usedsize,
             c.hwmsize,
             c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
            a.filesize - c.hwmsize canshrinksize
             FROM (SELECT file_id,
                          file_name,
                          round(bytes / 1024 / 1024) filesize
                     FROM dba_data_files) a,
                  (SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize
                     FROM dba_free_space dfs
                    GROUP BY file_id) b,
                  (SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize
                     FROM dba_extents
                    GROUP BY file_id) c
            WHERE a.file_id = b.file_id
              AND a.file_id = c.file_id
            ORDER BY unsedsize_belowhwm DESC)
    WHERE file_id IN (SELECT file_id
                        FROM dba_data_files
                       WHERE tablespace_name = 'SYSTEM')
ORDER BY file_id;

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------
  FILESIZE   FREESIZE   USEDSIZE    HWMSIZE UNSEDSIZE_BELOWHWM CANSHRINKSIZE
---------- ---------- ---------- ---------- ------------------ -------------
         1
/u01/app/oracle/oradata/ORCL/system01.dbf
      2580        533       2047       2039                 -8           541

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle