How do I achieve the total number of records in all the tables in a library in Oracle?

If your user does not have DBA authority and only wants to view the number of records for all of the tables in this user (note: Not all tables in the library you said), change to:

EXECUTE dbms_utility. Analyze_schema (' MyUserName ', ' COMPUTE ');
SELECT OWNER, TABLE_NAME, num_rows from User_tables;

Note: MyUserName is your username, in uppercase.

The reason for not making it a function is that analyze is a time-consuming task when the volume of data is large. If the exact number is not required, the second and subsequent statistics can use the previous analyze results.


Another method:

SET ECHO off
SET head off
SET Linesize 200
SET PAGESIZE 0
Select ' Select ' | | Tname | | ' TabName, COUNT (1) from ' | | Tname | | ';' From TAB;
SPOOL C:\COUNTALL. Sql
/
SPOOL off

Then execute the generated countall.sql