Previous Topic

Next Topic

Book Contents

Deploying SQL profiles

SQL profiles can be used to improve performance.

To deploy SQL profiles:

  1. Unzip InForm_SQLProfiles.zip.(located in the <Installation_Directory>\InForm\Bin\DBOra\ReviewSchema folder).
  2. Connect to SQLPlus as a user with the proper privileges:
    • ADVISOR role
    • CREATE ANY SQL PROFILE PRIVILEGE
    • ALTER ANY SQL PROFILE PRIVILEGE
    • DROP ANY SQL PROFILE PRIVILEGE
    • EXECUTE PRIVILEGE ON DBMS_SQLTUNE

    Note: The user can be of any type, such as system user or pfadmin user, as long as a sys user has granted them the proper privileges.

  3. Create a staging table in the destination database.

    EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'STGTAB_SQLPROF_DB006');

  4. Import the data to the destination database.

    imp <user>/<pwd>@<instance> tables=('STGTAB_SQLPROF_DB006') file= InForm_SQLProfiles.dmp log=imp.log ignore=y

  5. Create SQL Profiles in the destination database using the data stored in the staging table.

    EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, staging_table_name => 'STGTAB_SQLPROF_DB006');

  6. Verify that the profiles imported correctly.

    Select name, created from dba_sql_profiles;

Send Feedback