My Oracle Support Banner

How to Transfer Stored Outlines from One Database to Another (9i and above) (Doc ID 728647.1)

Last updated on OCTOBER 31, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.4 [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

This document shows you how to export Stored Outlines from one database and import them on another in Oracle9i and above.

For Oracle8i please use:

<Note:102311.1> How to Transfer Stored Outlines from One Database to Another (8i)

A typical usage scenario is when queries are getting a sub-optimal execution plan in a production database PROD, but are performing well in a similarly configured test database QA. Here it is possible to force each query on PROD to use the same execution plan as on QA via a Stored Outline. The steps are:

  1. create Stored Outlines for the queries on QA
  2. export the Stored Outlines using the EXP utility
  3. transfer the dumpfile to the production system
  4. import the Stored Outlines on PROD using the IMP utility
  5. enable them for use thus allowing the queries on PROD to use the same plan as on QA

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
 1. Create Stored Outlines for the queries on QA
 2. Export the Stored Outline(s) using the EXP utility
 3. Transfer the dumpfile
 4. Import the Stored Outlines using the IMP utility
 5. Enable the Stored Outlines for use
 6. For 10g and higher, datapump can be used
References

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