Solved

get table record count in oracle

Posted on 2009-04-12
5
779 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
  • 3
  • 2
5 Comments
 
LVL 73

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 73

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 73

Expert Comment

by:sdstuber
ID: 24137050
glad I could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now