Re: importing statistics

On Jan 29, 11:57 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
>
> On Jan 29, 10:35 am, "Ben" <bal..._at_comcast.net> wrote:
>
> > 9.2.0.5 Ent Ed AIX5L
>
> > I am trying to import statistics using the
> > dbms_stats.import_schema_stats procedure from one db/schema to
> > another. The procedure died with an 'ORA-20000 Schema TRDTA does not
> > exist or insufficient privileges' the schema does exist and I'm logged
> > in as sysdba.Privileges granted through a role are not 'visible' from most
> procedures/funcitons/packages as they are, by default, using the
> privileges directly granted to the owner and not the privileges
> granted (either directly or through a role) of the current user.
> Also, you can prove this schema does exist in the database you're
> attempting to move statistics to?
>
> > In trying to find what would cause this, the only thing
> > I see ( and fairly certain this is the cause ) is that some of the
> > indexes are not named the same between prod and test environments.
> > Would that cause my issue?No. Why would it be? Object names have no bearing on user privileges
> and this is clearly a privilege issue.
>
> > In other words the _PK indexes are called
> > _0 on some of my tables and vice versa. I'm not sure how this happened
> > as it has been that way since I took over this database and I haven't
> > researched renaming them yet.Don't start because of this problem as the index names have nothing to
> do with it.
>
> > Would the best ( and simplest ) thing to do be 'ALTER INDEX ...
> > RENAME ...' ?Again, why would you think this? There is nothing about object names
> in the error message you posted, and everything about granted
> privileges, especially since your procedure is likely using the
> default of running under the owner's directly granted privileges. You
> may be connected as SYSDBA but those privileges are granted, for the
> most part, by roles, not directly, and will not be available to such
> procedures (as I explained at the beginning). I would verify the
> TRDTA schema exists and that you, as sysdba, have access to it through
> direct grants, and not roles.
>
>
>
> > ThanksDavid FItzjarrell

I have found that not all standard error messages are exact. I am not a moron, yes I can prove that the schema exists, am I going to? no.
After the error, I went to see if any of the objects had the new stats that I tried to import, and yes it made it to a certain index in the schema and that index just so happened to be named differently than it was named in production. I renamed that index and then tried to import the statistics again, guess what, it made it past that index and on to the next index that was named differently. Hence, I believe that the object/index naming issue is what is causing my problems. Can anyone else verify that they have seen this behavior? What would be the best way to correct the issue? Are there any negatives with 'ALTER INDEX .... RENAME TO ....' ? Received on Mon Jan 29 2007 - 11:08:40 CST