CREATE TABLE t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
 
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
 
DECLARE
 CURSOR query IS
 SELECT *
 FROM dba_free_space
 ORDER BY tablespace_name, file_id, block_id;
 
 this_row     query%ROWTYPE;
 previous_row query%ROWTYPE;
 old_file_id  PLS_INTEGER;
 old_block_id PLS_INTEGER;
BEGIN
  OPEN query;
  FETCH query INTO this_row;
  previous_row := this_row;
  old_file_id := previous_row.file_id;
  old_block_id := previous_row.block_id;
 
  WHILE query%FOUND LOOP
    IF this_row.file_id = previous_row.file_id AND
      this_row.block_id = previous_row.block_id+previous_row.blocks
    THEN
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (previous_row.tablespace_name, previous_row.file_id,
       this_row.block_id, old_file_id, old_block_id, this_row.blocks,
       this_row.bytes);
    ELSE
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (this_row.tablespace_name, this_row.file_id,
       this_row.block_id, this_row.file_id, this_row.block_id,
       this_row.blocks, this_row.bytes);
 
      old_file_id := this_row.file_id;
      old_block_id := this_row.block_id;
    END IF;
    previous_row := this_row;
    FETCH query INTO this_row;
  END LOOP;
  COMMIT;
END;
/
 
col tablespace_name format a20
col sum_mb format 999.999
 
SELECT * FROM v_contig_space;