Search code examples
sqloracle-databaseora-00937

Extracting number of records in each table

I want to write a query which retrieves rows in each tables present under a particular owner. I am not very good at writing queries and so I could not figure out the solution on internet. The query I am writing is:

SELECT TABLE_NAME, COUNT(*) FROM DBA_TABLES WHERE TABLE_NAME IN 
(SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='ABC');

I know the query is completely wrong but I have just given it to let know what exactly I want. I hope it helps. The error which I am getting is:

ORA-00937: not a single-group group function

Please help me in writing the exact query for the same. Thanks!

Solution

  • Extracting number of records in each table

    Then you are completely in a wrong direction while querying DBA_TABLES. It will give you the count of tables owned by a user, but not the count of rows in each table.

    There are three ways:

    • In pure SQL, the table name must be static. i.e. you will have to list down the table names explicitly and get the count.
    SELECT COUNT(*) FROM table_1;
SELECT COUNT(*) FROM table_2;
SELECT COUNT(*) FROM table_3;
...
and so on...

    Using a good text editor, you could do it quickly.

    • If the statistics are collected up to date, then you could rely on NUM_ROWS in DBA_TABLES. In any case, better gather the statistics first using DBMS_STATS.
    SQL> SELECT table_name, num_rows FROM dba_tables WHERE owner='SCOTT';

TABLE_NAME   NUM_ROWS
---------- ----------
CLASS               0
SALGRADE            5
BONUS               0
DEPT                4
EMP                14
    • In PL/SQL, loop through all the table names, create dynamic sql and execute (ab)using EXECUTE IMMEDIATE.