1

I am attempting query my oracle database to determine the number of entries in each table. I spooled the result of the following query to a sql file : 

select 'select count(*) '||table_name||';' from dba_tables;

This created a sql file of the format

select count(*) from Table_Name1;
select count(*) from Table_Name2;
etc

I then spooled the output of running this sql file, however it only returned a count for some of the tables, for other tables it simply says:

'This table or view does not exist'

How can that be if I've just populated that list of table names automatically from the database?

The end aim of this exercise is to have a file with the number of all fields in each table, then make a change to the program using this database and run the same operation again into a new file and compare the 2 so that I can see which tables change when I make a change to the program.

JabbaWook
  • 677
  • 1
  • 8
  • 25
  • Regarding your end goal rather than the error you get, [@a_horse_with_name's XML magic trick](http://stackoverflow.com/a/10705088/266304) is pretty nifty. – Alex Poole Nov 16 '15 at 16:13
  • Column `NUM_ROWS` in table `DBA_TABLES` contains number of rows. But you have to gather statistics first. – Dmitriy Nov 16 '15 at 16:34
  • 2
    Apparently not the problem you had, but for anyone else, could also be the DBA_TABLES shows all tables, whether you have access to them or not. So could include a table that would error when actually trying to access. ALL_TABLES limits the results to tables you have rights to see. – Shannon Severance Nov 16 '15 at 16:56
  • that's a good point, I was doing this as a sysdba user which is why it wasnt a problem for me. – JabbaWook Nov 17 '15 at 09:03

1 Answers1

6

You forgot to fully qualify the names:

select 'select count(*) '||owner||'.'||table_name||';' 
from dba_tables;

In case you have tables that have non-standard names, you should throw double quotes in there as well:

select 'select count(*) "'||owner||'"."'||table_name||'";' 
from dba_tables;
  • fantastic thank you! that worked! you wouldn't happen to know of a way to overcome the 'cannot reference overflow table of an index-organised table' error do you? or should I ask that in a new question? – JabbaWook Nov 16 '15 at 16:05
  • 1
    @JabbaWook - different question really, but add `where iot_type != 'IOT_OVERFLOW'`. – Alex Poole Nov 16 '15 at 16:15