create or replace procedure show_space(p_segname_1 in varchar2,
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y') as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p(p_label in varchar2, p_num in number) is
begin
dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then
--rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then
--rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then
--rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK);
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p('Free Blocks', l_free_blks);
end if;
p('Total Blocks', l_total_blocks);
p('Total Bytes', l_total_bytes);
p('Unused Blocks', l_unused_blocks);
p('Unused Bytes', l_unused_bytes);
p('Last Used Ext FileId', l_LastUsedExtFileId);
p('Last Used Ext BlockId', l_LastUsedExtBlockId);
p('Last Used Block', l_LAST_USED_BLOCK);
/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ', 50, '*'));
dbms_output.put_line('The segment is analyzed');
p('0% -- 25% free space blocks', l_fs1_blocks);
p('0% -- 25% free space bytes', l_fs1_bytes);
p('25% -- 50% free space blocks', l_fs2_blocks);
p('25% -- 50% free space bytes', l_fs2_bytes);
p('50% -- 75% free space blocks', l_fs3_blocks);
p('50% -- 75% free space bytes', l_fs3_bytes);
p('75% -- 100% free space blocks', l_fs4_blocks);
p('75% -- 100% free space bytes', l_fs4_bytes);
p('Unused Blocks', l_unformatted_blocks);
p('Unused Bytes', l_unformatted_bytes);
p('Total Blocks', l_full_blocks);
p('Total bytes', l_full_bytes);
end if;
end;
/
用法 SQL > create table t as select * from dba_objects;
Table created.
SQL > set serverout on
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 696
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .5701632
PL
/
SQL procedure successfully completed.
SQL > delete from t;
50602 rows deleted.
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........696
75% -- 100% free space bytes............5701632
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0
PL
/
SQL procedure successfully completed.
SQL > alter table t move;
Table altered.
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 8
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .65536
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .5
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 40960
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .401
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .3
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0
PL
/
SQL procedure successfully completed
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y') as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p(p_label in varchar2, p_num in number) is
begin
dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then
--rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then
--rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then
--rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK);
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p('Free Blocks', l_free_blks);
end if;
p('Total Blocks', l_total_blocks);
p('Total Bytes', l_total_bytes);
p('Unused Blocks', l_unused_blocks);
p('Unused Bytes', l_unused_bytes);
p('Last Used Ext FileId', l_LastUsedExtFileId);
p('Last Used Ext BlockId', l_LastUsedExtBlockId);
p('Last Used Block', l_LAST_USED_BLOCK);
/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ', 50, '*'));
dbms_output.put_line('The segment is analyzed');
p('0% -- 25% free space blocks', l_fs1_blocks);
p('0% -- 25% free space bytes', l_fs1_bytes);
p('25% -- 50% free space blocks', l_fs2_blocks);
p('25% -- 50% free space bytes', l_fs2_bytes);
p('50% -- 75% free space blocks', l_fs3_blocks);
p('50% -- 75% free space bytes', l_fs3_bytes);
p('75% -- 100% free space blocks', l_fs4_blocks);
p('75% -- 100% free space bytes', l_fs4_bytes);
p('Unused Blocks', l_unformatted_blocks);
p('Unused Bytes', l_unformatted_bytes);
p('Total Blocks', l_full_blocks);
p('Total bytes', l_full_bytes);
end if;
end;
/
用法 SQL > create table t as select * from dba_objects;
Table created.
SQL > set serverout on
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 696
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .5701632
PL
/
SQL procedure successfully completed.
SQL > delete from t;
50602 rows deleted.
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........696
75% -- 100% free space bytes............5701632
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0
PL
/
SQL procedure successfully completed.
SQL > alter table t move;
Table altered.
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 8
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .65536
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .5
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 40960
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .401
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .3
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
The segment is
analyzed 0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0
PL
/
SQL procedure successfully completed