RE: Phoee, Find views with column name
I'm not sure if probing the sga would work - its common in systems that do a lot of parsing to see many calls to view$ in trace files (for the same view) - leading to the hypothesis that view defn's are not cached. hth connor --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] -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 JamadagniMIS, 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Phoee, Find views with column name
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 [EMAIL PROTECTED] -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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Phoee, Find views with column name
xxx_DEPENDENCIES would probably be a better starting point. hth connor --- [EMAIL PROTECTED] wrote: 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Phoee, Find views with column name
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Phoee, Find views with column name
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
