Friday, February 24, 2012

Check which segment resides in the last block of each datafile

SELECT de.file_id, owner, segment_name, partition_name, segment_type
  FROM dba_extents de,
      (SELECT file_id, MAX(block_id) mblock_id
         FROM dba_extents
       WHERE tablespace_name = 'DATA01'
       GROUP BY file_id
      ) fmax
WHERE de.file_id = fmax.file_id AND de.block_id = fmax.mblock_id 
ORDER BY file_id;

No comments: