RE: Anyone run into this strange ORA-00904 error ??
I have also seen a case where new line or other weird ASCII charater find they way into the name that could cause this behavior. -Original Message- Sent: Wednesday, December 03, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Sometimes the upper case and lower case get mixed up in the data dictionary. I know that Oracle claims case insensitivity but it happens. Take a look at sys.col$ and sys.obj$. --- Krishna Kakatur <[EMAIL PROTECTED]> wrote: > > Did you check grants on the table? > > This happens sometimes, with some oracle versions, > in the following situation: > > step1: user1 creates tab1 and >grants all to user2. (without grant option) > step2: user2 creates view1 based on tab1 >and grants all to user3. > > Now, when user3 tries to access view1 he gets this > kind of error. > > This can be resolved by issuing the foll sql in > step1 > > GRANT ALL ON tab1 TO user2 WITH ADMIN OPTION; > > > -- > Thanks, > Krishna > > ~~ > NOTICE: This email message is for the sole use of > the intended > recipient(s) and may contain confidential and > privileged > information. Any unauthorized review, use, > disclosure or > distribution is prohibited. If you are not the > intended > recipient, please contact the sender by reply email > and destroy > all copies of the original message. > ~~ > > > > > [EMAIL PROTECTED] wrote: > > > > We started having a weird problem that looks like > some kind of data dictionary corruption. > > > > My first choice is to run catalog / catproc. This > did nothing to resolve the problem. > > > > Why am I able to describe an object, but get > ORA-00904 when I try to select from the table... > > > > SQL> desc ispownre3.individual_names; > > Name Null? > Type > > - > -- > > INTERNAL_IDENTIFIER > NUMBER(12) > > TITLE_CD > NUMBER(3) > > . . . > > > > SQL> select * from ispownre3.individual_names; > > select * from ispownre3.individual_names > > * > > ERROR at line 1: > > ORA-00904: invalid column name > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Krishna Kakatur > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
Sometimes the upper case and lower case get mixed up in the data dictionary. I know that Oracle claims case insensitivity but it happens. Take a look at sys.col$ and sys.obj$. --- Krishna Kakatur <[EMAIL PROTECTED]> wrote: > > Did you check grants on the table? > > This happens sometimes, with some oracle versions, > in the following situation: > > step1: user1 creates tab1 and >grants all to user2. (without grant option) > step2: user2 creates view1 based on tab1 >and grants all to user3. > > Now, when user3 tries to access view1 he gets this > kind of error. > > This can be resolved by issuing the foll sql in > step1 > > GRANT ALL ON tab1 TO user2 WITH ADMIN OPTION; > > > -- > Thanks, > Krishna > > ~~ > NOTICE: This email message is for the sole use of > the intended > recipient(s) and may contain confidential and > privileged > information. Any unauthorized review, use, > disclosure or > distribution is prohibited. If you are not the > intended > recipient, please contact the sender by reply email > and destroy > all copies of the original message. > ~~ > > > > > [EMAIL PROTECTED] wrote: > > > > We started having a weird problem that looks like > some kind of data dictionary corruption. > > > > My first choice is to run catalog / catproc. This > did nothing to resolve the problem. > > > > Why am I able to describe an object, but get > ORA-00904 when I try to select from the table... > > > > SQL> desc ispownre3.individual_names; > > Name Null? > Type > > - > -- > > INTERNAL_IDENTIFIER > NUMBER(12) > > TITLE_CD > NUMBER(3) > > . . . > > > > SQL> select * from ispownre3.individual_names; > > select * from ispownre3.individual_names > > * > > ERROR at line 1: > > ORA-00904: invalid column name > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Krishna Kakatur > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
Title: Message Thanks for all the ideas folks we decided to restore the TEST database instead of pursue so we would have less down time (problem was relatively recent). THEN in the middle of cold backup restore, I started seeing the same thing in my development instance . !!! I got help from a fellow DBA and we managed to track it down, but I was too stressed out from dealing with this all day to respond to let everyone know what happened. Bambi's idea was closet to problem When you have a function-based index, based on a user function and you do a fromuser/touser import, the touser tables that had the function-based index will get ORA-904, until the function-based index is dropped and re-created. According to Oracle, it is supposed to work this way. **I** think they should at least have an import warning. For those of you who wish to test for yourself (on 8.1.7), here is the test case we came up with: == 1 == Create BTU1 user == drop user btu1 cascade; create user btu1 identified by btu1 ; grant connect,resource to btu1; grant query rewrite to btu1; . == 2 == Create table and function-based index === connect btu1/btu1 create or replace package this_pkg IS function f_upper (in1 varchar2 ) return varchar2 deterministic; PRAGMA RESTRICT_REFERENCES( f_upper, WNDS, RNDS, WNPS); end this_pkg; / . create or replace package body this_pkg IS function f_upper ( in1 varchar2 ) return varchar2 is begin return upper(in1); end; end this_pkg; / create table btu1_table ( col1 number, col2 varchar2(30)) / insert into btu1_table values (1, 'hello') / insert into btu1_table values (2, 'goodbye') / create index btu1_index on btu1_table ( substr ( this_pkg.f_upper ( col2 ) ,1,30) ) / . == 3 == export this user == exp dba/passwd file=btu1.dmp owner=btu1 . == 4 == Create 2nd user to import objects into drop user btu2 cascade; create user btu2 identified by btu2 ; grant connect,resource to btu2; grant query rewrite to btu2; . == 5 == Import objects into anothe user == imp dba/passwd file=btu1.dmp fromuser=btu1 touser=btu2 . Export file created by EXPORT:V08.01.07 via conventional path import done in WE8EBCDIC1047 character set and WE8EBCDIC1047 NCHAR character set . importing BTU1's objects into BTU2 . . importing table "BTU1_TABLE" 2 rows imported Import terminated successfully without warnings. . == 6 == Select the newly imported objects and get ORA-904 sqlplus btu2/btu2 SQL> select * from btu1_table; select * from btu1_table * ERROR at line 1: ORA-00904: invalid column name Babette Turner-Underwood work: [EMAIL PROTECTED] home: [EMAIL PROTECTED] 954-3752 (Mon - Fri 7am - 3pm) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bellow, BambiSent: 2003-12-02 6:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Anyone run into this strange ORA-00904 error ?? I have run into this problem in the past in two different situations. The first is where there is a reserved word somewhere that you managed to sneak through somehow... the second is where there is an internal view created for some reason that barfs... e.g., VW_NSO_1.$NSO_COL_1. Check your alert log. If it's the latter, it's a bug. If it's the former, try specifying the columns and adding a column alias where the column could conceivably be a reserved word. You didn't give us all the column names, but try something like this... select internal_identifier int_id, title_cd,nonissue, nonissue2, boy_this_could_be_a_problem a,nonissue3... from your_table where x=y HTH, Bambi. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
RE: Anyone run into this strange ORA-00904 error ??
Title: Message I have run into this problem in the past in two different situations. The first is where there is a reserved word somewhere that you managed to sneak through somehow... the second is where there is an internal view created for some reason that barfs... e.g., VW_NSO_1.$NSO_COL_1. Check your alert log. If it's the latter, it's a bug. If it's the former, try specifying the columns and adding a column alias where the column could conceivably be a reserved word. You didn't give us all the column names, but try something like this... select internal_identifier int_id, title_cd,nonissue, nonissue2, boy_this_could_be_a_problem a,nonissue3... from your_table where x=y HTH, Bambi. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
Re: Re: Anyone run into this strange ORA-00904 error ??
i believe column privileges are only for dml. views are supposed to filter out columns for selects. i could be wrong. > > From: Krishna Kakatur <[EMAIL PROTECTED]> > Date: 2003/12/02 Tue PM 12:34:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Anyone run into this strange ORA-00904 error ?? > > > Did you check grants on the table? > > This happens sometimes, with some oracle versions, > in the following situation: > > step1: user1 creates tab1 and >grants all to user2. (without grant option) > step2: user2 creates view1 based on tab1 >and grants all to user3. > > Now, when user3 tries to access view1 he gets this kind of error. > > This can be resolved by issuing the foll sql in step1 > > GRANT ALL ON tab1 TO user2 WITH ADMIN OPTION; > > > -- > Thanks, > Krishna > > ~~ > NOTICE: This email message is for the sole use of the intended > recipient(s) and may contain confidential and privileged > information. Any unauthorized review, use, disclosure or > distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply email and destroy > all copies of the original message. > ~~ > > > > > [EMAIL PROTECTED] wrote: > > > > We started having a weird problem that looks like some kind of data dictionary > > corruption. > > > > My first choice is to run catalog / catproc. This did nothing to resolve the > > problem. > > > > Why am I able to describe an object, but get ORA-00904 when I try to select from > > the table... > > > > SQL> desc ispownre3.individual_names; > > Name Null?Type > > - -- > > INTERNAL_IDENTIFIERNUMBER(12) > > TITLE_CD NUMBER(3) > > . . . > > > > SQL> select * from ispownre3.individual_names; > > select * from ispownre3.individual_names > > * > > ERROR at line 1: > > ORA-00904: invalid column name > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Krishna Kakatur > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
Babette, Version? Are you doing this through sqlplus and if so, is the client installation a different version than the db? Rachel --- [EMAIL PROTECTED] wrote: > We started having a weird problem that looks like some kind of data > dictionary corruption. > > My first choice is to run catalog / catproc. This did nothing to > resolve the problem. > > Why am I able to describe an object, but get ORA-00904 when I try to > select from the table... > > > SQL> desc ispownre3.individual_names; > Name Null?Type > - > -- > INTERNAL_IDENTIFIERNUMBER(12) > TITLE_CD NUMBER(3) > . . . > > SQL> select * from ispownre3.individual_names; > select * from ispownre3.individual_names > * > ERROR at line 1: > ORA-00904: invalid column name > > __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
It this a view ? -- Denny Koovakattu Quoting Tanel Poder <[EMAIL PROTECTED]>: > Anyone run into this strange ORA-00904 error ??What happens if you select 1 > or 'X' from the table? > > Tanel. > > - Original Message - > From: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L > Sent: Tuesday, December 02, 2003 6:34 PM > Subject: Anyone run into this strange ORA-00904 error ?? > > > We started having a weird problem that looks like some kind of data > dictionary corruption. > > My first choice is to run catalog / catproc. This did nothing to resolve > the problem. > > Why am I able to describe an object, but get ORA-00904 when I try to select > from the table... > > > > SQL> desc ispownre3.individual_names; >Name Null?Type >- -- >INTERNAL_IDENTIFIERNUMBER(12) >TITLE_CD NUMBER(3) > . . . > > SQL> select * from ispownre3.individual_names; > select * from ispownre3.individual_names >* > ERROR at line 1: > ORA-00904: invalid column name > This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
Did you check grants on the table? This happens sometimes, with some oracle versions, in the following situation: step1: user1 creates tab1 and grants all to user2. (without grant option) step2: user2 creates view1 based on tab1 and grants all to user3. Now, when user3 tries to access view1 he gets this kind of error. This can be resolved by issuing the foll sql in step1 GRANT ALL ON tab1 TO user2 WITH ADMIN OPTION; -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ > [EMAIL PROTECTED] wrote: > > We started having a weird problem that looks like some kind of data dictionary > corruption. > > My first choice is to run catalog / catproc. This did nothing to resolve the problem. > > Why am I able to describe an object, but get ORA-00904 when I try to select from the > table... > > SQL> desc ispownre3.individual_names; > Name Null?Type > - -- > INTERNAL_IDENTIFIERNUMBER(12) > TITLE_CD NUMBER(3) > . . . > > SQL> select * from ispownre3.individual_names; > select * from ispownre3.individual_names > * > ERROR at line 1: > ORA-00904: invalid column name -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Anyone run into this strange ORA-00904 error ??
Babette, Can you list the describe of the whole table? Daniel FInk [EMAIL PROTECTED] wrote: We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
Re: Anyone run into this strange ORA-00904 error ??
Title: Anyone run into this strange ORA-00904 error ?? What happens if you select 1 or 'X' from the table? Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 02, 2003 6:34 PM Subject: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
RE: Anyone run into this strange ORA-00904 error ??
Title: Message Would an export/drop/import work for you? Abraham -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
Anyone run into this strange ORA-00904 error ??
Title: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL> desc ispownre3.individual_names; Name Null? Type - -- INTERNAL_IDENTIFIER NUMBER(12) TITLE_CD NUMBER(3) . . . SQL> select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name
