A-A+
scripts:统计指定用户下所有表的总条数
用途:
统计指定用户下所有表的总条数并分列单表条数(实际的总条数非统计信息中的条数)以供统计之用。
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