Vivek Agarwal’s Portal/Java Blog

An IBM Gold Consultant’s weblog about IBM, Lotus, WebSphere, J2EE, IT Processes, and other IT technologies

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@wps6
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jul 17 11:29:25 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>
  • 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.

Advertisements

11 Responses to “How to determine row count for all tables in an Oracle Schema?”

  1. Sathish said

    This query is good. I was searching in the net among them this is straight forward query.

  2. 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

  3. 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.

  4. Ronald said

    Thanks a lot for the query it saved my time

  5. Jasdev said

    Here is another SQL that will work 10g and up

    select table_name,
    to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name)),’/ROWSET/ROW/C’)) as count
    from user_tables

    • Aravind said

      How can i add a where clause to this query?
      select table_name,
      to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name)),’/ROWSET/ROW/C’)) as count
      from user_tables

      select count(*) c from ‘||table_name||’ where field1 = ‘xyz’

      Thanks.

  6. Luke said

    Wouldn’t this do the same thing? Select table_name, Num_Rows from All_tables where OWNER = ‘SCHEMA Name’

  7. Venkat Chitta said

    Yes really excellent and useful. Thanks vivek.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: