使用show_space时的一类错误现象

最新推荐文章于 2024-01-25 11:50:48 发布

原创最新推荐文章于 2024-01-25 11:50:48 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#schema #sql #table #ext #file #user

oracle 专栏收录该内容

137 篇文章

订阅专栏

本文介绍了一个用于Oracle数据库的空间管理过程show_space，该过程能够显示指定段的空间使用情况，包括总块数、未使用块数等，并能根据不同参数展示更多详细信息。

SQL> create or replace procedure show_space
2 ( p_segname_1 in varchar2,
3 p_space in varchar2 default 'MANUAL',
4 p_type_1 in varchar2 default 'TABLE' ,
5 p_analyzed in varchar2 default 'N',
6 p_owner_1 in varchar2 default user)
7 as
p_segname varchar2(100);
8    9 p_type varchar2(10);
10 p_owner varchar2(30);
11
12 l_unformatted_blocks number;
13 l_unformatted_bytes number;
l_fs1_blocks number;
14   15 l_fs1_bytes number;
16 l_fs2_blocks number;
17 l_fs2_bytes number;
l_fs3_blocks number;
18   19 l_fs3_bytes number;
20 l_fs4_blocks number;
21 l_fs4_bytes number;
22 l_full_blocks number;
23 l_full_bytes number;
24
l_free_blks number;
25   26 l_total_blocks number;
27 l_total_bytes number;
28 l_unused_blocks number;
29 l_unused_bytes number;
30 l_LastUsedExtFileId number;
31 l_LastUsedExtBlockId number;
32 l_LAST_USED_BLOCK number;

33   34 procedure p( p_label in varchar2, p_num in number )
35 is
begin
36   37 dbms_output.put_line( rpad(p_label,40,'.') ||
38 p_num );
39 end;
40 begin
41 p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
42   43 p_type := p_type_1;
44
45 if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
46   47 end if;
48
49 if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
50   51 end if;
52
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
53   54 p_type := 'CLUSTER';
55 end if;

56   57
58 dbms_space.unused_space
59 ( segment_owner => p_owner,
segment_name => p_segname,
60   61 segment_type => p_type,
62 total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
63   64 unused_blocks => l_unused_blocks,
65 unused_bytes => l_unused_bytes,
66 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
67   68 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
69
70 if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
71   72 ( segment_owner => p_owner,
73 segment_name => p_segname,
74 segment_type => p_type,
75 freelist_group_id => 0,
76 free_blks => l_free_blks );
77
78 p( 'Free Blocks', l_free_blks );
79 end if;
80
81 p( 'Total Blocks', l_total_blocks );
82 p( 'Total Bytes', l_total_bytes );
83 p( 'Unused Blocks', l_unused_blocks );
84 p( 'Unused Bytes', l_unused_bytes );
85 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
86 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
87 p( 'Last Used Block', l_LAST_USED_BLOCK );
88
89
90 /*IF the segment is analyzed */
91 if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
92   93 segment_name => p_segname ,
segment_type => p_type ,
94   95 unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
96   97 fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
98   99 fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
100 101 fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
102 103 fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
104 105 full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
106 107 dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
108 109 p( '0% -- 25% free space blocks', l_fs1_blocks);
110 p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
111 112 p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
113 114 p( '50% -- 75% free space bytes', l_fs3_bytes);
115 p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
116 117 p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
118 119 p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);
120 121
end if;
122 123
124 end;
125 /