Re: importing statistics

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.

>
> Thanks

David FItzjarrell Received on Mon Jan 29 2007 - 10:57:10 CST