Re: How to count total number of rows from all user-tables

shinwar wrote:

> Hi, Oracle Gurus
>
> Before this I have posted one question. Now, I have one more question
> on how to count total number rows of each user table using a single
> query statement. I really do not know if it can be done in a single
> SQL statement. Let's say. I have 100 user tables. I do not want to
> issue 100 SQL commands for this job.
>
> Instead, using a single SQL which uses user_ind_columns.table_name, I
> like to find total number of rows of all my tables. The output should,
> for example, be in this format:
>
> user-tables total_no_of_rows
> ----------- -----------------
> table1 99999
> table2 99999
> ....
> ....
>
> Appreciate any of your advise. Thank you for your valuable time.

I read the other replies and they all seem, to me to be trying to hard.

Now you, of course didn't bother to indicate your verion of Oracle but assuming 8i you should be using CBO which means you should be running regular statistics using DBMS_STATS. In which case the following SQL statement will do the job just fine.

SELECT SUM(num_rows)
FROM user_tables;

If you are not keeping your statistics current then you should be.

In earlier versions ... various analyze table options can be used to load the user_tables view.

Daniel Morgan Received on Fri Oct 04 2002 - 10:10:46 CDT