Home > Oracle in general > How to copy schema stats from one schema to another schema

How to copy schema stats from one schema to another schema

The following post is a short description how to copy schema statistics from one schema to another. Basically there are two methods for doing this:

  • using DBMS_STATS or
  • using Data Pump

This is especially useful when doing large data transfers between either schemas or databases and statistics are needed fast. Remember that data density, row chaining or index cluster factor changed due to the data import and index rebuilds.

Method 1: Using DBMS_STATS

Extract the statistics

First we need to login as schema owner which from which we want to extract the statistics. In the follwing example the schema owner and schema is is “SCOTT”. Logged in as scott we create a table holding the statistic information:

exec dbms_stats.create_stat_table('SCOTT','STATISTICS');

The arguments required are: <SCHEMANAME>, <TABLENAME>.

To extract the statistics run:

begin
dbms_stats.export_schema_stats(ownname => 'SCOTT',stattab => 'STATISTICS');
end;
/

Transfer the statistics

Now all statistics from that particular schema are stored in the table named “STATISTICS”. The table must be transfered to the destination schema (in the same or another database). This can be done for instance with exp/imp or via a database link.

For exporting the statistics with export/import:

exp system/manager file=stats_table.dmp log=stats_table.log tables=SCOTT.STATISTICS

Importing is done with:

imp system/manager file=stats_table.dmp log=stats_table_imp.log 
 fromuser=<source user name> touser=<destination user name>

Import the statistics

Import into another database but with the same schema name

If the schema name in the destination database is identical to the schema name in the source database (“SCOTT” in source and “SCOTT” in destination) the stats can be imported with:

begin
exec dbms_stats.import_schema_stats(ownname => 'SCOTT',stattab => 'STATISTICS');
 end;
 /

Import into another or the same database but with a different schema name

If the name of the destination schema is changed you have to adjust your statistic table before importing the stats. In the following example we import the stats into a schema called “BLAKE”.

In the stats table the column named “C5” contains the name of the schema the statistics were exported from and will be imported to. If you want to import the stats into another schema you have to modify this column first:

update statistics set c5='BLAKE';

and afterwards import the statistics (loggen in as user BLAKE or SYS):

begin
exec dbms_stats.import_schema_stats(ownname => 'SCOTT',stattab => 'STATISTICS');
 end;
 /

Method 2: Using data pump

Beside using DBMS_STATS you can use data pump to transfer table and index statistics from one database to another.

When doint a normal export/import table and index statistics are transfered along with the data. If you want to transfer only the statistics you can use the following call:

impdp system/manager dumpfile=<directory>:<file> logfile=<directory>:<logfile name> 
remap_schema=<source>:<destination> remap_tablespace=<source>:<destination> 
schemas=<name of source schema(s)> include=STATISTICS
Categories: Oracle in general Tags:
  1. Dan Grotjan
    June 20th, 2012 at 16:56 | #1

    Thanks for the great post. The method you presented for importing statistics into another schema within the same database was just what I needed, and I don’t believe you’ll ever find that method in Oracle doco. (Nor will the first-line Oracle support ever suggest it)!

  2. Shaji
    November 15th, 2012 at 13:16 | #2

    Thanks ..it helped..!!

  3. Mayank
    February 8th, 2013 at 13:27 | #3

    Can you please suggest if Stats collection of Schema/Tables can automated with any Oracle nightly job in 11.2 release
    Also when i should collect SYSTEM stats

    Thanks for great post
    Mayank

    • Ronny Egner
      March 14th, 2013 at 18:19 | #4

      You should collect stats with DBMS_STATS.GATHER_DICTIONARY_STATS.

  4. July 18th, 2013 at 03:04 | #5

    Very nice post, Usually when we import stats using impdp as suggested in Method 2. It doesn’t work or give some errors. How to resolve them ?

    In the same way as written in this post DBA can also move table stats from one database to another database.

    http://www.dbas-oracle.com/2013/07/Move-Table-Schema-Statistics-to-Different-Database-using-DBMSSTATS.html

  5. eg
    February 12th, 2015 at 10:07 | #6

    Using data pump to import statistics should be using “include=statistics” instead of “contents=statistics”.

    • Ronny Egner
      February 17th, 2015 at 14:38 | #7

      Correct. Changed. Thanks.

  1. November 3rd, 2009 at 15:00 | #1