| spool b.html
prompt <html>
prompt <embed src="C:\Documents and Settings\tdb.laiti\Bureau\b.svg" width="150%" height="150%" type="image/svg+xml" />
prompt </html>
spool off
spool b.svg
set verify off serveroutput on lines 400 trimspool on pages 0 feed off define mon_seg=FREE
define ma_couleur=green
declare
cursor c1 ( identifiant_seg varchar2, dbf_id number) is
select * FROM (
select * FROM (
select '['||decode(segment_type, 'LOBINDEX','LI', 'INDEX PARTITION','IP', 'NESTED TABLE','NT',
'ROLLBACK','R', 'TABLE PARTITION','TB', 'LOB PARTITION','LP', 'LOBSEGMENT','LS',
'TABLE','T', 'INDEX','I', 'CLUSTER','C', 'O') ||']'|| owner||'.'|| segment_name seg,
block_id, trunc(bytes/1024/1024) size_M,
(select trunc(bytes/1024/1024) from majda_segments s where e.owner=s.owner
and e.segment_name =s.segment_name
and e.segment_type = s.segment_type
and nvl(e.PARTITION_NAME,' ')=nvl(s.PARTITION_NAME,' ')) taille_seg_M, blocks
from majda_extents e
where
file_id = dbf_id
UNION
select 'FREE' seg, block_id , trunc(bytes/1024/1024) size_M ,0 taille_seg_M ,blocks from majda_free_space
where
file_id = dbf_id
)
order by block_id)
where seg=identifiant_seg;
lv_c1 c1%rowtype;
size_M varchar2(100);
lv_file_name varchar2(300);
total_G varchar2(100);
total_free_G varchar2(100);
total_occupied_M varchar2(100);
posy number;
posx number;
debut_x number:=450;
debut_y number:=60;
yfleche number;
cursor cur_dbf is
select
file_id,
file_name,
AUTOEXTENSIBLE,
tablespace_name,
trunc(bytes/1024/1024) taille_M,
decode(trunc(bytes/1024/1024),0,trunc(bytes/1024/1024)||'M',round(bytes/1024/1024/1024,1)||'G') taille_G,
decode(trunc(free_M/1024),0,free_M||'M',trunc(free_M/1024)||'G') free_G,
decode(trunc(free_tbs_M/1024),0,free_M||'M',trunc(free_tbs_M/1024)||'G') free_tbs_G,
decode(trunc(total_tbs_M/1024),0,total_tbs_M||'M',round(total_tbs_M/1024,1)||'G') total_tbs_G
FROM (
select file_id , AUTOEXTENSIBLE,
file_name,
bytes, tablespace_name,
( select trunc(sum(bytes)/1024/1024) from majda_free_space f where f.file_id=d.file_id) free_M,
( select trunc(sum(bytes)/1024/1024) from majda_free_space f where f.tablespace_name=d.tablespace_name) free_tbs_M,
( select trunc(sum(bytes)/1024/1024) from majda_data_files dd where dd.tablespace_name=d.tablespace_name) total_tbs_M
from majda_data_files d
)
order by tablespace_name, file_id; lv_cur_dbf cur_dbf%rowtype;
unite_x number;
posx1 number;
posx2 number;
old_block_id number;
premier_block_id number;
sum_blocks number;
a number;
libelle varchar2(300);
tbs varchar2(30);
old_tbs varchar2(30);
begin
dbms_output.enable(10000000000);
dbms_output.put_line('<svg xml:space="preserve" >');
-- defs
dbms_output.put_line('<defs>');
dbms_output.put_line('<path d="M 0 0 L 5 2 L 0 4 z" stroke="red" stroke-width="5" id="fleche"/>');
dbms_output.put_line('</defs>'); select trunc(sum(bytes)/1024/1024/1024) into total_G from majda_data_files;
select trunc(sum(bytes)/1024/1024/1024) into total_free_G from majda_free_space; dbms_output.put_line('<use xlink:href="#fleche" x="10" y="8"/>');
dbms_output.put_line('<line x1="10" y1="30" x2="100" y2="30" style = "stroke-width:4;stroke:green;" />');
dbms_output.put_line('<line x1="10" y1="50" x2="100" y2="50" style = "stroke-width:4;stroke:black;" />'); dbms_output.put_line('<text x="100" y="10" fill="red" font-size="12" font-family="Courier New" font-weight="bold"> : Autoextensible </text>');
dbms_output.put_line('<text x="100" y="30" fill="green" font-size="12" font-family="Courier New" font-weight="bold"> : '||total_free_G||'G Free space </text>');
dbms_output.put_line('<text x="100" y="50" fill="black" font-size="12" font-family="Courier New" font-weight="bold"> : '||total_G||'G Occupied space </text>'); select trunc(1150/(select max(bytes)/1024/1024 from dba_data_files),5) into unite_x from dual ; posy:=debut_y;
old_tbs:=' '; open cur_dbf;
loop
fetch cur_dbf into lv_cur_dbf;
exit when cur_dbf%notfound;
posy:=posy+20; tbs:=lv_cur_dbf.tablespace_name;
if old_tbs != tbs
then
libelle:=rpad(lv_cur_dbf.tablespace_name||': '||lv_cur_dbf.free_tbs_G||'/'||lv_cur_dbf.total_tbs_G,40,' ');
else
libelle:=rpad(' ',40,' ');
end if;
libelle:=libelle||'['||lpad(lv_cur_dbf.file_id,3,' ')||'] ';
libelle:=libelle||lpad(lv_cur_dbf.free_G,5,' ')||'/'||lpad(lv_cur_dbf.taille_G,5,' ');
dbms_output.put_line('<text x="10" y="'||posy||'" fill="black" font-size="12" font-family="Courier New" >'||libelle|| '</text>');
dbms_output.put_line('<circle cx="'||debut_x||'" cy="'||posy||'" r="2" />');
posx:=debut_x+trunc(lv_cur_dbf.taille_M*unite_x);
dbms_output.put_line('<circle cx="'||posx||'" cy="'||posy||'" r="2" />');
dbms_output.put_line('<line x1="'||debut_x||'" y1="'||posy||'" x2="'||posx||'" y2="'||posy||'" style = "stroke-width:1;stroke:black;" />'); if lv_cur_dbf.AUTOEXTENSIBLE='YES'
then
yfleche:=posy-2;
dbms_output.put_line('<use xlink:href="#fleche" x="'||posx||'" y="'||yfleche||'"/>');
end if;
-- additionner les blocs contigus
old_block_id:=0;
premier_block_id:=0;
sum_blocks:=0;
open c1('&mon_seg',lv_cur_dbf.file_id);
loop
fetch c1 into lv_c1;
exit when c1%notfound; -- a:=old_block_id+sum_blocks;
-- dbms_output.put_line('lv_c1.block_id='||lv_c1.block_id||' : '||lv_c1.blocks ||'<--'||a); if lv_c1.block_id = old_block_id+sum_blocks
then
sum_blocks:=sum_blocks+lv_c1.blocks;
else
posx1:=debut_x+trunc((premier_block_id-9)*8192*unite_x/1024/1024);
posx2:=posx1+trunc(sum_blocks*8192*unite_x/1024/1024);
if trunc(sum_blocks*8192*unite_x/1024/1024) > 10
then
dbms_output.put_line('<line x1="'||posx1||'" y1="'||posy||'" x2="'||posx2||'" y2="'||posy||'" style = "stroke-width:4;stroke:&ma_couleur;" />');
else
dbms_output.put_line('<circle cx="'||posx1||'" cy="'||posy||'" r="2" fill="&ma_couleur" />');
end if;
-- a:=trunc(sum_blocks*8192/1024/1024);
-- dbms_output.put_line(a||'M');
sum_blocks:=lv_c1.blocks;
premier_block_id:=lv_c1.block_id;
end if; old_block_id:=lv_c1.block_id;
end loop;
close c1; -- et le traitement du dernier :
posx1:=debut_x+trunc((premier_block_id-9)*8192*unite_x/1024/1024);
posx2:=posx1+trunc(sum_blocks*8192*unite_x/1024/1024); if trunc(sum_blocks*8192*unite_x/1024/1024) > 10
then
dbms_output.put_line('<line x1="'||posx1||'" y1="'||posy||'" x2="'||posx2||'" y2="'||posy||'" style = "stroke-width:4;stroke:&ma_couleur;" />');
else
dbms_output.put_line('<circle cx="'||posx1||'" cy="'||posy||'" r="2" fill="&ma_couleur" />');
end if;
old_tbs:=lv_cur_dbf.tablespace_name;
-- dessin de fleche pour signifier autoextensible
end loop;
close cur_dbf; dbms_output.put_line('</svg>');
end;
/
spool off |
