getting the table names and their row counts using a query in oracle db

Question

I have a database and I want to get the names of the tables in that database along with the number of data rows in those tables.

select table_name from dba_tables where owner = 'owner'

returns the names of all the tables in the database, but I can't get the number of rows in each table.

Querying the num_rows column would not give the row count in real time since it requires all the tables to run the "analyze" command on them.

Is there a way to get both columns, the table name and the row count of each respective table using a single query?

Lukasz Szozda · Accepted Answer · 2015-09-06 13:22:45Z

3

Use:

SELECT 
   table_name, 
   num_rows,
   last_analyzed
FROM dba_tables

You may need to update statistics before:

exec dbms_stats.gather_schema_stats(ownname => 'NAME');

answered Sep 6, 2015 at 13:22

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Uwe Fest · Accepted Answer · 2025-08-06 15:12:58Z

0

with function row_count(tab_name in varchar2) return number as
    rc number;
begin
    execute immediate 'select count(*) from ' || tab_name into rc;
    return rc;
end;
select table_name, row_count(table_name) as row_count from all_tables
    where owner = 'owner';
/

edited Aug 6 at 15:12

answered Aug 6 at 15:12

Uwe Fest

11 bronze badge

1 Comment

Jeremy Caney Aug 6 at 19:32

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

getting the table names and their row counts using a query in oracle db

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related