Oracle Performance: Copy Sql Profiles to Another Database

Performance Optimization

Sql Profiles are really easy to use. Sometimes, we need to transfer a bunch of profiles to another database.

Create the special staging table

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => 'PROFILES', schema_name=>'CCBP');
     END;
/

Copy desired profile to the Staging table

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_name         => 'SYS_SQLPROF_0659e1f0f6d80000',
staging_table_name => 'PROFILES',
staging_schema_owner=>'CCBP');
END;
/

Make sure profile is there

select distinct obj_name from CCBP.PROFILES;

 

Export special staging table

expdp cxly dumpfile=profiles.dmp TABLES=CCBP.PROFILES

-- Dump file in /u01/app/oracle/product/12.1.0/db_1/rdbms/log/profiles.dmp

 

--In Target Database Create the staging table

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => 'PROFILES', schema_name=>'CCBP');
     END;
/

Now import the profile

impdp cxly dumpfile=profiles.dmp TABLES=CCBP.PROFILES TABLE_EXISTS_ACTION=REPLACE

-- Transfer SQL profiles from Staging Table to Final place
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => 'PROFILES',
staging_schema_owner=>'CCBP', replace=>FALSE);
END;
/

Check to make sure new profile is there

COL NAME FORMAT A33
select name, created from dba_sql_profiles orderby created;