pt_wood
asked on
How to get total row count from Oracle (10g)
Is there a simple way in Oracle, using an sql query or via SQLDeveloper to query the total number of rows for a schema of a given user? Thank you.
You really want a count of all rows in all tables for a user?
Use the following:-
select sum(num_rows) Total from user_tables;
above query may not give exact result. It deponds on how you analyze the tables.
If you want exact, then do this:-
declare
total number ;
cursor c1 is select table_name from user_tables;
str varchar(4000);
cnt number;
begin
total := 0;
for rec in c1 loop
str := 'select count(*) from ' || rec.table_name ;
execute immediate str into cnt;
total := total + cnt;
end loop;
End;
/
select sum(num_rows) Total from user_tables;
above query may not give exact result. It deponds on how you analyze the tables.
If you want exact, then do this:-
declare
total number ;
cursor c1 is select table_name from user_tables;
str varchar(4000);
cnt number;
begin
total := 0;
for rec in c1 loop
str := 'select count(*) from ' || rec.table_name ;
execute immediate str into cnt;
total := total + cnt;
end loop;
End;
/
you can do it in a single sql too
SELECT table_name,
TO_NUMBER(EXTRACTVALUE(
xmltype(DBMS_XMLGEN.getxml
'/ROWSET/ROW/X'
))
COUNT
FROM user_tables;
if you want to count rows in a schema other than your own change the FROM clause to be
FROM all_tables where owner = 'YOURUSER'
SELECT table_name,
TO_NUMBER(EXTRACTVALUE(
xmltype(DBMS_XMLGEN.getxml
'/ROWSET/ROW/X'
))
COUNT
FROM user_tables;
if you want to count rows in a schema other than your own change the FROM clause to be
FROM all_tables where owner = 'YOURUSER'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was exactly what I was looking for - thank you.
glad I could help