3

I'm querying dba_tables in order to get a listing of tables and their rowcount with following sql request :

select * from dba_tables where owner='dbSID';

But I notice that even for some non empty tables, NUM_ROWS column is returning null values.

Could somebody please explain this strange behavior of dba_tables ?

1 Answer 1

3

It's an estimate. See http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091.
If you run the following :

exec dbms_stats.gather_schema_stats('owner'); 

within Oracle SQL*Plus. You'll get then the exact value of row numbers (until subsequent inserts / deletes change the actual row count so it no longer matches the one gathered).

If you need a precise value you'll need to use SELECT count(*) ...

2
  • thanks @yangyang how can I loop over all tables in user_tables if I use Select count(*) ... statement ? In this case I will need a stored procedure for that.
    – mounaim
    Sep 12, 2014 at 13:16
  • Yes, you'll need to use PL/SQL for that.
    – hbn
    Sep 12, 2014 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.