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 /
Procedure created.
Elapsed: 00:00:00.16
SQL> exec show_space('bbed','auto');
BEGIN show_space('bbed','auto'); END;
*
ERROR at line 1:
ORA-18008: cannot find OUTLN schema
ORA-06512: at "SYS.STANDARD", line 264
ORA-06512: at line 1
Elapsed: 00:00:00.02
SQL> exec show_space('bbed','auto','T','Y');
BEGIN show_space('bbed','auto','T','Y'); END;
*
ERROR at line 1:
ORA-18008: cannot find OUTLN schema
ORA-06512: at "SYS.STANDARD", line 264
ORA-06512: at line 1
Elapsed: 00:00:00.01
SQL>
上述情况主要是有人删除了outln用户造成的,呵呵,重建outln用户即可。