Extracting number of records in each table

Question

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!

`SELECT TABLE_NAME, COUNT(*) FROM DBA_TABLES WHERE OWNER='ABC' GROUP BY TABLE_NAME` — Mihai, Oct 12 '15 at 07:10
"*I want to write a query which retrieves rows in each tables present under a particular owner*" So you want to fetch rows from the tables or do you want just the metadata information? — Lalit Kumar B, Oct 12 '15 at 07:10

score 2 · Accepted Answer · answered Oct 12 '15 at 07:18

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.

Using the second method (NUM_ROWS) does not seem to be working. I get one more column in output named NUM_ROWS but the fields under the same is blank for all the tables. — user3379410, Oct 12 '15 at 07:33
@user3379410: then you haven't collected any statistics for your tables. — , Oct 12 '15 at 07:34
@user3379410: no, I mean there are no _statistics_ for those tables: http://docs.oracle.com/database/121/CNCPT/sqllangu.htm#CNCPT88915 — , Oct 12 '15 at 07:39

score 0 · Answer 2 · answered Oct 12 '15 at 07:10

When you using group function (count) you must place all columns which appear in SELECT and not in group function in GROUP BY section

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

BUT this query will return nothing interesting to you. It is not number of rows. It is number of tables in that schema.

score 0 · Answer 3 · answered Oct 12 '15 at 07:51

Getting the row count of all tables, can be done using a little XML magic:

select owner, 
       table_name, 
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from "'||owner||'"."'||table_name||'"')),'/ROWSET/ROW/C')) as count
from dba_tables
where owner = 'ABC' 
  and iot_name is null;

This generates a select count(*) from .. for every table, then "runs" that through dbms_xmlgen and parses the generated XML output for the actual number.

dbms_xmlgen documentation

Extracting number of records in each table

3 Answers3