Oracle Performance: Copy Sql Profiles to Another Database

Using Sql Profiles

Performance Optimization: I'm pretty sure that's Don Burleson

Sql Profiles are really easy to use. Sometimes, we need to transfer a bunch of profiles to another database. This will work fine, as long as the other database is running the identical sql.  Here's how to do it.

Create the special staging table

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => 'PROFILES', schema_name=>'CHRIS');
     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=>'CHRIS');
END;
/

Make sure profile is there

select distinct obj_name from CHRIS.PROFILES;

Export special staging table

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

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

In Target Database Create Staging Table

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

Now import the profile

impdp cxly dumpfile=profiles.dmp TABLES=CHRIS.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=>'CHRIS', replace=>FALSE);
END;
/

Buy on Amazon

Check to make sure new profile is there

COL NAME FORMAT A33
select name, created from dba_sql_profiles order by created;

It's Easy!

I hope you find sql profiles as easy to use as I do.  

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle Performance: Copy Sql Profiles to Another Database