select the table_name, column_name and the value from the column from ALL schemas from an oracle DB
I need to select the table_name, column_name and the value from the column from ALL schemas from an oracle DB
Oracle DatabaseSQL
Last Comment
Natavia Finnie
8/22/2022 - Mon
Mark Geerlings
1. Is this a one-time task, or will this be an ongoing need?
2. Is this just for one, constant value? Or will the value you are looking for change over time?
3. Is the number of schemas fairly static, or do you need to dynamically accommodate new schemas?
Franck Pachot
So, you want to read the whole database and return one row per column value in any table?
I would severely question the requirement for that. I see no efficient way to do that. And how do you handle the different datatypes?
Mark Geerlings
I agree that this requirement looks very unusual (but it does look very similar to a previous question from this user (https://www.experts-exchange.com/questions/29163904/Total-count-of-a-field-from-all-schemas-grouped-my-month.html?anchorAnswerId=42985252#a42985252). There may not be an efficient way to do this in Oracle. We do need some more information in order to suggest a possible solution, in addition to the three questions I posted earlier:
4. This question says "ALL schemas" but that is almost certainly an exaggeration. I'm sure that the SYS and SYSTEM schemas don't need to be included in this. And there are likely some other Oracle-supplied schemas that should also be excluded.
5. What is/are the table_name(s) that should be returned? Is this just one table per schema?
6. What is/are the column_name(s) that should be returned? Is this just one column per table?
1) this is a one-time task
2) The value is either 'Y' or 'N'
3 and 4) the schemas are dynamic and should not include the SYS or SYSTEM schemas
5 and 6) It is one table per schema and on particular column per table per schema (SCHEMA_NAME.TABLE_NAME.IS_ENHANCED)
2. Is this just for one, constant value? Or will the value you are looking for change over time?
3. Is the number of schemas fairly static, or do you need to dynamically accommodate new schemas?