Method One: First establish a computational function
1 Create or Replace functionCount_rows (table_nameinch varchar2,2Ownerinch varchar2 default NULL)3 return Number4AuthidCurrent_User5 is6Num_rows Number;7stmtvarchar2( -);8 begin9 ifOwner is NULL ThenTenstmt:= 'Select COUNT (*) from "'||table_name||'"'; One Else Astmt:= 'Select COUNT (*) from "'||Owner||'"."'||table_name||'"'; - End if; - ExecuteImmediate stmt intonum_rows; the returnnum_rows; - End;
And then, by counting the functions,
Select from User_tables
Gets the value to be counted
Method Two:
1 Select from
View the number of records, but the Num_rows store is the last parsed value, not accurate, to use this method, you must analyze before you can try to complete the statement as
1 Declare2V_tnamevarchar( -);3V_sqlanalyzevarchar( -);4V_num Number;5V_sqlvarchar( -);6 cursorC17 is8 Selecttable_name fromUser_tables;9 beginTen OpenC1; One Loop A FetchC1 intoV_tname; - ifC1%Found Then -V_sqlanalyze:='Analyze Table'||V_tname||'Estimate Statistics'; the Executeimmediate v_sqlanalyze; -V_sql:= 'Select Num_rows from user_tables where table_name =upper (" "||V_tname||" ")'; - ExecuteImmediate V_sql intoV_num; -Dbms_output.put_line ('Table Name:'||V_tname||'number of rows:'||v_num); + Else - Exit; + End if; A EndLoop; at End;
The number of records for all tables in the Oracle query database