--先建立函数
create or replace function f_lqf_temp
(tablename in varchar2)
return number
is
ls_sql varchar2(1000);
li_num number(10,0);
begin
ls_sql := 'select count(*) from ' || tablename;
execute immediate ls_sql into li_num;
return li_num;
end f_lqf_temp;
--处理
select table_name,f_lqf_temp(table_name) from dba_tables where owner = 'GZ1117' and table_name like '%CHECK%'
我整理了一下！
--使用system用户创建统计表
create table table_rows
(table_name varchar2(50) not null,
table_rows number not null);
--把权限赋给username用户
grant all on table_rows to username;
--切换至username用户下 建立函数
create or replace function count_row
(tablename in varchar2)
return number
is
ls_sql varchar2(500);
li_num number(10,0);
begin
ls_sql := 'select count(*) from ' || tablename;
execute immediate ls_sql into li_num;
return li_num;
end count_row;
--切换至username用户下 处理
truncate table system.table_rows;
insert into system.table_rows (table_name,table_rows)
select table_name,count_row(table_name) from dba_tables where owner = 'USERNAME';
