Counting rows in all tables of an Oracle schema can be a challenge, especially with rows being constantly added and deleted. There are two primary methods to retrieve row counts:

1. Counts from Last Analysis:
   Use the NUM_ROWS column in DBA_TABLES, which reflects the row count as of the last DBMS_STATS analysis. However, this count can become outdated.
2. Real-Time Row Count:
   Execute SQL queries to count rows in each table at runtime. This is accurate but can be time-consuming.

For a more elegant solution, Oracle ACE Laurent Schneider demonstrates how  DBMS_XMLGEN to efficiently retrieve row counts.

Real-Time Row Count Query

Here’s how you can retrieve the row count for each table in your schema:

SELECT
    table_name,
    TO_NUMBER(
      EXTRACTVALUE(
        XMLTYPE(
          DBMS_XMLGEN.GETXML('SELECT COUNT(*) c FROM ' || table_name)
        ), '/ROWSET/ROW/C'
      )
    ) AS ROWS_COUNT
FROM USER_TABLES
ORDER BY ROWS_COUNT DESC;

Explanation of the Query

1. USER_TABLES:
   Retrieves all table names in the current user’s schema.
2. DBMS_XMLGEN.GETXML:
   Executes a dynamic SQL query (SELECT COUNT(*)) for each table and returns the result as XML.
3. EXTRACTVALUE:
   Extracts the row count from the XML data returned by DBMS_XMLGEN.
4. TO_NUMBER:
   Converts the extracted value into a numeric format for sorting and display.
5. ORDER BY ROWS_COUNT DESC:
   Displays the tables with the highest row counts first.

Sample Output

Advantages of This Approach

• Real-Time Accuracy: Retrieves the exact row count at query execution.
• Single Query: Efficiently counts rows for all tables without looping through them manually.
• Dynamic Execution: Adapts automatically to all tables in the schema.

Caveats and Considerations

• Performance: For schemas with a large number of tables or high row counts, execution time may increase.
• Permissions: Ensure the user running the query has access to USER_TABLES and the DBMS_XMLGEN package.

Alternative: Using NUM_ROWS from DBA_TABLES

For a faster but potentially outdated result, you can query the NUM_ROWS column in DBA_TABLES:

SELECT table_name, num_rows
FROM user_tables
ORDER BY num_rows DESC;

This method reflects counts as of the last statistics gathering (DBMS_STATS.GATHER_TABLE_STATS).

Conclusion

For accurate, real-time row counts, the DBMS_XMLGEN approach is efficient and easy to implement. Use it when you need up-to-date data, or rely on DBA_TABLES for a quicker estimate.

Would you like further assistance with Oracle queries or database optimization tips? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio