-
Dear friends,
I had a anoymous procedure for checking tablespace usage,
it can be run without problem. When I conver it to a store procedure, it have compilation error. Can anyone help me??
the anonymous procedure as follow (run on ora 8.1.6):
declare
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;
begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;
The store proc (with error) as follow:
create procedure dsp_space
AS
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;
begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;
/
kelvin
-
-
replace as by is
hi leungcwk
try it now it will be work
execute dsp_space
note : I used oracle8i 8.1.7
-----------------------------------------------------------------------
create or replace procedure dsp_space
is
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;
begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;
/
-----------------------------------------------------------------------
If There Is somthing to do it just do it with oracle ....
-
Re: replace as by is
Thanks Mohammed.
However, I still get compilation errors.
There seems some problem on cursor declaration.
If you have any idea to fix the error, please feed back
to me. thanks
SQL> l
1 create or replace procedure dsp_space
2 is
3 name char(20);
4 mbytes char(20);
5 used char(20);
6 free char(20);
7 pct_used char(20);
8 cursor test_cur is
9 select a.tablespace_name name,
10 sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
11 1048576 mbytes, round(((sum(b.bytes)/
12 count( distinct a.file_id||'.'||a.block_id ) -
13 sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
14 round((sum(a.bytes)/count( distinct b.file_id ) /
15 1048576),2) free,
16 round(100 * ( (sum(b.bytes)/
17 count( distinct a.file_id||'.'||a.block_id ))
18 -(sum(a.bytes)/count( distinct b.file_id ) )) /
19 (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
20 pct_used
21 from sys.dba_free_space a, sys.dba_data_files b
22 where a.tablespace_name = b.tablespace_name
23 group by a.tablespace_name, b.tablespace_name;
24 test_val test_cur%rowtype;
25 begin
26 open test_cur;
27 dbms_output.put_line('Table Space ' ||' '||
28 'mbytes ' ||' '||
29 'used ' ||' '||
30 'free ' ||' '||
31 'pct_used ');
32 dbms_output.put_line('-----------------------------------------------------
---------------------------------------------');
33 loop
34 fetch test_cur into test_val;
35 exit when test_cur%notfound;
36 name := test_val.name;
37 mbytes := test_val.mbytes;
38 used := test_val.used;
39 free := test_val.free;
40 pct_used := test_val.pct_used;
41 dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '||
pct_used);
42 end loop;
43 close test_cur;
44* end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE DSP_SPACE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/8 PLS-00341: declaration of cursor 'TEST_CUR' is incomplete or
malformed
9/1 PL/SQL: SQL Statement ignored
21/28 PLS-00201: identifier 'SYS.DBA_DATA_FILES' must be declared
24/10 PL/SQL: Item ignored
34/1 PL/SQL: SQL Statement ignored
34/21 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
36/1 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
36/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
37/1 PL/SQL: Statement ignored
37/11 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
38/1 PL/SQL: Statement ignored
38/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
LINE/COL ERROR
-------- -----------------------------------------------------------------
39/1 PL/SQL: Statement ignored
39/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
40/1 PL/SQL: Statement ignored
40/13 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
-----------------------------------------------------------------------
[/B][/QUOTE]
[Edited by leungcwk on 03-19-2002 at 11:14 PM]
kelvin
-
Ans : Convert anonymous to stored procedure
create or replace procedure dsp_space
is
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) / 1048576 mbytes, round(((sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id )
- sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) / 1048576),2) free,
round(100 * ( (sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
begin
dbms_output.put_line('Table Space ' ||' '|| 'mbytes ' ||' '|| 'used ' ||' '|| 'free ' ||' '|| 'pct_used ');
FOR test_rec in test_cur Loop
name := test_rec.name;
mbytes := test_rec.mbytes;
used := test_rec.used;
free := test_rec.free;
pct_used := test_rec.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
End Loop;
end;
/
-
the problem has nothing to do with this code. it is with sys tables.
first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.
connect sys/password
grant select on dba_free_space, dba_data_files to user;
and then compile your procedure.
- you cannot use sys tables with your schema procedure if you don't have priviledges to use them.
F.
-
Originally posted by Mnemonical
the problem has nothing to do with this code. it is with sys tables.
first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.
connect sys/password
grant select on dba_free_space, dba_data_files to user;
and then compile your procedure.
- you cannot use sys tables with your schema procedure if you don't have priviledges to use them.
F.
Thanks Mnemonical,
This is the solution! problem fixed, thank you.
Regards,
Kelvin
kelvin
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|