Deploy the SQL profiles
SQL profiles can be used to improve performance.
To deploy SQL profiles:
- Unzip InForm_SQLProfiles.zip.(located in the <Installation_Directory>\InForm\Bin\DBOra\ReviewSchema folder).
- 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.
- Create a staging table in the destination database.
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'STGTAB_SQLPROF_DB006');
- Import the data to the destination database.
imp <user>/<pwd>@<instance> tables=('STGTAB_SQLPROF_DB006') file= InForm_SQLProfiles.dmp log=imp.log ignore=y
- 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');
- Verify that the profiles imported correctly.
Select name, created from dba_sql_profiles;