CREATE TABLE tl_contig_space (
tablespacE_name CHAR(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace system
storage (initial 64K next 64K pctincrease 0);
 
 
 
CREATE VIEW vw_new_look AS
SELECT tablespace_name,
starting_file_id,
starting_block_id,
SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks,
MAX(blocks) max_blocks,
SUM(bytes) sum_bytes
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    INTEGER;
old_block_id   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);
          COMMIT;
      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;
          COMMIT;
      END IF;
      previous_row := this_row;
      FETCH query INTO this_row;
   END LOOP;
END;
/