The profile statistics are in an Oracle Database internal format, so you cannot query profiles directly. However, you can list profiles.

To list SQL profiles:

1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

2. Query the DBA_SQL_PROFILES view.

   For example, execute the following query:

   COLUMN category FORMAT a10
   COLUMN sql_text FORMAT a20
   
   SELECT NAME, SQL_TEXT, CATEGORY, STATUS
   FROM   DBA_SQL_PROFILES;
   

   Sample output appears below:

   NAME                           SQL_TEXT             CATEGORY   STATUS
   ------------------------------ -------------------- ---------- --------
   SYS_SQLPROF_01285f6d18eb0000   select promo_name, c DEFAULT    ENABLED
                                  ount(*) c from promo
                                  tions p, sales s whe
                                  re s.promo_id = p.pr
                                  omo_id and p.promo_c
                                  ategory = 'internet'
                                   group by p.promo_na
                                  me order by c desc

The CATEGORY attribute determines which sessions can apply a profile. View the CATEGORY attribute by querying DBA_SQL_PROFILES.CATEGORY. By default, all profiles are in the DEFAULT category, which means that all sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you determine which sessions are affected by profile creation. For example, by setting the category to DEV, only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. Other sessions do not have access to the SQL profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.

The example in this section assumes that you want to change the category of the SQL profile so it is used only by sessions with the SQL profile category set to TEST, run the SQL statement, and then change the profile category back to DEFAULT.

To alter a SQL profile:

1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

2. Use the ALTER_SQL_PROFILE procedure to set the attribute_name.

   For example, execute the following code to set the attribute CATEGORY to TEST:

   VARIABLE pname my_sql_profile
   BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
      name            =>  :pname
   ,  attribute_name  =>  'CATEGORY'
   ,  value           =>  'TEST'      
   );
   END;
   

3. Change the initialization parameter setting in the current database session.

   For example, execute the following SQL:

   ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';
   

4. Test the profiled SQL statement.

5. Use the ALTER_SQL_PROFILE procedure to set the attribute_name.

   For example, execute the following code to set the attribute CATEGORY to DEFAULT:

   VARIABLE pname my_sql_profile
   BEGIN 
     DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
        name            =>  :pname
   ,    attribute_name  =>  'CATEGORY'
   ,    value           =>  'DEFAULT'   
   );
   END;

Assumptions

This section assumes the following:

• You want to drop my_sql_profile.

• You want to ignore errors raised if the name does not exist.

To drop a SQL profile:

1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

2. Use the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure.

   The following example drops the profile named my_sql_profile:

   BEGIN
     DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
       name => 'my_sql_profile' 
   );
   END;
   /

Table 26-1 shows the main procedures and functions for managing SQL profiles.

The following graphic shows the basic workflow of transporting SQL profiles.

Figure 26-3 Transporting SQL Profiles

Description of "Figure 26-3 Transporting SQL Profiles"

Assumptions

This tutorial assumes the following:

• You want to transport my_profile from a production database to a test database.

• You want to create the staging table in the dba1 schema.

To transport a SQL profile:

1. Connect SQL*Plus to the database with the appropriate privileges, and then use the CREATE_STGTAB_SQLPROF procedure to create a staging table to hold the SQL profiles.

   The following example creates my_staging_table in the dba1 schema:

   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( 
       table_name  => 'my_staging_table'
   ,   schema_name => 'dba1' 
   );
   END;
   /
   

2. Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.

   The following example populates dba1.my_staging_table with the SQL profile my_profile:

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (  
       profile_name         => 'my_profile'
   ,   staging_table_name   => 'my_staging_table'
   ,   staging_schema_owner => 'dba1' 
   );
   END;
   / 
   

3. Move the staging table to the database where you plan to unpack the SQL profiles.

   Move the table using your utility of choice. For example, use Oracle Data Pump or a database link.

4. On the database where you plan to import the SQL profiles, use UNPACK_STGTAB_SQLPROF to unpack SQL profiles from the staging table.

   The following example shows how to unpack SQL profiles in the staging table:

   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
        replace            => true
   ,    staging_table_name => 'my_staging_table'
   );
   END;
   /