How to Migrate SQL profile from one database to another?
You
can migrate SQL profile using export and import from one database to another
database just like stored outline. Prior to oracle 10g you can migrate SQL
profiles with the dbms_sqltune.import_sql_profile procedure where as in
oracle 10g release 2 and beyond using dbms_sqltune package. In both case
you have to create a staging table on the source database and populate that
staging table with the relevant data. Below is the step to migrate SQL profile
in 10g release 2.
Step1. Create the staging
table to store SQL Profiles in source database
SQL>
sys/oracle@test.com as sysdba
SQL>
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name
=> ‘SQL_PROFILES’,schema_name=>’HR’);
END;
/
PL/SQL procedure successfully
completed
Step2. Now Copy SQL profiles from SYS to the Staging table
SQL> BEGIN
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_category => ‘%’,
staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’HR’);
END;
/
PL/SQL procedure
successfully completed
Note: As you need to copy all SQL
profiles on my database ‘%’ value for profile_category was the best option.
Step3. Export the staging table at source
SQL> select count(*) from HR.sql_profiles;
SQL> select count(*) from HR.sql_profiles;
COUNT(*)
-------------
3
-------------
3
expdp
system/***** dumpfile=expdp_sql_profiles.dmp TABLES=HR.SQL_PROFILES
DIRECTORY=DPUMP
Step4. Restore the database with the backup taken before all SQL
profiles were generated and import the staging table at target database.
impdp
system/***** dumpfile=expdp_sql_profiles.dmp TABLES=HR.SQL_PROFILES
DIRECTORY=DPUMP TABLE_EXISTS_ACTION=REPLACE
Note: Do not forget to create staging
table on destination database. Use replace = TRUE if you need to have same
SQL_Profiles on both the database.
Step5. Finally Unpack the
SQL profiles from the staging table on destination database.
SQL>
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’HR’,
replace=>FALSE);
END;
/
PL/SQL procedure
successfully completed.
No comments:
Post a Comment