Link to home
Create AccountLog in
Avatar of Natavia Finnie
Natavia FinnieFlag 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
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

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?
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?
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?
Avatar of Natavia Finnie

ASKER

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)
ASKER CERTIFIED SOLUTION
Avatar of Natavia Finnie
Natavia Finnie
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account