Oracle Cloud Infrastructure Documentation

Search Sessions

You can specify conditions and filters to obtain data about the current sessions in your Managed Database.

Note

The Search sessions feature in Database Management is currently not available for Autonomous Databases. Any mention of "Managed Databases" in this section only refers to External Databases and Oracle Cloud Databases for which Diagnostics & Management is enabled.

A dynamic search in the Search sessions section, allows you to query the V$SESSION view and obtain real-time or historic data about current sessions.

Before you search for sessions, you must ensure that you've set a session credential. For information, see Set Session Credentials.

Here's information on the privileges required to search for sessions and perform other related tasks:

  • To list sessions: READ ON V_$SESSION, READ ON GV_$SESSION
  • To enable or disable SQL trace: EXECUTE ON DBMS_MONITOR
  • To terminate a session: ALTER SYSTEM

To search for sessions:

  1. Go to the Managed database details page and on the left pane under Resources, click Search sessions.
  2. In the Search sessions section, specify filter conditions:
    1. Select one of the following options to specify filtering criteria:
      • Any: The current sessions that meet any of the specified criteria are filtered and displayed.
      • All: The current sessions that meet all the specified criteria are filtered and displayed.
    2. Select a filter attribute or qualifier to filter data, an operator to specify how the filter attribute relates to the value, and add a value of the specified filter attribute.

      By default, the SID, Program, and Service filter attributes are displayed and you can specify the operator and value. To add an additional filter attribute or a column to be displayed in the search results, click Add a filter or column. You can click the Remove icon (Remove) to remove a single filter attribute or click Remove all to remove all filter attributes.

      For filter attributes such as SID and DB user, the Show columns check box is selected by default and cannot be deselected as this information will be displayed for the sessions listed in the search results.

    3. Click Search.
The filtered current sessions are listed under Results and information such as the session's Status and Program are displayed by default and additional columns such as Server and SQL trace are displayed if you added them when setting filter attributes and selected the Show column check box. You can use the Search in results field to search for specific sessions.

You can click the Actions icon (Actions) for a session and perform the following tasks:

  • Terminate session: Click to terminate the session. Note that if you terminate a session, all the operations for the session will be terminated and the user will be disconnected from the database.
  • Enable SQL trace: Click to enable SQL trace for the session to capture detailed information for performance diagnostics. In the Enable SQL trace panel, you have the option of enabling SQL trace with Wait information and Bind information, and setting the frequency at which row source statistics are dumped in the trace files for each cursor.
  • Disable SQL trace: Click to disable SQL trace for the session.