Deploying SQL profiles

SQL profiles can be used to improve performance.

To create a SQL Profile, a user must have the following privileges:

• ADVISOR role
• CREATE ANY SQL PROFILE PRIVILEGE
• ALTER ANY SQL PROFILE PRIVILEGE
• DROP ANY SQL PROFILE PRIVILEGE
• EXECUTE PRIVILEGE ON DBMS_SQLTUNE

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

Copyright © 2014, 2016 Oracle and/or its affiliates. All rights reserved.