The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on the local database instance.

To generate an ASH report on the local database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/ashrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: text
   

   In this example, a text report is chosen.

3. Specify the begin time in minutes before the system date:

   Enter value for begin_time: -10
   

   In this example, 10 minutes before the current time is selected.

4. Specify the duration to capture ASH information in minutes from the begin time.

   Enter value for duration:
   

   In this example, the default duration of system date minus begin time is accepted.

5. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name ashrpt_1_0310_0131.txt
   

   In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on a specified database and instance. This script enables you to specify a database and instance for which the ASH report will be generated.

To generate an ASH report on a specific database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/ashrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database IDs and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using 3309173529 for database id
   Enter value for inst_num: 1
   

4. To generate an ASH report on a physical standby instance, the standby database must be opened read-only. The ASH data on disk represents activity on the primary database and the ASH data in memory represents activity on the standby database.

   Note:

   This step is applicable only if you are generating an ASH report on an Active Data Guard physical standby instance. If this is not the case, then skip this step.

   Specify whether to generate the report using data sampled from the primary or standby database:

   You are running ASH report on a Standby database.
   To generate the report over data sampled on the Primary database, enter 'P'.
   Defaults to 'S' - data sampled in the Standby database.
   Enter value for stdbyflag:
   Using Primary (P) or Standby (S): S
   

   In this example, the default value of Standby (S) is selected.

5. Specify the begin time in minutes before the system date:

   Enter value for begin_time: -10
   

   In this example, 10 minutes before the current time is selected.

6. Specify the duration to capture ASH information in minutes from the begin time.

   Enter value for duration:
   

   In this example, the default duration of system date minus begin time is accepted.

7. Specify the slot width in seconds that will be used in the Activity Over Time section of the report:

   Enter value for slot_width: 
   

   In this example, the default value is accepted. For more information about the Activity Over Time section and how to specify the slot width, see "Activity Over Time".

8. Follow the instructions in the subsequent prompts and enter values for the following report targets:

   • target_session_id

   • target_sql_id

   • target_wait_class

   • target_service_hash

   • target_module_name

   • target_action_name

   • target_client_id

   • target_plsql_entry

9. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name ashrpt_1_0310_0131.txt
   

   In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information on the database instance with a database ID value of 3309173529 beginning from 10 minutes before the current time and ending at the current time.

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle Real Application Clusters (Oracle RAC) environment. Only ASH data that is written to disk will be used to generate the report. This report will only use ASH samples from the last 10 minutes that are found in the DBA_HIST_ACTIVE_SESS_HISTORY table.

To generate an ASH report for Oracle RAC:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/ashrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database IDs and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
    3309173529        2 TINT251      tint252      samp252
    3309173529        3 TINT251      tint253      samp253
    3309173529        4 TINT251      tint254      samp254
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using database id: 3309173529
   Enter instance numbers. Enter 'ALL' for all instances in an Oracle
   RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
   Defaults to current instance.
   Enter value for inst_num: ALL
   Using instance number(s): ALL
   

4. Specify the begin time in minutes before the system date:

   Enter value for begin_time: -1:10
   

   In this example, 1 hour and 10 minutes before the current time is selected.

5. Specify the duration to capture ASH information in minutes from the begin time.

   Enter value for duration: 10
   

   In this example, the duration is set to 10 minutes.

6. Specify the slot width in seconds that will be used in the Activity Over Time section of the report:

   Enter value for slot_width: 
   

   In this example, the default value is accepted. For more information about the Activity Over Time section and how to specify the slot width, see "Activity Over Time".

7. Follow the instructions in the subsequent prompts and enter values for the following report targets:

   • target_session_id

   • target_sql_id

   • target_wait_class

   • target_service_hash

   • target_module_name

   • target_action_name

   • target_client_id

   • target_plsql_entry

8. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name ashrpt_rac_0310_0131.txt
   

   In this example, the default name is accepted and an ASH report named ashrpt_rac_0310_0131 is generated. The report will gather ASH information on all instances belonging to the database with a database ID value of 3309173529 beginning from 1 hour and 10 minutes before the current time and ending at 1 hour before the current time.