René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Generating a map of the Extents in a Tablespace with PL/SQL

The following PL/SQL Package can be used to generate a map of extents in a Tablespace. It must be called like so in sql*plus: exec tablespace_map.create_map('<utl_file_dir>','<file_name>','<tablespace_name>');. The procedure will then create a file in the directory <utl_file_dir> named <file_name> with a map of <tablespace_name>. Make sure that <utl_file_dir> correspondends to a valid directory and the init parameter utl_file_dir is accordingly set.
create or replace package tablespace_map as

  procedure create_map (
    p_path       in varchar2, -- must correspond to utl_file_dir init param
    p_filename   in varchar2, -- the name of the file within v_path to create
    p_tablespace in varchar2  -- the tablespace of which the map is created
  );

  function get_identifier(v_name varchar2) return varchar2;

  procedure w(v varchar2);
  procedure wnl;

  procedure write_block(p_block varchar2);

  procedure fill_emtpy_blocks(p_through_block number);

  procedure print_legend;

  ofh utl_file.file_type;
  type r_assoc is record (k varchar2(30), v varchar2(3));
  type t_assoc is table of r_assoc;

  ts_id_assocs t_assoc;
  width        number(3) := 120;

  v_cols       number;
  v_cur_block  number := 0;
  v_last_block number := 0;

end tablespace_map;
/


create or replace package body tablespace_map as

  procedure create_map (
    p_path       in varchar2,
    p_filename   in varchar2,
    p_tablespace in varchar2
  ) is
    v_segment_name      varchar2(30);
    v_segment_type      varchar2(30);
    v_filename          varchar2(513);
    v_oldfilename       varchar2(513) := '?';
    v_block_id          number;
    v_blocks            number;
    v_id                varchar2(3);
    v_total_blocks      number:=0;
    v_last_total_blocks number := 0;
    cursor cur (v_ts varchar2)
    is select   s.segment_name, s.segment_type, /*to_char(s.file_id)*/ f.name, s.block_id, s.blocks, f.blocks
       from     sys.dba_extents s
                ,sys.v_$datafile f
       where    f.file# = s.file_id and
                s.tablespace_name = v_ts
       order by s.file_id, s.block_id;
  begin
    ts_id_assocs := t_assoc();
    ts_id_assocs.delete();

    ofh := utl_file.fopen(p_path,p_filename,'W');
    v_cols := width / 4;

    open cur(p_tablespace);
    loop
      fetch cur into v_segment_name, v_segment_type, v_filename, v_block_id,v_blocks, v_total_blocks;

      if cur%notfound then
        fill_emtpy_blocks(v_last_total_blocks);
        exit;
      end if;


      if v_oldfilename <> v_filename then
        if v_oldfilename <> '?' then
          fill_emtpy_blocks(v_last_total_blocks);
        end if;

        v_oldfilename := v_filename;

        v_cur_block := 0;
        v_last_block:=0;
        wnl;
        wnl;
        w('------------------------ File: ' || v_filename);
        wnl;
      end if;

      v_last_total_blocks := v_total_blocks;

      v_id := get_identifier(v_segment_name); 

      if v_last_block + 1 < v_block_id then
        fill_emtpy_blocks (v_block_id-1);
      end if;

      write_block(' ' || v_id);

      for i in 2 .. v_blocks loop
        write_block('...,');
      end loop;
      
    end loop;

    wnl;
    wnl;
    print_legend;

    utl_file.fclose(ofh);
  end;

  procedure write_block(p_block varchar2) is
  begin
    if mod(v_cur_block, v_cols) = 0 then
      wnl;
      w ('Block ' || to_char(v_cur_block,'00000') || ' ');
    end if;

    v_cur_block  := v_cur_block+1;
    w (p_block);
    v_last_block := v_cur_block;
  end;

  function get_identifier(v_name varchar2) return varchar2 is
    i number;
    r r_assoc;
  begin
    i := ts_id_assocs.first();
    while i is not null loop
      r := ts_id_assocs(i);
      if r.k = v_name then
        return r.v;
      end if;
      i := ts_id_assocs.next(i);
    end loop;
    ts_id_assocs.extend(1);
    i:=ts_id_assocs.last();
    r.k := v_name;
    r.v := to_char(i,'FM000');
    ts_id_assocs(i) := r;
  
    return r.v;
  end;

  procedure wnl is
  begin
    utl_file.new_line(ofh);
  end;

  procedure w(v varchar2) is
  begin
    utl_file.put(ofh,v);    
  end;

  procedure fill_emtpy_blocks (p_through_block number) is
  begin
    for i in v_cur_block .. p_through_block loop
      write_block ('   ,');
    end loop;
  end;

  procedure print_legend is
  i number;
  r r_assoc;
  begin
    i := ts_id_assocs.first();
    while i is not null loop
      r := ts_id_assocs(i);
      w(r.v || ' = ' || r.k);
      wnl;
      i := ts_id_assocs.next(i);
    end loop;
  end;
end;
/