Transfer oracle sql profiles between two databases.

 

                                                                                                                Date added (2015-03-29 19:58:47)

Summary
Here is a simple guide to transfer sql profiles from one database to another. For better understanding assume that you want to transfer one sql profile from the 'source' database to the 'destination' database.

1. Connect to the source database as sysdba and grant the required privileges to the user which will own the staging table.
For this example the user is osm.

$ sqlplus "/ as sysdba" 
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO osm;

2. Connect as user osm to the source db and create the staging table.

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROFILES_STGTAB');
END;
/

3. Check the existing sql profiles at the source db and copy the desired to the staging table

SELECT * FROM dba_sql_profiles ORDER BY created DESC;

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_014c5ae7b1c80001', staging_table_name => 'SQL_PROFILES_STGTAB');
END;
/

SELECT * FROM osm.SQL_PROFILES_STGTAB;

4. Copy the staging table SQL_PROFILES_STGTAB from the source db to the destination db.
5. Grant again at the destination db the required privilege.

GRANT ADMINISTER SQL MANAGEMENT OBJECT TO osm;

6. Add the sql profiles from the staging table to the destination db.

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE, staging_table_name => 'SQL_PROFILES_STGTAB');
END;
/

SELECT * FROM dba_sql_profiles ORDER BY created DESC;

http://www.runningoracle.com/product_info.php?products_id=446