How to copy a sql tuning set from one database to another database in oracle

The process to move a sql tuning plan from one database to another is by using the dbms_sqltune package. NOTE: this is a way to do only of a profile is enable and running on aa sql id.

1) Create a source staging table

   EXEC DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'ahmad_plan');

2) Copy the appropriate tuning plan(s) to the table

in this example, the name was found in enterprise manager for the specific sql_id = 'dnvar5cuuxv3j'.

Look for the profile name if added to the above sql id using OEM or sqlplus and use its name for sqltune.pack_stgtab_sqlprof. 

Refer to doc https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACBCEEH for understanding more. 

exec dbms_sqltune.pack_stgtab_sqlprof('SYS_SQLPROF_0439c17f75330007',staging_table_name =>'ahmad_plan',staging_schema_owner =>'ahmadbhu');

To get all of them, use:

exec dbms_sqltune.pack_stgtab_sqlprof('%',staging_table_name =>'ahmad_plan',staging_schema_owner =>'ahmadbhu');

3) export/import this stage table to the target database

exp ahmadbhu@orcl file=ahmad_plan.dmp log=ahmad_plan.log tables=ahmad_plan

imp ahmadbhu@orcl1 file=ahmad_plan.dmp log=ahmad_imp_plan.log full=y

4) copy the tuning plan(s) to the database

exec dbms_sqltune.unpack_stgtab_sqlprof('SYS_SQLPROF_0439c17f75330007',replace=>TRUE,staging_table_name =>'ahmad_plan',staging_schema_owner =>'ahmadbhu');

That is it you are all set. Now you can test the performance.