create or replace procedure sp_static_tab
/**
* 统计所有表的行数 */is vv_table_name varchar2(64); vi_table_rows number; vv_sqlstr varchar2(200); cursor r_cursor is select table_name from user_tables where substr(table_name,1,3)<>'BIN'; 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 ); end; end loop; close r_cursor; commit; end;
/****************************************
--建表create table tb_static_tab( static_date date , -- 统计日期 table_name varchar2(64), -- 表名 table_rows number, -- 表行数 table_size number -- 表占空间 byte);*****************************************/