以下函数,为什么只能查出当前用户的表的记录数?
我的需求是用一个procedure计算所有Oracle用户所有表的记录数.而数据库里现在有多少用户,是动态改变的.
比如:
该函数如果在scott里创建,通过scott登陆则查询不了system的表;
若在system里创建,通过system登陆,则查询不了scott的表;
我把以下存储过程,使用本地动态SQL实现,现象也如上面所描述的一样.
急寻答案,谢谢!
CREATE OR REPLACE function get_row_cnts(p_tname in varchar2) return number
as
v_the_cursor
int;
v_column_value
number default NULL;
v_status
int;
begin
v_the_cursor := dbms_sql.open_cursor;
dbms_sql.parse(c=>v_the_cursor,statement=>'select count(*) from '||p_tname,language_flag=>dbms_sql.native);
dbms_sql.define_column(c=>v_the_cursor,position=>1,column=>v_column_value);
v_status := dbms_sql.execute(v_the_cursor);
if ( dbms_sql.fetch_rows(c=>v_the_cursor) > 0) then
dbms_sql.column_value(c=>v_the_cursor,position=>1,value=>v_column_value);
end if;
dbms_sql.close_cursor(c=>v_the_cursor);
return v_column_value;
exception
when others then
dbms_output.put_line('=========>'||sqlerrm);
dbms_sql.close_cursor(c=>v_the_cursor);
RAISE;
end;
/
CREATE OR REPLACE PROCEDURE OBJECT_cont
(v_collection_time date)
AS
v_segment_name dba_segments.segment_name%type;
v_owner
dba_segments.owner%type;
v_bytes
number;
v_count
int;
str_tmep
varchar(400);
cursor tab_info is
select segment_name,owner,bytes/1024/1024 from DBA_SEGMENTS where segment_type = 'TABLE';
BEGIN
open tab_info;
loop
fetch tab_info into v_segment_name,v_owner,v_bytes;
exit when tab_info%notfound;
str_tmep := 'select count(*) from c_ynunicom.'||v_segment_name;
EXECUTE IMMEDIATE str_tmepinto v_count;
.......................................................
commit;
end loop;
close tab_info;
END;
/
|
