0

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?

HenricF
  • 218
  • 3
  • 19
kimoturbo
  • 41
  • 1
  • 1
  • 8
  • 1
    Why are you still using oracle 7? Is there something holding you back to upgrading to something more modern. Oracle 7 is from '92... – philnate Apr 15 '15 at 19:31
  • 1
    `SELECT table_name, num_rows from all_tables` these 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. – xQbert Apr 15 '15 at 19:33
  • That's what I am trying to do. I am trying to update from oracle 7 to 10g or 11g, but there are many things that I need to figure out in the database before I can upgrade so that I don't mess things up, since it's a database of a company. I just need to know which tables that are useless and that I can drop. They have around 370 tables and I am sure they are only using around 50 or so tables, so I need to drop the rest and then I will react the new database on my own. – kimoturbo Apr 16 '15 at 06:20
  • This "Might" be relevant... but 7 docs are painful to read and more painful to search. http://stackoverflow.com/questions/2247310/how-do-i-list-all-tables-in-a-schema-in-oracle-sql – xQbert Apr 16 '15 at 13:59

1 Answers1

2

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.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Note that this question is about Oracle 7(!). Did Oracle 7 even have optimizer statistics and the num_rows column? – Jon Heller Apr 15 '15 at 20:23
  • I tried the num_rows column and it always gave me null answers. I tried to update it using the DBMS_STATS, but it said DBM_STATA must be declared first. Also I tried to a similar query to the one you wrote and it didn't work out as well. SQL PLUS Code: select 'select count(*) from '|| owner || '.' || table_name || ';' from dba_tables where owner = 'xxxx' order by table_name; – kimoturbo Apr 16 '15 at 06:24
  • You're correct this is more of a > 7 answer. I've not been able to find evidence of system tables existing in 7 yet. Does 7 even have a dictionary to list the available system objects you could inquire upon `Select * from dictionary where table_name like '%TABLE%'`? – xQbert Apr 16 '15 at 13:11
  • In Oracle 7, All_tables does exist, but the problem is with the num_rows not with the All_tables. It just returns null and when I tried to use the DBMS_STATS like I found online. It said that it must be declared first. I don't know about the from dictionary, but I don't think it would make a difference since my problem lies with either counting the rows or using the num_rows and both of them don't work. – kimoturbo Apr 16 '15 at 18:41
  • I was going to have you look at dictionary to see if there was a different system table in 7 that contained the table listing. If all tables is there and works, then it seems odd that the select to generate a select statement that you then ran wouldn't work. – xQbert Apr 16 '15 at 19:47
  • Is it possible to make a loop and in each iteration to count the number of rows in each table? – kimoturbo Apr 17 '15 at 02:42