Retrieve Row Counts for All Oracle Tables

When managing large Oracle databases, you may need to quickly retrieve the row counts for all tables to analyze table usage or storage. Below are two effective queries to achieve this goal.

1. Using DBMS_XMLGEN for Accurate Counts

This method dynamically generates a query to count rows in each table. It combines ALL_TABLES, USER_TAB_PRIVS, and DBMS_XMLGEN for accurate results.

Query:

SELECT
    t.owner,
    t.table_name,
    TO_NUMBER(
        EXTRACTVALUE(
            XMLTYPE(
                dbms_xmlgen.getxml(
                    'SELECT COUNT(*) c FROM ' || t.owner || '.' || t.table_name
                )
            ),
            '/ROWSET/ROW/C'
        )
    ) AS row_count
FROM all_tables t, user_tab_privs p
WHERE t.owner = p.owner
  AND t.table_name = p.table_name
  AND privilege = 'SELECT'

UNION ALL

SELECT
    USER AS owner,
    t.table_name,
    TO_NUMBER(
        EXTRACTVALUE(
            XMLTYPE(
                dbms_xmlgen.getxml('SELECT COUNT(*) c FROM ' || t.table_name)
            ),
            '/ROWSET/ROW/C'
        )
    ) AS row_count
FROM user_tables t;

2. Using ALL_TABLES.NUM_ROWS for Estimated Counts

This method retrieves precomputed row counts from the ALL_TABLES view, which is updated during statistics gathering operations. It is faster but relies on up-to-date statistics.

Query:

SELECT owner, table_name, num_rows
FROM ALL_TABLES
WHERE num_rows > 0
  AND owner = 'OWNER_NAME'
ORDER BY num_rows DESC;

When to Use Each Query

Best Practices

1. Statistics Maintenance:
   • Ensure table statistics are up-to-date by running DBMS_STATS.GATHER_SCHEMA_STATS.
2. Performance:
   • Use the ALL_TABLES.NUM_ROWS method for large schemas to avoid querying every table dynamically.
3. Access Privileges:
   • Ensure your user has SELECT privileges for all required tables when using the DBMS_XMLGEN method.

Conclusion

Whether you need real-time counts or prefer a performance-oriented approach, these queries will help you efficiently retrieve row counts for all Oracle tables. Choose the method that best suits your requirements and database configuration.

Would you like help adapting these queries for your specific use case? 😊