How to determine row count for all tables in an Oracle Schema?
Posted by Vivek Agarwal on July 17, 2007
If you ever need to determine the row count for all tables in an Oracle Schema as I did, here is something to help you out.
Disclaimer: This is not my original work and I do not claim to be an Oracle DBA/expert, but I am writing it up as I continually need this and each time have to go through a discovery process on how to do it.
Save the script: Save the following snippet as a sql script file – for example, “OracleUserTableRowCount.sql” (unfortunately, I do not remember where I got this script from and cannot give credit where it is due).
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off
Execution:
- Connect to the Oracle database using the user for whom you want to generate the table row count report. For example -
D:oraclescriptsrowCount>sqlplus icm/foo@wps6SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jul 17 11:29:25 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionSQL>
- Next execute the script you created -
SQL> @OracleUserTableRowCount.sql
SQL>
This will create a file with the row counts in the directory you started sql-plus from.
Sathish said
This query is good. I was searching in the net among them this is straight forward query.
Sam said
Thanks for the script, I did a few more updates on this:
-> Added the owner name
-> getting table list from all_tables, excluding system, sys
-> seperating the table name and count by a comma to be able to open in an excel as a csv file
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 1000
Column d noprint new_value date_
Column u noprint new_value user_
Spool c:\temp\tmp
Select ‘Select ”’||owner||’.'||table_name||’ , ”||count(*) from ‘||owner||’.'||table_name ||’;',
to_char(sysdate, ‘YYYYMMDDHH24MISS’) d, user u
from all_tables
where owner not in (‘SYS’, ‘SYSTEM’)
order by table_name
/
Spool off
matthew kerle said
why not just query num_rows directly from the user_tables table?
select table_name, num_rows, blocks from user_tables order by num_rows desc;
much easier one query not n + 1.
JonWat said
Because the information in user tables is as of the last time you gathered statistics on the tables: it is not a count of the current # of rows. If you haven;t analysed the tables, those columns will be blank.
vehshi said
Then where does count(*) on a table gets it data from?