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 pfdbadmin 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;