Oracle 识别数据文件中的自由空间间隙

SQL> SELECT   owner,
  2           segment_name,
  3           file_id,
  4           block_id,
  5           trunc(block_id*bs/1024)||'k' starting_pos,
  6           case
  7              when blocks*bs < 1024*1024 then trunc(blocks*bs/1024)||'k'
  8              else trunc(blocks*bs/1024/1024)||'m'
  9           end chunk_size
 10      FROM dba_extents,
 11           ( select block_size bs from dba_tablespaces where tablespace_name = 'USERS')
 12     WHERE tablespace_name = 'USERS'
 13  UNION ALL
 14  SELECT   '',
 15           ' ',
 16           file_id,
 17           block_id,
 18           trunc(block_id*bs/1024)||'k' starting_pos,
 19           case
 20              when blocks*bs < 1024*1024 then trunc(blocks*bs/1024)||'k'
 21              else trunc(blocks*bs/1024/1024)||'m'
 22           end chunk_size
 23      FROM dba_free_space,
 24           ( select block_size bs from dba_tablespaces where tablespace_name = 'USERS')
 25     WHERE tablespace_name = 'USERS'
 26  ORDER BY file_id, block_id;

OWNER                          SEGMENT_NAME                      FILE_ID STARTING_POS CHUNK_SIZE
------------------------------ ------------------------------ ---------- ------------ ------------
SCOTT                          EMP                                     7 1024k        64k
SCOTT                          DEPT                                    7 1088k        64k
SCOTT                          SALGRADE                                7 1152k        64k
SCOTT                          EMP_PK                                  7 1216k        64k
SCOTT                          DEPT_PK                                 7 1280k        64k
                                                                 7 1344k        64k
MCDONAC                        PRODUCT                                 7 1408k        64k
MCDONAC                        EXT                                     7 1472k        64k
MCDONAC                        CUSTOMER                                7 1536k        64k
                                                                 7 1600k        64k
MCDONAC                        EXTERNAL_EMP                            7 1664k        64k
MCDONAC                        T                                       7 1728k        64k
...
...
复制