Here is a query based on the "dbms.xmlgen" PL/SQL package, returning the name and the record number of each table in a schema of the database. It could be very usefull, if you want to see the total record number of each table of the database.
SELECT table_name,
to_number(extractvalue
(xmltype
(dbms_xmlgen.getxml
('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) nbr_lines
FROM user_tables
ORDER BY 1;
Query result:
TABLE_NAME NBR_LINES
------------------------
COUNTRIES 25
DEPARTMENTS 27
DEPT 29
DEPT2 27
EMP2 0
EMPLOYEES 107
EMPLOYEES2 107
EMPLOYES 4
JOBS 19
JOB_GRADES 6
JOB_HISTORY 10
LOCATIONS 23
MY_EMPLOYEE 4
REGIONS 4
14 rows selected.
If you have an error, think about checking if you have the execution permission on the "dbms.xmlgen" package. By default it must be there. But check it anyway.
Source: Quest-Pipelines.com
