用途：
统计指定用户下所有表的总条数并分列单表条数（实际的总条数非统计信息中的条数）以供统计之用。

set serverout on
declare 
v_sum_num number :=0;
v_num number :=0;
v_table_name varchar2(100);
str_sql varchar2(500);
cursor tab_name is select table_name from dba_tables where owner= upper('&user_name') order by 1;
begin
	dbms_output.put_line('---------TABLE SUM------------');
	for i in tab_name
		loop
			v_table_name:=i.table_name;
			execute immediate 'select count(*) from ' || v_table_name into v_num;
			dbms_output.put_line(v_table_name||':'||v_num);
			v_sum_num:=v_num+v_sum_num;
		end loop;
	dbms_output.put_line('------------------------------');
	dbms_output.put_line('SUM_NUM:'||v_sum_num);
end;

输出示例如下：

SQL> set serverout on
SQL> declare 
  2  v_sum_num number :=0;
  3  v_num number :=0;
  4  v_table_name varchar2(100);
  5  str_sql varchar2(500);
  6  cursor tab_name is select table_name from dba_tables where owner= upper('&user_name') order by 1;
  7  begin
        dbms_output.put_line('---------TABLE SUM------------');
  8    9        for i in tab_name
 10             loop
 11                     v_table_name:=i.table_name;
 12                     execute immediate 'select count(*) from ' || v_table_name into v_num;
 13                     dbms_output.put_line(v_table_name||':'||v_num);
 14                     v_sum_num:=v_num+v_sum_num;
 15             end loop;
 16     dbms_output.put_line('------------------------------');
 17     dbms_output.put_line('SUM_NUM:'||v_sum_num);
 18  end;
 19  /
Enter value for user_name: test
old   6: cursor tab_name is select table_name from dba_tables where owner= upper('&user_name') order by 1;
new   6: cursor tab_name is select table_name from dba_tables where owner= upper('test') order by 1;
---------TABLE SUM------------
TABLE1:2770
TABLE2:5092
------------------------------
SUM_NUM:7862

PL/SQL procedure successfully completed.



SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TABLE2
TABLE1
	
SQL> select count(*) from table1;

  COUNT(*)
----------
      2770

SQL> select count(*) from table2;

  COUNT(*)
----------
      5092

SQL> select 2770+5092 from dual;

 2770+5092
----------
      7862