找出记录数最多的表

--找出当前用户记录数最多的表--
create or replace procedure Test
is
  tab_name varchar(200);
  MaxCount_Tab varchar(200);
  MaxCount number(10);
  CurCount number(10);
  cursor cur_tab is select * from cat where table_type='TABLE';
begin
  CurCount:= 0;
  MaxCount:= 0;
  for tab in cur_tab loop
    tab_name:= tab.Table_name;
    EXECUTE IMMEDIATE 'select count(*) from ' ||'"'||tab_name||'"'||'' into CurCount;
    if CurCount>MaxCount then
      MaxCount:= CurCount;
      MaxCount_Tab:= tab_name;
    end if;
  end loop;
  dbms_output.put_line(MaxCount_Tab||','||to_char(MaxCount));
end;

--查询数据库下所有用户下的表的记录数，并插入到A表中--
create table A
(
USERNAME VARCHAR2(20),
TABLENAME VARCHAR2(50),
JS VARCHAR2(10)
);

create or replace procedure p_getcount
as
  miCount INTEGER;
  t_owner varchar2(40);
  t_table varchar2(40);
BEGIN
  FOR c_tab IN (SELECT owner,table_name FROM dba_tables where owner not in ('SYS','SYSTEM','CTXSYS','PERFSTAT','WMSYS','EXFSYS','SYSMAN')) LOOP
  --dbms_output.put_line('select count(*) from ' || c_tab.owner||'.'||c_tab.table_name || '');
  --dbms_output.put_line( c_tab.owner||'.'||c_tab.table_name);
  t_owner := c_tab.owner;
  t_table := c_tab.table_name;
  EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.owner||'.'||'"'||c_tab.table_name||'"' || '' into miCount;
  --dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
  INSERT INTO A VALUES (C_TAB.OWNER,C_TAB.TABLE_NAME,miCount);

  END LOOP;

  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line( t_owner||'.'||t_table);
  RAISE;
  commit;
end;