Solved

get table record count in oracle

Posted on 2009-04-12
5
785 Views
Last Modified: 2013-12-18
want to count records for all the tables in a schema. For example,
SQL> select table_name from user_tables order by table_name;
T1
T2
T3

3 rows selected.

SQL> select count(1) from t1;
         2
Can one complex query be written to get the records per table? The result is
table_name   count_records
T1                 2
T2                 5
T3                 4
...
0
Comment
Question by:jl66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 24127825
if your statistics are up to date you can get close approximations by selecting "num_rows" from user_tables

select table_name,num_rows from user_tables;

if you really want to count all of the rows...


select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from user_tables

note, if you have many large tables this could take a long time.
0
 

Author Comment

by:jl66
ID: 24133897
Excellent!
Is it possible to loop all tables in one schema and get certain columns out in each table?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24134285
I'm not sure what you're asking.

If I've answered your original question please close this question.

When you open a new question, please include sample input and expected output to illustrate what you're looking for.
When you close this question there will be an option to ask a related question if you want to tie these two together
0
 

Author Closing Comment

by:jl66
ID: 31569400
Excellent.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24137050
glad I could help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question