Migrating SQL Profiles across databases

We were in middle of R12 upgrades and during the upgrade in our test instances, we performed some SQL tuning through OEM and implemented several SQL profiles to improve the SQL query performance.

I was tasked to migrate those profiles from one test instance to another and eventually to production database during the upgrade.

Following are the steps followed:

1. Create the staging table to move the SQL profiles and move the required profiles or all the profiles at once. I used the following script to stage all the SQL profiles into the staging table.

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'APPS');
truncate table apps.SQL_PROFILES_TT;

PROMPT ROWS IN THE SQL PROFILES DICTIONARY TABLE...

SELECT count(*) from dba_sql_profiles;

PROMPT LOADING PROFILES INTO THE STAGING TABLE...

declare
sql_prof_name dba_sql_profiles.name%TYPE;
cursor c1 is
SELECT NAME FROM DBA_SQL_PROFILES order by created;
begin
        open c1;
        loop
                fetch c1 into sql_prof_name;
                exit when c1%NOTFOUND;
                DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>sql_prof_name,STAGING_SCHEMA_OWNER=>'APPS');
        end loop;
        close c1;
end;
/
PROMPT ROWS IN THE STAGING TABLE ....
select count(*) from apps.SQL_PROFILES_TT;
2. Export the staging table using the traditional export/EXPDP commands. In my case, I chose to use the traditional export and used the following parfile.

exp parfile=export_profiles.par

Parameter file:

userid="/ as sysdba"
file=<dump file name>
log=<log file name>
tables=apps.SQL_PROFILES_TT
3. Move the export file to the target database server and import the SQL profiles.

imp parfile=import_profiles.par

Parameter file:

userid="/ as sysdba"
file=<dump file name>
log=<log file name>
fromuser=apps
touser=apps
ignore=y
4. Connect as sysdba in target database and execute the following command to implement the sql profiles:

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_TT',STAGING_SCHEMA_OWNER=>'APPS');