Logo Background

Migrate Oracle SQL Profile

  • By on October 8, 2012 | No Comments

    Looking for steps on how to migrate Oracle SQL Profile? SQL Profile can be exported and imported just like stored outlines, but with a different procedure.

    There is a different procedure for migrating SQL profiles in Oracle 10g release one and the migration procedure changes in Oracle 10g Release 2.

    You use DBMS_SQLTUNE subprograms to move SQL Profiles and SQL Tuning Sets from one system to another using a common programmatic model.

    In both cases, you create a staging table on the source system and populate that staging table with the relevant data.

    TRANSFER A SQL PROFILE

    To transfer a SQL Profile you need to pack and export it from SOURCE, then import and unpack it into TARGET. Connect as SQLTXPLAIN, SYSTEM or SYSDBA

    1. Create staging table in SOURCE

    SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (TABLE_NAME => 'STGTAB_SQLPROF', 
    schema_name => USER);

    2. Pack SQL Profile into staging table in SOURCE

    SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => '<sql_profile_name>', 
    profile_category => 'DEFAULT', staging_table_name => 'STGTAB_SQLPROF', 
    staging_schema_owner => USER);

    3. Export staging table from SOURCE

    UNIX> exp <usr>/<pwd> tables=stgtab_sqlprof file=sqlprof.dmp \
    statistics=none log=sqlprof_exp.log

    4. Import staging table into TARGET

    UNIX> imp <usr>/<pwd> tables=stgtab_sqlprof file=sqlprof.dmp ignore=y log=sqlprof_imp.log

    5. Grant necessary privilege to the user to execute DBMS_SQLTUNE

    SQL> GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO <user>;
    SQL> GRANT ADVISOR TO <user>;
    SQL> GRANT CREATE ANY SQL PROFILE TO <user>;
    SQL> GRANT ALTER ANY SQL PROFILE TO <user>;
    SQL> GRANT DROP ANY SQL PROFILE TO <user>;

    6. Unpack SQL Profile from staging table in TARGET

    SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (profile_name => '<sql_profile_name>', 
    profile_category => 'DEFAULT', REPLACE => TRUE, staging_table_name => 'STGTAB_SQLPROF', 
    staging_schema_owner => USER);

    Notes:
    1. Connect with same user in both SOURCE and TARGET.
    2. User must have CREATE ANY SQL PROFILE privilege and the SELECT privilege on staging table.

    Once you have finish migrating the Oracle SQL Profile, check on the TARGET system for the new profile created.

    SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
    Previous
    Next
    » Create Oracle SQL Profile For Tuning
Leave a Comment