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

Last updated on APRIL 17, 2023

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

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

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