开发者

Counting no. of records from multiple tables; Oracle DB

开发者 https://www.devze.com 2023-03-20 06:49 出处:网络
I know i need to use开发者_JAVA技巧 this query to get the list of tables for a schema: select table_name from all_tables where owner=\'schema\'

I know i need to use开发者_JAVA技巧 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?


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.


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';
0

精彩评论

暂无评论...
验证码 换一张
取 消