How to transfer SQL Profiles from one database to another

Following up from part 1 on my post on How to transfer SQL Plan Baselines from one database to another, I will now show you the steps to transfer a SQL Profile in the same manner.
For SQL Profiles you depend on the package dbms_sqltune
I am connecting to the databases as a privileged user called dbadmin throughout this post.

1. First, find the name of SQL profile you are about to transfer:

select * from dba_sql_profiles order by created;

The query above will return the SQL Profiles in the order in which they were created. Notice the name of the relevant one, as this will be used as an argument in the procedures later.

2. On the source database, create a staging table for SQL Profiles:

vi 2.cre_SQLProfile_stgtab.sql

Add the following:

connect dbadmin
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
    table_name  => 'MYSTAGETAB_SQLPROF'
,   schema_name => 'DBADMIN'
);
END;
/
exit

Execute the file:

sqlplus /nolog @2.cre_SQLProfile_stgtab.sql

3. Pack your SQL Profile into your staging table

Create the file 3.pack_SQLProfile_stgtab.sql:

vi 3.pack_SQLProfile_stgtab.sql

Add the following:

conn dbadmin
alter session set nls_language='american';

BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
    profile_name         => 'SYS_SQLPROF_014fb0ca24980001'
,   staging_table_name   => 'MYSTAGETAB_SQLPROF'
,   staging_schema_owner => 'DBADMIN'
);
END;
/
exit

Run the file:

sqlplus /nolog @3.pack_SQLProfile_stgtab.sql

Let's verify that the staging table now has some rows in it:

connect dbadmin
select count(*)
from  MYSTAGETAB_SQLPROF;

4. Create the export parameter file and export the table:

vi 4.expdp_SQLPROFILE_stgtab.sql

Add the following:

userid=dbaadmin

tables=MYSTAGETAB_SQLPROF

logfile=expdp_MYSTAGETAB_SQLPROF.log

dumpfile=MYSTAGETAB_SQLPROF.dmp

job_name=exp_MYSTAGETAB_SQLPROF

directory=DP

Make sure the directory DP exists in the database. If not, create it with

create directory DP as '/exports/';

Then export the table:

expdp parfile=4.expdp_SQLPROFILE_stgtab.sql

Logon to the destination server. 

5. Import the staging table in the destination database

Create an import parameter file:

vi 5.impdp_SQLProfile.par

Add the following:

userid=dbadmin
tables=MYSTAGETAB_SQLPROF
logfile=impdp_MYSTAGETAB_SQLPROF.log
dumpfile=MYSTAGETAB_SQLPROF.dmp
job_name=imp_MYSTAGETAB_SQLPROF
directory=DP

Run the import:

impdp parfile=5.impdp_SQLProfile.par

6. Unpack the information in the staging table

Create the file for unpacking the SQL Plans to the local SMB:

vi 6.unpack.sql

Add the following:

conn dbadmin
alter session set nls_language='american';
set serveroutput on
BEGIN
   DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
     replace            => true
,    staging_table_name => 'MYSTAGETAB_SQLPROF'
,    profile_name => 'SYS_SQLPROF_014fb0ca24980001'
);
END;
/
exit

Execute the file:

sqlplus /nolog @6.unpack.sql

Let's verify that the optimizer has actually created a new execution plan based on the SQL Profile:

SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('5cbuqgt5m5961')
ORDER BY LAST_ACTIVE_TIME DESC;

Output:

last active	SQL_ID	PLAN_HASH_VALUE	BASELINE	SQL_PROFILE	AdaptivePlan	Child Num	ELAPSED_TIME	EXECUTIONS	duration (sec)	sec per exe
21.10.2020 13:19	5cbuqgt5m5961	1355560190				1	20850937263	1488	20851	14
21.10.2020 12:55	5cbuqgt5m5961	1355560190				0	112190741129	27134	112191	4,1
21.10.2020 14:47	5cbuqgt5m5961	3389950398	SQL_PLAN_ds58srfg86uf4f5a21fb1	SYS_SQLPROF_014fb0ca24980001		3	116651	302	0	0

After the SQL Profile was created, the performance was finally stabelized.
The documentation for the dbms_sqltune package can be found here