首先建立一个计算函数

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> create or replace function count_rows(table_name in varchar2,                               owner in varchar2 default null) return number authid current_user IS    num_rows number;    stmt varchar2(2000); begin    if owner is null then       stmt := 'select count(*) from "'||table_name||'"';    else       stmt := 'select count(*) from "'||owner||'"."'||table_name||'"';    end if;    execute immediate stmt into num_rows;    return num_rows; end;然后通过计算函数进行统计 select table_name, count_rows(table_name) nrows from user_tables

where table_name not in('IPRTT_SUB_POLICY','DC_SR_SUB_POLICY','TTIPR_POLICY','XWZ_POLICY','WZ_POLICY',

 'W_POLICY','IPTTC_COVERAGE_INFO','CYBTTC_COVERAGE','IPC_MO_CYB_TIMEPOINT','CYBTTC_COVERAGE_TEMP',  'IPC_TEMP_CYB','IPC_TEMP_CYBWZX','CYBTT_SUB_POLICY','CYBCYBC_COVERAGE_REL','INSURANCES','IPCASUAL','IPCCYBCYB_COVERAGE_REL',  'IPCOUNTERS','IPCYB_R_SUB_POLICY','IPTEST','SPERRORLOG','TEST','ITTPR_MONEYIN_INFO','IPSUB_POL_ID','IPDC_R_SUB_POLICY'  ,'IPB_B_CALC_MDL','IPS_LOAN_RULE3')  and  table_name not like'%TMP%' 获取要统计的值

 

然后用UE比较

 

或者 select t.table_name,t.num_rows from user_tables t

查看记录数，但是这种不准确。 还是觉得上面的自己写函数实现好些

declarev_tName varchar(50);v_sqlanalyze varchar(500);v_num number;v_sql varchar(500);cursor c1 isselect table_name from user_tables;beginopen c1;loopfetch c1 into v_tName;if c1%found thenv_sqlanalyze :='analyze table  '||v_tName||'  estimate statistics';execute immediate v_sqlanalyze;v_sql := 'select NUM_ROWS  from user_tables where table_name =upper('''||v_tName||''')';execute immediate v_sql into  v_num;dbms_output.put_line('表名： '||v_tName||' 行数： '||v_num);elseexit;end if;end loop;end;