Counting no. of records from multiple tables; Oracle DB

Question

I know i need to use this query to get the list of tables for a schema: select table_name from all_tables where owner='schema'

I know the following query counts the record in a table: select count(*) from schema.table

There are 2400+ tables in that schema. My question is how to count the number of records from all the tables using one step?

Rob van Wijk · Accepted Answer · 2011-07-15 08:54:45Z

2

You can use DBMS_XMLGEN.GETXMLTYPE function to do this in one shot:

SQL> select table_name
  2       , to_number
  3         ( extractvalue
  4           ( dbms_xmlgen.getxmltype('select count(*) c from ' || table_name)
  5           , '/ROWSET/ROW/C'
  6           )
  7         ) cnt
  8    from user_tables
  9   order by table_name
 10  /        

TABLE_NAME                            CNT
------------------------------ ----------
... [output removed] ...

71 rows selected.

But if your schema contains a lot of data, this might take a long time. Just selecting NUM_ROWS might be sufficient if estimations are ok as well.

Regards,
Rob.

answered Jul 15, 2011 at 8:54

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

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

Comments

Sebi · Accepted Answer · 2011-07-15 06:44:02Z

1

The table ALL_TABLES contains the column NUM_ROWS. (You can get a description of the table with the following SQL statement: DESCRIBE ALL_TABLES;)

The following statement shows the number of records for every table:

SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES WHERE OWNER='SCHEMA';

To get the number of records in all tables of your schema, use:

SELECT SUM(NUM_ROWS) FROM ALL_TABLES WHERE OWNER='SCHEMA';

answered Jul 15, 2011 at 6:44

Sebi

2,5843 gold badges26 silver badges29 bronze badges

2 Comments

Alex Poole Over a year ago

NUM_ROWS Is populated when statistics are gathered. It is therefore almost always (a) an estimate and (b) out of date. Depending on how, and how often, the stats are gathered it may be wildly innaccurate. It might be useful for a quick hint on which tables are likely to be largest, but other than that I wouldn't rely on it...

tibby Over a year ago

Thank you all, it really helped. As I only needed to know which tables are populated and proximate row count was enough, the NUM_ROWS column did it for me. Thanks again! T.

Collectives™ on Stack Overflow

Counting no. of records from multiple tables; Oracle DB

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related