My Oracle Support Banner

How To Transfer Statistics To Different Schemas and Tables and Indexes and Partition and Columns (Doc ID 2701368.1)

Last updated on SEPTEMBER 24, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 19.4.0.0.0 and later
Information in this document applies to any platform.

Goal

The purpose of this document is to explain how to copy statistics among different schemas, tables, indexes, partition and columns by DBMS_STATS package.
It can be used to copy statistics from one table or index to another one with the similar data volumn and distribution to avoid unnecessary statistics gathering.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Create table to hold statistics.
 Export source statistics by DBMS_STATS.EXPORT_XXXX_STATS.
 Export the table holding the statistics
 Import the table holding statistics into the target schema of target environment.
 Update source schema, table, index, column, partition name to target schema, table, index, column, partition name.
 Import the statstics to dictionary.
 Confirm the statistics from dictionary views.
 Copy statistics by DBMS_STATS.GET_XXX_STATS and DBMS_STATS.SET_XXX_STATS when both source and target in the same database
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.