Re: Exporting and Importing table statistics

Anita,

Thanks for clearing that up.

I had seemed to recall that this was possible when someone asked about it, but all I could recall and/or find was CREATE OUTLINE.

Probably one of those things I learned in 8i DBA class and forgot because I haven't used it.

Jared

On Friday 25 May 2001 16:52, A. Bardeen wrote:
> 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: 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).