SQLT capture sql plan using profiles to move plan to another db

identify sqlid, and plan hash we want to ping, can use v$sql or OEM

cd to utl directory of sqlt download

cd sqlt/utl

Note to use sql script : DBMS_SQLTUNE is used which requires Tuning Pack License.

sqlplus / as sysdba

SQL> @coe_xfr_sql_profile.sql <sqlid> <planhash>

then generates:

coe_xfr_sql_profile_<sqlid>_<planhash>.sql

This script can be edited if we want to FORCE matching i.e use the same plan irrespective of literals.

force_match => FALSE/* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

Run the script into target DB as sys user as needed to enable profile.

To drop profile if needed:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(‘coe_<sqlid>_<hash>’);