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