Re: importing statistics

Wolfgang Breitling wrote:
>
> "Ben" <balvey_at_comcast.net> wrote in news:1170088542.107218.152980
> @v45g2000cwv.googlegroups.com:
>
> > 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. 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? 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.
> > Would the best ( and simplest ) thing to do be 'ALTER INDEX ...
> > RENAME ...' ?
> >
>
> Yes, missing objects will cause that. And an index that has a different
> name is of course missing in the target database. Same can happen for a
> table that is present in the source but not in the target. Or a column
> that has been added to a table in the source but not in the target.
>
> One way to resolve this is to add the missing object - e.g. by
> synchrinizing the index names. The other is to correct the discrepancies
> in the stattab table by renaming or removing the superfluous entries.

A similar pain is function based indexes...if you created func-index-A then B in database1, but did it as func-index-B then A in database2, then the names of the hidden columns can also be out of alignment.

That means you'll now have to hunt through xxx_tab_cols to map the appropriate column names in stattab...Its all good fun!

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------