1

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • 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
    Commented Jan 16, 2015 at 17:51
0

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.

Improve this answer
4
  • This is probably the slowest method. If you choose do this, check laurentschneider.com/wordpress/2007/04/…, it could be helpful.
    – Raj
    Commented Jan 15, 2015 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
    Commented Jan 16, 2015 at 8:55
  • This answer is not correct. Refer to what sjk wrote.
    – o0x258
    Commented Jan 16, 2015 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
    Commented Jan 19, 2015 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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