In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for which schemas a table (with a particular name, which will be the same across all the queried schemas) has records/rows?

Is there a view in Oracle which contains all records? For example, if I am looking for columns, I can search ALL_TAB_COLUMNS; if I am searching for tables, I can look in DBA_TABLES or ALL_OBJECTS. Is there an equivalent for records?

This would also be helpful for another query - to find all tables (of a given name) across all schemas, where a particular field has a non-null value in at least one record.


DBA_TABLES.NUM_ROWS column can be used to identify tables that are empty. But this column is updated by DBMS_STATS, so it is not 100% accurate.

There is no view that contains all records.

  • Note: From Oracle version 10g DBMS_STATS will be executed automatically but it may take some hours until a new table would be analyzed. – o0x258 Jan 16 '15 at 17:51

There is no table or view that is the equivalent for records. You could achieve this by generating dynamic SQL in a PL/SQL script to produce selects against each table and then run each generated SQL using 'execute immediate'. Unfortunately I don't have the time to write up a quick script at the minute as I'm still at work.

  • This is probably the slowest method. If you choose do this, check laurentschneider.com/wordpress/2007/04/…, it could be helpful. – Raj Jan 15 '15 at 17:05
  • @Raj. Thanks for that link. I'll have a play around with that type of method when I get 5 minutes – BriteSponge Jan 16 '15 at 8:55
  • This answer is not correct. Refer to what sjk wrote. – o0x258 Jan 16 '15 at 17:47
  • Not sure why a downvote as what I have said is certainly possible and would satisfy the OP requirement. The answer by sjk does not seem to indicate why my answer is incorrect. – BriteSponge Jan 19 '15 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.