Avatar of Natavia Finnie
Natavia Finnie
Flag for United States of America asked on

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

Avatar of undefined
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Natavia Finnie

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)
Natavia Finnie

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.