Jared,
Yes, DBMS_STATS can be used to move statistics from
one db to another for the SAME table/index. It's
great for moving prod stats into a dev db for sql
tuning. You can also use it to set specific values
(e.g. to set NUM_ROWS to 1000000).
You might want to check out the following doc on
metalink:
Note: 117203.1 "How to Use DBMS_STATS to Move
Statistics to a Different Database"
One caveat, it was not designed to copy stats to a
different schema owner. You won't get an error
running the procedure, it just won't reset any stats.
You can get around this by hacking the stats table
generated by DBMS_STATS and changing the username
column to the new schema owner. The column names are
rather cryptic, but if I remember it's C5.
HTH,
- Anita
- Jared Still <jkstill_at_cybcon.com> wrote:
> On Friday 25 May 2001 13:46,
> Chaim.Katz_at_Completions.Bombardier.com wrote:
> > I have no experience with this, but I think the
> package DBSTATS allows you
> > to export stats to a table and then import those
> stats back into the data
> > dictionary. I think outline perserves a query plan
> but ignores the dd
> > stats.
> >
>
> You're referring to the DBMS_STATS package. After
> spending a few minutes
> perusing the docs, it's unclear whether this can be
> used for recreating
> statistics on another database. The docs refer to
> using CREATE OUTLINE
> for this.
>
>
> Jared
>
> > Chaim
> >
> >
> >
> >
> > Jared Still <jkstill_at_cybcon.com> on 05/25/2001
> 03:32:54 PM
> >
> > Please respond to ORACLE-L_at_fatcity.com
> >
> > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > cc: (bcc: Chaim Katz/Completions/Bombardier)
> >
> > On Friday 25 May 2001 01:05, Saurabh Sharma wrote:
> > > i feel this is at the full database export
> level.
> > > once u export ur database, u can import it as it
> is into another database
> > > and it'll create allthe required definitions for
> u.
> > > the statistics are exported with the tablespace
> and table definitions.
> > >
> > > hope i'm sure. if not pls correct.
> >
> > OK, not correct. Statistics are not exported,
> check the manual.
> >
> > EXP will recalculate statistics, but doesn't
> export them. At least
> > I can find no indication of it.
> >
> > You can use Oracle's plan stability feature
> however via CREATE OUTLINE.
> >
> > Jared
> >
> > > saurabh
> > > ----- Original Message -----
> > > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > Sent: Friday, May 25, 2001 3:48 AM
> > >
> > > > Hi all,
> > > > According to the 8i utilities manual I should
> be able to export a
> > > > tables' statistics
> > > > from one database, then import those
> statistics into another database.
> > > > I have tried various combinations of export
> and import scripts but
> > > > nothing seems to
> > > > work. My table has none of the restrictions
> described in the utilities
> > > > manual that
> > > > would prevent the statistics from being
> exported and then imported.
> > > > Can this export/import of statistics be done
> at a single table level or
> > > > does it have
> > > > to be a full database export? If not at the
> table level can it be done
> > > > at the schema
> > > > level?
> > > >
> > > > Thanks everyone,
> > > > Gary Bonner
> > > > bonnergj_at_songs.sce.com
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > > > --
> > > > Author:
> > > > INET: bonnergj_at_songs.sce.com
> > > >
> > > > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > > > San Diego, California -- Public
> Internet access / Mailing Lists
> > > >
>
> > > > To REMOVE yourself from this mailing list,
> send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (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.com
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (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.com
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (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! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
Received on Fri May 25 2001 - 19:17:39 CDT
