How will you find out the number of rows in each table in a schema in Oracle?
Here are two approaches with their pros and cons.
1. Check USER_TABLES
Pros: Very fast
Cons: Accurate only upto LAST_ANALYZED date of USER_TABLES. If statistics are up-to-date, this method will give a good estimate of data volume.
SQL> select table_name, num_rows 2 from user_tables; TABLE_NAME NUM_ROWS ------------------------------ ---------- REGIONS 4 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 TCOMP_KEY 0 JOB_HISTORY_NOTES 0 TCOMP_KEY_NOTES 0 COUNTRIES 25 10 rows selected.
The result may not be reliable though if table volumes change rapidly, or if the LAST_ANALYZED date is too old. In the second case, you could get the schema statistics updated and then run the query to select NUM_ROWS per table.
SQL> -- Gather statistics to update NUM_ROWS
SQL> exec dbms_stats.gather_schema_stats('HR');
PL/SQL procedure successfully completed.
Run the same SQL again – note the difference in count of REGIONS table.
SQL> select table_name, num_rows 2 from user_tables; TABLE_NAME NUM_ROWS ------------------------------ ---------- REGIONS 5 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 TCOMP_KEY 0 JOB_HISTORY_NOTES 0 TCOMP_KEY_NOTES 0 COUNTRIES 25 10 rows selected.
2. Write PL/SQL to Select Count per Table
Pros: Accurate, since counts are real-time values
Cons: Slow
Function to calculate count using dynamic SQL, for an input table name:
create or replace function
count_rows (p_tablename in varchar2)
return number
as
l_count number;
begin
execute immediate
'select count(*)
from ' || p_tablename
into l_count;
return l_count;
end;
/
Compiling the function:
SQL> create or replace function 2 count_rows (p_tablename in varchar2) 3 return number 4 as 5 l_count number; 6 begin 7 execute immediate 8 'select count(*) 9 from ' || p_tablename 10 into l_count; 11 12 return l_count; 13 end; 14 / Function created.
How to invoke this function:
SQL> select table_name 2 , count_rows(table_name) num_rows 3 from user_tables; TABLE_NAME NUM_ROWS ------------------------------ ---------- REGIONS 5 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 TCOMP_KEY 0 JOB_HISTORY_NOTES 0 TCOMP_KEY_NOTES 0 COUNTRIES 25 10 rows selected.
