select the table_name, column_name and the value from the column from ALL schemas from an oracle DB

Natavia Finnie
Natavia Finnie used Ask the Experts™
on
I need to select the table_name, column_name and the value from the column from ALL schemas from an oracle DB
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Mark GeerlingsDatabase Administrator

Commented:
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 PachotOracle DBA

Commented:
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 GeerlingsDatabase Administrator

Commented:
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#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?
Natavia FinnieSenior Analyst Technical Focus Applications

Author

Commented:
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)
Senior Analyst Technical Focus Applications
Commented:
I modified a query that @slightwv help me with previously:
with cte as (
  select
  xmlelement("root",
    xmlagg(
        dbms_xmlgen.getxmltype('select ''' || owner || ''' schema, THE_COLUMN_NAME as is_enabled from ' ||
          owner || '.' || table_name
        )
    )
  ) myxml
  from all_tables
  where table_name = 'THE_TABLE_NAME' 
)
select schema, is_iw4_enabled
from cte, xmltable(
      '/root/ROWSET/ROW'
      passing myxml
      columns
        schema        varchar2(30)  path 'SCHEMA',
				is_iw4_enabled			varchar2(1)		path 'IS_ENABLED'	);

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial