Count records in all the tables owned by a user

[new2ora]

Hi, I am trying to get a count of all the records in all the tables owned by user. I want the result in a file. Here's the format..

table records
------ -------
table1 10
table2 5
table3 1000

Any help is much appreciated.

Thanks

 

[SantaMufasa]

New,

Let's start with the presumption that your organisation:

a) uses Oracle's Cost-Based Optimizer (CBO),
b) regularly gathers statistics to cause the CBO to behave optimally.

Let's also presume that we can satisfy the need you described, above, with fairly accurate data versus must-be-exact, up-to-the-moment accurate data.

If all of the above are true, then the following query should work for you:

select table_name, num_rows from user_tables
order by table_name;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
A                                       8
ACCOUNT                                 2
ADDRESS
ADJUSTMENTTYPE                          0
ADVOCATE                                2
...
YADA2                                   1
YOURTABLE                              22

Notice in the above results, that the ADDRESS table has NULL for NUM_ROWS. This results from my not having yet gathered statistics for the ADDRESS table.

If either:

a) My above presumptions are incorrect, or
b) You do not know how to gather statistics,

...please let us know so we can provide either alternatives or some additional guidance.

In any case, let us know where you and we stand with the information in this posting.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.