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).
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
...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
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 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. 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
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
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
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
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).
