dba_free_space中同一个file_id存在多条记录的问题

今日公司有一个需求，需要对使用率不到60%的数据文件进行resize，我使用下面的命令来找出数据文件：

SQL> Set pagesize 1000
SQL> col FILE_NAME for a40
SQL> col "used%" for 99.99
SQL> col "free%" for 99.99
SQL> SELECT a.FILE_ID,
  2         a.FILE_NAME,
  3         trunc(a.BYTES/1024/1024/1024,0) total_G,
  4         trunc(b.bytes/1024/1024/1024,0) free_G,
  5               'alter database datafile '''||a.file_name||''' resize '||ceil((a.BYTES - b.bytes)/1024/1024/1024) || 'G;' resizeG
  6    FROM DBA_data_files a, DBA_FREE_SPACE b
  7  where a.file_id = b.file_id
  8     and A.TABLESPACE_NAME IN (select TABLESPACE_NAME from dba_tablespaces) 
  9     and 100 * trunc(b.bytes / a.BYTES, 2)>30;

   FILE_ID FILE_NAME                                   TOTAL_G     FREE_G
---------- ---------------------------------------- ---------- ----------
RESIZEG
--------------------------------------------------------------------------------
        87 /oradata/oradb1/system02.dbf                      8          2
alter database datafile '/oradata/oradb1/system02.dbf' resize 6G;

        87 /oradata/oradb1/system02.dbf                      8          3
alter database datafile '/oradata/oradb1/system02.dbf' resize 5G;

         2 /oradata/oradb1/sysaux01.dbf                      4          1
alter database datafile '/oradata/oradb1/sysaux01.dbf' resize 3G;

        81 /oradata/oradb1/users03.dbf                       8          3
alter database datafile '/oradata/oradb1/users03.dbf' resize 5G;

       103 /oradata/oradb1/users04.dbf                       8          2
alter database datafile '/oradata/oradb1/users04.dbf' resize 6G;

       103 /oradata/oradb1/users04.dbf                       8          3
alter database datafile '/oradata/oradb1/users04.dbf' resize 5G;

发现一个奇怪的现象，同一个文件出现两次，发别查看涉及的两个

SQL> select TABLESPACE_NAME,FILE_ID from DBA_FREE_SPACE;

TABLESPACE_NAME                                                 FILE_ID
------------------------------------------------------------ ----------
SYSTEM                                                                1
SYSTEM                                                                1
UNDOTBS1                                                              2
UNDOTBS1                                                              2

SQL> select TABLESPACE_NAME,FILE_ID from DBA_data_files;

TABLESPACE_NAME                                                 FILE_ID
------------------------------------------------------------ ----------
USERS                                                                 4
SYSAUX                                                                3
UNDOTBS1                                                              2
SYSTEM                                                                1
EXAMPLE                                                               5
HUANG                                                                 6

从  DBA_FREE_SPACE 视图中可以看出，同一个file_id可能存在多天记录，原因是：dba_free_space显示的是有free 空间的tablespace ，如果一个tablespace 的free 空间不连续，那每段free空间都会在dba_free_space中存在一条记录。如果一个tablespace 有好几条记录，说明表空间存在碎片

select语句改成下面形式，同一条记录就不会出现多次了

Set pagesize 1000
col FILE_NAME for a40
col "used%" for 99.99
col "free%" for 99.99
SELECT a.FILE_ID,
       a.FILE_NAME,
       trunc(a.BYTES/1024/1024/1024,0) total_G,
       trunc(b.bytes/1024/1024/1024,0) free_G,
             'alter database datafile '''||a.file_name||''' resize '||ceil((a.BYTES - b.bytes)/1024/1024/1024) || 'G;' resizeG
  FROM DBA_data_files a, (<span style="color:#ff0000;">select file_id,sum(bytes) bytes from DBA_FREE_SPACE group by file_id</span>) b
where a.file_id = b.file_id
   and A.TABLESPACE_NAME IN (select TABLESPACE_NAME from dba_tablespaces) 
   and 100 * trunc(b.bytes / a.BYTES, 2)>30;