Oracle-L: RE: Phoee, Find views with column name

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Sat, 13 Apr 2002 12:23:18 -0800
Message-ID: <F001.00443EE4.20020413122318@fatcity.com>

If it's only aliases you need to worry about you can get the view text and use dbms_sql.parse and dbms_sql.describe_columns to get the names. However if the column in the view is involved in a function or perhaps used in conjunction with another column, then this method does not work.

There was an admittedly ugly suggestion of" getting the view names from one of the dependencies tables; renaming the table containing the column of interest; creating another table with the same name and structure as the original one but with the column missing, and then checking to see which views became invalid. I wouldn't do that on a production system.

One would think that once a view has been parsed, Oracle would hold that somewhere in memory. I don't know the X$ structures well enough. Perhaps it isn't accessible. Perhaps one needs to dump the SGA.

It looks like the real answer is to write your own parser.

Ian MacGregor
Stanforfd Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Thursday, April 11, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L

This won't work if I alias the column name in the view definition, would it?

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Thursday, April 11, 2002 5:24 PM
To: Multiple recipients of list ORACLE-L

Darn E-mail package. Every once in a while I get a message as I'm deleting one
or more I don't want & one I do want goes as well. Well, I guess that's IBM/Lotus for you.

Anyway, someone asked how to find all the views that include a specific column.
Try the following:

select view_name

      from user_views, user_tab_columns
      where view_name = table_name
        and column_name = '<fill_in_the_blank>';

Dick Goulet

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------