我写一个存储过程来打印,当前用户下所有的表占用的大小以G为单位的。编译的时候 from dba_segments这行出错,我在程序窗口中运行select * from dba_segments,正常的。请问是怎么回事?以下是代码
create or replace procedure pro_count is
cursor cu_tname is
select table_name from user_tables;
v_tname varchar2(50);
c number;
begin
open cu_tname;
loop
fetch cu_tname
into v_tname;
exit when cu_tname%notfound;
select bytes/1024/1024
into c
from dba_segments
where segment_name = v_tname;
dbms_output.put_line(c);
end loop;
close cu_tname;
end;
create or replace procedure pro_count is
cursor cu_tname is
select table_name from user_tables;
v_tname varchar2(50);
c number;
begin
open cu_tname;
loop
fetch cu_tname
into v_tname;
exit when cu_tname%notfound;
select bytes/1024/1024
into c
from dba_segments
where segment_name = v_tname;
dbms_output.put_line(c);
end loop;
close cu_tname;
end;
因为多个用户下可能存在重名的对象,会导致错误。