查找数据库所有表行数

冷不防 2022-08-05 12:04 148阅读 0赞

create or replace procedure tj_data is  
      -- 当前存储过程用到的变量
      v_tableName VARCHAR2(100);
      v_sql  varchar2(200);
      v_count number;
      -- 获取当前数据库中的所有表
      CURSOR TABLE_LOOP IS select owner||'.'||table_name from dba_tables where owner in ('SCOTT','YALI') ORDER BY 1 ;
    
      BEGIN
      EXECUTE IMMEDIATE 'truncate table tj_tab';
        -- 打开游标
        OPEN TABLE_LOOP;
          LOOP
            FETCH TABLE_LOOP INTO v_tableName;
              EXIT WHEN TABLE_LOOP %NOTFOUND;
                  v_sql:= 'select count(1) from '||v_tableName;
                      begin 
                      execute immediate v_sql into v_count;
                      dbms_output.put_line(v_tableName||':'||v_count);
                      insert into tj_tab values(v_tableName,v_count);
                      commit;
                      end;
          END LOOP;
        CLOSE TABLE_LOOP;
    end;
    /
    
    
    create table tj_tab
    (
     table_name varchar2(640),  -- 表名
     table_rows number     -- 表行数
    );
    
    
    
    --查询每个用户下表的行数
    SET serveroutput ON;
    DECLARE
      i VARCHAR(200);
      v_sql VARCHAR(2000);
      t_owner VARCHAR(200);
      t_count number;
      CURSOR v_owners
      IS
        SELECT owner||'.'||table_name aa FROM dba_tables where tablespace_name not in ('SYSAUX','SYSTEM') and iot_name is null;
    BEGIN
      for i in v_owners loop
      v_sql:='select count(*) from '|| i.aa;
      execute immediate v_sql into t_count;
      dbms_output.put_line(i.aa||'='||t_count);
      end loop;
    END;