|
|
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;
/
|