RE: strange error on DBMS_STATS
believe me, I'm talking to the wrong person if I want to get the right answers. Okay, we fixed it but I'm not sure why it happened. I re-granted s/i/u/d on all the tables owned by the schema_owner directly to the user running the gather_stats it's running properly. Reconstruction of events: 1) sql*loader direct path load loaded duplicate rows into the table, causing the pk index to become "unusable" 2) script to fix the duplicates: a) attempt to enable pk index, with exceptions into exceptions table b) delete the duplicate rows c) re-enable the pk (this caused other problems, like putting it in the wrong tablespace but not this problem) and then we couldn't run the stats weird --- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote: > I thought people talked to themselves so they could get all the right > answers. > > -Original Message- > Sent: Thursday, December 19, 2002 3:16 PM > To: Multiple recipients of list ORACLE-L > > > you and Raj had the same thought. but no, the index is owned by the > table owner > > got access so I could run the gather with a 10046 trace. so of course > it's not failing as yet. sigh. and my boss wonders why I talk to > myself > > > --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > > Rachel Carmichael wrote: > > > > > > We are not changing passwords, so I am presuming that this > involves > > > (somehow) a change of username. According to the package header, > it > > > will throw an ORA-2 if there are insufficent privileges. We > > rebuilt > > > an index yesterday but did NOT change or add any table. > > > > > > I had this happen once before, on a different database, never > > solved > > > it, but substituted dbms_utility.analyze_schema which worked. I > can > > do > > > the same thing this time but I'd prefer to solve it. > > > > > > Anyone ever see anything like this before? > > > > > > oh yeah 9.2.0.1 on Solaris 8 > > > > > > Rachel > > > > > > > I have not seen it but could the rebuilt index now have a different > > owner ? > > -- > > Regards, > > > > Stephane Faroult > > Oriole Software > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Stephane Faroult > > 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). > > > > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > 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). > > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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: strange error on DBMS_STATS
Title: RE: strange error on DBMS_STATS I thought people talked to themselves so they could get all the right answers. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 3:16 PM To: Multiple recipients of list ORACLE-L Subject: Re: strange error on DBMS_STATS you and Raj had the same thought. but no, the index is owned by the table owner got access so I could run the gather with a 10046 trace. so of course it's not failing as yet. sigh. and my boss wonders why I talk to myself --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > Rachel Carmichael wrote: > > > > We are not changing passwords, so I am presuming that this involves > > (somehow) a change of username. According to the package header, it > > will throw an ORA-2 if there are insufficent privileges. We > rebuilt > > an index yesterday but did NOT change or add any table. > > > > I had this happen once before, on a different database, never > solved > > it, but substituted dbms_utility.analyze_schema which worked. I can > do > > the same thing this time but I'd prefer to solve it. > > > > Anyone ever see anything like this before? > > > > oh yeah 9.2.0.1 on Solaris 8 > > > > Rachel > > > > I have not seen it but could the rebuilt index now have a different > owner ? > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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: strange error on DBMS_STATS
you and Raj had the same thought. but no, the index is owned by the table owner got access so I could run the gather with a 10046 trace. so of course it's not failing as yet. sigh. and my boss wonders why I talk to myself --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > Rachel Carmichael wrote: > > > > We are not changing passwords, so I am presuming that this involves > > (somehow) a change of username. According to the package header, it > > will throw an ORA-2 if there are insufficent privileges. We > rebuilt > > an index yesterday but did NOT change or add any table. > > > > I had this happen once before, on a different database, never > solved > > it, but substituted dbms_utility.analyze_schema which worked. I can > do > > the same thing this time but I'd prefer to solve it. > > > > Anyone ever see anything like this before? > > > > oh yeah 9.2.0.1 on Solaris 8 > > > > Rachel > > > > I have not seen it but could the rebuilt index now have a different > owner ? > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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: strange error on DBMS_STATS
good question. It should be but I can check that. I don't know that it's failing on the index though. I'm going to use Tim's "hammer" and see if I can find out --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Probably wrong question ... but was rebuilt index still owned by > schema > owner? > > 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! > > *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.*1 > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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: strange error on DBMS_STATS
Rachel Carmichael wrote: > > We are not changing passwords, so I am presuming that this involves > (somehow) a change of username. According to the package header, it > will throw an ORA-2 if there are insufficent privileges. We rebuilt > an index yesterday but did NOT change or add any table. > > I had this happen once before, on a different database, never solved > it, but substituted dbms_utility.analyze_schema which worked. I can do > the same thing this time but I'd prefer to solve it. > > Anyone ever see anything like this before? > > oh yeah 9.2.0.1 on Solaris 8 > > Rachel > I have not seen it but could the rebuilt index now have a different owner ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: strange error on DBMS_STATS
I love the hammer. The problem is, this is the production database, I'm the development DBA. Until there are production problems. I have no access to the system, and I have no access to the routine that runs dbms_stats. Other than that, I can do anything :) I'll see if I can get this inserted into the cron job, although they aren't going to be happy to have it fail again tonight. tough. I think I'll quit and go be a ski bum. Which is funnier than it sounds as I don't know how to ski and ain't about to learn. --- Tim Gorman <[EMAIL PROTECTED]> wrote: > ...I've got this hammer called "SQL Trace" and just about > every problem looks like a nail... > > Good thing is, this'll probably resolve it for you. Can you > run this in the same session prior to running the > GATHER_SCHEMA_STATS? > >alter session set max_dump_file_size = unlimited; >alter session set events '10046 trace name context > forever, level 12'; > > If you're pressed for space in USER_DUMP_DEST, you might > want to run the trace at "level 4" instead of "level 12", to > dump the bind variable values only... > > This will generate a trace file which, embedded within it, > should contain some indication of exactly where the > ORA-01031 error is being thrown. > > Look within the raw ".trc" (near the bottom of the file) for > the phrase "err=" (it should say "err=1031" or "err=-1031", > I forget which). Note the cursor# for that line and then > search upwards for the phrase "PARSING IN CURSOR #nnn" to > see the SQL text. Then, from the site of the error message, > search again upwards for the phrase "BIND #nnn" to find the > dump of bind-variable values for the most recent call. > > Seeing as how the trace will dump all recursive SQL called > in GATHER_SCHEMA_STATS as well as their bind-variable > values, we might be able to pin-point exactly which item it > is failing upon... > > > > > I'm running dbms_stats.gather_schema_stats, the account > > running it is NOT the schema owner, but DOES have the > > "analyze any" privilege and has read/write access to all > > the tables in the schema. We are not trying to gather > > stats into our own tables, just want the stats refreshed > > after the (massive) loads into the DW tables. > > > > This has been running fine, then all of a sudden last > > night we started getting ORA-1031 ("insufficient > > privileges") errors. Now, according to the docs: > > > > ORA-01031 insufficient privileges > > > > Cause: An attempt was made to change the current > > username or password without the appropriate privilege. > > This error also occurs if attempting to install a database > > without the necessary operating system privileges. > > > > > > We are not changing passwords, so I am presuming that this > > involves (somehow) a change of username. According to the > > package header, it will throw an ORA-2 if there are > > insufficent privileges. We rebuilt an index yesterday but > > did NOT change or add any table. > > I had this happen once before, on a different database, > > never solved it, but substituted > > dbms_utility.analyze_schema which worked. I can do the > > same thing this time but I'd prefer to solve it. > > Anyone ever see anything like this before? > > > > oh yeah 9.2.0.1 on Solaris 8 > > > > Rachel > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tim Gorman > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Si
RE: strange error on DBMS_STATS
Yes, but it turned out to be privs (or lack thereof) to run the stats or associated queries in a procedure, rather than privs to do the analyze itself. Do you have auditing turned on? That's usually the first place I check to see what objects changed since the last time it worked. GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 19, 2002 10:39 AM > To: Multiple recipients of list ORACLE-L > Subject: strange error on DBMS_STATS > > > I'm running dbms_stats.gather_schema_stats, the account running it is > NOT the schema owner, but DOES have the "analyze any" > privilege and has > read/write access to all the tables in the schema. We are not > trying to > gather stats into our own tables, just want the stats refreshed after > the (massive) loads into the DW tables. > > This has been running fine, then all of a sudden last night we started > getting ORA-1031 ("insufficient privileges") errors. Now, according to > the docs: > > ORA-01031 insufficient privileges > > Cause: An attempt was made to change the current username or > password without the appropriate privilege. This error also occurs if > attempting to install a database without the necessary > operating system > privileges. > > > We are not changing passwords, so I am presuming that this involves > (somehow) a change of username. According to the package header, it > will throw an ORA-2 if there are insufficent privileges. > We rebuilt > an index yesterday but did NOT change or add any table. > > I had this happen once before, on a different database, never solved > it, but substituted dbms_utility.analyze_schema which worked. I can do > the same thing this time but I'd prefer to solve it. > > Anyone ever see anything like this before? > > oh yeah 9.2.0.1 on Solaris 8 > > Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: strange error on DBMS_STATS
...I've got this hammer called "SQL Trace" and just about every problem looks like a nail... Good thing is, this'll probably resolve it for you. Can you run this in the same session prior to running the GATHER_SCHEMA_STATS? alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever, level 12'; If you're pressed for space in USER_DUMP_DEST, you might want to run the trace at "level 4" instead of "level 12", to dump the bind variable values only... This will generate a trace file which, embedded within it, should contain some indication of exactly where the ORA-01031 error is being thrown. Look within the raw ".trc" (near the bottom of the file) for the phrase "err=" (it should say "err=1031" or "err=-1031", I forget which). Note the cursor# for that line and then search upwards for the phrase "PARSING IN CURSOR #nnn" to see the SQL text. Then, from the site of the error message, search again upwards for the phrase "BIND #nnn" to find the dump of bind-variable values for the most recent call. Seeing as how the trace will dump all recursive SQL called in GATHER_SCHEMA_STATS as well as their bind-variable values, we might be able to pin-point exactly which item it is failing upon... > I'm running dbms_stats.gather_schema_stats, the account > running it is NOT the schema owner, but DOES have the > "analyze any" privilege and has read/write access to all > the tables in the schema. We are not trying to gather > stats into our own tables, just want the stats refreshed > after the (massive) loads into the DW tables. > > This has been running fine, then all of a sudden last > night we started getting ORA-1031 ("insufficient > privileges") errors. Now, according to the docs: > > ORA-01031 insufficient privileges > > Cause: An attempt was made to change the current > username or password without the appropriate privilege. > This error also occurs if attempting to install a database > without the necessary operating system privileges. > > > We are not changing passwords, so I am presuming that this > involves (somehow) a change of username. According to the > package header, it will throw an ORA-2 if there are > insufficent privileges. We rebuilt an index yesterday but > did NOT change or add any table. > I had this happen once before, on a different database, > never solved it, but substituted > dbms_utility.analyze_schema which worked. I can do the > same thing this time but I'd prefer to solve it. > Anyone ever see anything like this before? > > oh yeah 9.2.0.1 on Solaris 8 > > Rachel > > > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: strange error on DBMS_STATS
Title: RE: strange error on DBMS_STATS Probably wrong question ... but was rebuilt index still owned by schema owner? 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! *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.*1
RE: strange error on DBMS_STATS
this is being run in sqlplus as execute dbms_stats.gather_schema_stats If I didn't run it (on another schema) within the same sql script, I'd think it was that I didn't have privs on dbms_stats. I'll keep looking --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Yes, but it turned out to be privs (or lack thereof) to run the stats > or > associated queries in a procedure, rather than privs to do the > analyze > itself. Do you have auditing turned on? That's usually the first > place I > check to see what objects changed since the last time it worked. > > GL! > > Rich > > > Rich Jesse System/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, Sussex, > WI USA > > > > -Original Message- > > From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, December 19, 2002 10:39 AM > > To: Multiple recipients of list ORACLE-L > > Subject: strange error on DBMS_STATS > > > > > > I'm running dbms_stats.gather_schema_stats, the account running it > is > > NOT the schema owner, but DOES have the "analyze any" > > privilege and has > > read/write access to all the tables in the schema. We are not > > trying to > > gather stats into our own tables, just want the stats refreshed > after > > the (massive) loads into the DW tables. > > > > This has been running fine, then all of a sudden last night we > started > > getting ORA-1031 ("insufficient privileges") errors. Now, according > to > > the docs: > > > > ORA-01031 insufficient privileges > > > > Cause: An attempt was made to change the current username or > > password without the appropriate privilege. This error also occurs > if > > attempting to install a database without the necessary > > operating system > > privileges. > > > > > > We are not changing passwords, so I am presuming that this involves > > (somehow) a change of username. According to the package header, it > > will throw an ORA-2 if there are insufficent privileges. > > We rebuilt > > an index yesterday but did NOT change or add any table. > > > > I had this happen once before, on a different database, never > solved > > it, but substituted dbms_utility.analyze_schema which worked. I can > do > > the same thing this time but I'd prefer to solve it. > > > > Anyone ever see anything like this before? > > > > oh yeah 9.2.0.1 on Solaris 8 > > > > Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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).