Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson


Moving OUTLINES from One DB to Another

A user may want to copy OUTLINEs from one database to another, for example, to copy the outlines of an application from a test database to a production database. This can easily be done utilizing Oracle’s export and import routines.


Once the optimization for an application is achieved in a test database, you can move the outlines created and stored for the application in a production database. Instead of recreating these outlines in the production database using the CREATE OUTLINE ...  FOR CATEGORY command for each optimized query of the application, export the outlines for the specified category from the test database, and then import these in the production database.


  1. The outlines for all categories are inserted in the OL$ and OL$HINTS tables owned by OUTLN schema.

  2. The OUTLN schema, OL$ and OL$HINTS tables are created during the database creation while sql.bsq is executed:

   create user outln identified by outln


   grant connect to outln


   grant resource to outln


   grant execute any procedure to outln


   create table outln.ol$


    ol_name            varchar2(30),                /* named is potentially generated */

    sql_text          long,                     /* the SQL stmt being outlined */

    textlen           number,                                          /* length of SQL stmt */

    signature           raw(16),                                    /* signature of sql_text */

    hash_value        number,                         /* KGL's calculated hash value */

    category          varchar2(30),                              /* category name */

    version           varchar2(64),        /* db version @ outline creation */

    creator           varchar2(30),       /* user from whom outline created */

    timestamp         date,                                 /* time of creation */

    flags             number,             /* e.g. everUsed, bindVars, dynSql */

    hintcount           number                       /* number of hints on the outline */



   create table outln.ol$hints


    ol_name            varchar2(30),                              /* outline name */

    hint#             number,                          /* which hint for a given outline */

    category            varchar2(30),          /* collection/grouping name */

    hint_type         number,                                                  /* type of hint */

    hint_text         varchar2(512),        /* hint specific information  */

    stage#              number,               /* stage of hint generation/applic'n */

    node#                           number,                                     /* QBC node id */

    table_name        varchar2(30),                         /* for ORDERED hint */

    table_tin            number,                                   /* table instance number */

    table_pos         number                                         /* for ORDERED hint  */



   create unique index outln.ol$name on outln.ol$(ol_name)


   create unique index outln.ol$signature on outln.ol$(signature,category)


   create unique index outln.ol$hnt_num on outln.ol$hints(ol_name, hint#) 



  1. Export the data from the OUTLN.OL$ and OUTLN.OL$HINTS from the test database, selecting the appropriate category for the required  application with the new 8i QUERY export parameter.

  2. In the production database, it is recommended to store all stored outlines in a separate tablespace. If this is not set yet, create a new tablespace, and set this tablespace as the default one for the OUTLN schema user.  Drop the OL$ and OL$HINTS tables so that the import  recreates them in the appropriate tablespace.

  3. Import the OUTLN.OL$ and OUTLN.OL$HINTS tables and/or rows only depending on the status of the previous step in the production database.

The above text is an excerpt from:

Using Oracle SQL Stored Outlines & Optimizer Plan Stability
ISBN 0-9740716-8-4

by Mike Ault

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.