[ORACLE]统计某用户下所有表的总条数

统计某用户下所有表的总条数

-- Created on 2020/11/10 by XXX 
declare
  -- Local variables here
  i integer;
  cursor c is
    select t.TABLE_NAME from all_tables t where t.OWNER = 'XXX';
  p_table_name varchar(64);
  l_cnt        varchar2(20);
  l_sql        varchar2(128);
begin
  -- 打开游标
  open c;
  -- 循环
  loop
    -- 游标数据放入变量
    fetch c
      into p_table_name;
    exit when c%notfound;
  
    /** 拼接SQL */
    l_sql := 'select count(1) from XXX.' || p_table_name;
    execute immediate l_sql
      into l_cnt;
    -- 打印
    dbms_output.put_line(p_table_name || '=>' || l_cnt);
  
    -- 结束循环
  end loop;
  -- 关闭游标
  close c;
end;