Can someone tell me how I can get the number of rows in each table in my oracle database? I have found several queries, but none of them worked because I am using oracle 7 and sqlplus 3.2 and basically all what I found didn't work on it. I just need something that would work on sqlplus 3.2.
Required:
Table Name Rows
Table 1 0
Table 2 5
...
Is it possible to do it with something like a loop? Or what exactly should I do?
if SELECT table_name, num_rows from all_tables doesn't give you what you need.
You could use dynamic SQL and counts as Rahul selected.
Run the below to get results which dynamically build a union on all tables, then run the results as it's own query to get final results.
SELECT 'SELECT ' ||t.name || ' as tName, count(*) as Row_Cnt
FROM ' || t.name || ' UNION ALL '
FROM ALL_TABLES t
Just be sure to remove the last union statement on the last query.
Also note: if you don't have access to see the table, it will not come out in this list!
---Updated ------
So if all_tables doesn't exist none of this will work. Since I don't have a oracle 7 instance handy... could you see if SELECT * FROM dictionary returns anything that might produce a list of all the tables? If you find a view or table object use it in place of all_tables above.
I'm reading the docs for oracle 7 now but finding little easily searchable. thus a guess and check method may go faster.
Select * from dictionary where table_name like '%TABLE%'?
SELECT table_name, num_rows from all_tablesthese are table statistics from which db engine uses to determine optimal execution paths, they are not 100% but usually close enough. Also note, all_tables is limited to what you have access to see. If you have no permissions to the table, you will not be able to see the table/record count in the results.