move execution plan between two oracle databases

IMPORT/EXPORT execution plan from one Database to another

You use DBMS_SQLTUNE subprograms to move SQL profiles and SQL tuning sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:

Call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure to create the staging table on the source system.

Call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure to populate the staging table with information from the source system.

Once you have moved the staging table to the destination system, you call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure to recreate the object on the new system.


EXAMPLE:

NOTE: Do not sys schema for staging table

Create a staging table:
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name  => 'PROFILE_TO_PROD');

dump profile to staging table
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(profile_name => 'migrate_to_prod_1',profile_category => 'DEFAULT',staging_table_name=> 'PROFILE_TO_PROD', staging_schema_owner => 'MD86506');

export/import staging table
exp tables=PROFILE_TO_PROD file=prof01.dmp log=prof01.log
imp file=prof01.dmp log=profimp.log tables=PROFILE_TO_PROD

unpack the staging table for sql_profile to come into effect
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace            => FALSE, -
                                        staging_table_name => 'PROFILE_STGTAB');


References: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#CHDGFHEC