新建一个表和存过统计所有表的大小

新建表：

CREATE TABLE TB_STATIC_TAB 
   (    STATIC_DATE DATE, 
    TABLE_NAME VARCHAR2(200), 
    TABLE_ROWS NUMBER, 
    TABLE_SIZE NUMBER
   ) 

新建存过：

create or replace procedure pro_temp3

 is

  vv_table_name varchar2(64);

  vi_table_rows number;

  vv_sqlstr varchar2(200);

  cursor r_cursor is
  
    select table_name from user_tables;

begin

  EXECUTE IMMEDIATE 'truncate table tb_static_tab';

  OPEN r_cursor;

  LOOP
  
    FETCH r_cursor
      INTO vv_table_name;
  
    exit when r_cursor %notfound;
  
    vv_sqlstr := 'select count(1) from ' || vv_table_name;
  
    begin
    
      EXECUTE IMMEDIATE vv_sqlstr
      
        into vi_table_rows;
    
      insert into tb_static_tab
      
        (
         
         static_date,
         
         table_name,
         
         table_rows,
         
         table_size
         
         )
      
      values
        (
         
         sysdate,
         
         vv_table_name,
         
         vi_table_rows,
         
         null
         
         );
    
    EXCEPTION
    
      WHEN others THEN
        null;
      
    end;
    commit;
  
  end loop;

  close r_cursor;

  commit;

end;