Show all Active SQL for Sessions using v$session and v$sqlarea

SQL Code`

 1set feedback off
 2set serveroutput on size 9999
 3column username format a20
 4column sql_text format a55 word_wrapped
 5begin
 6  for x in
 7   (select username||'('||sid||','||serial#||') ospid = '|| process ||
 8    ' program = ' || program username,
 9    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
10    to_char(sysdate,' Day HH24:MI') current_time,
11    sql_address,
12    sql_hash_value
13   from v$session
14   where status = 'ACTIVE'
15   and rawtohex(sql_address) <> '00'
16   and username is not null ) loop
17   for y in (select sql_text
18   from v$sqlarea
19   where address = x.sql_address ) loop
20   if ( y.sql_text not like '%listener.get_cmd%' and
21    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
22    dbms_output.put_line( '--------------------' );
23    dbms_output.put_line( x.username );
24    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
25    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
26   end if;
27  end loop;
28 end loop;
29end;
30/

Sample Oracle Output:

1
2no rows selected
3SQL>

Purpose:

• To monitor and display currently active user sessions along with the SQL statements they are executing, providing insights into database activity, resource usage, and potential performance bottlenecks. This aids in troubleshooting, performance tuning, and security auditing.

Breakdown:

Output Settings:

*   `set feedback off`: Suppresses standard output messages for cleaner results.
*   `set serveroutput on size 9999`: Enables display of output from `dbms_output.put_line` calls and sets a large buffer for output.
*   `column username format a20`, `column sql_text format a55 word_wrapped`: Formats output columns for readability and word wrapping.

Retrieving Active Sessions:

*   `for x in (select ... from v$session where ...) loop`: Iterates through active sessions:
    
    *   Selects relevant information from `v$session`:
        
        *   User details, session IDs, OS process ID, program name, logon time, current time, SQL address, SQL hash value.
        
    *   Filters for active sessions with SQL activity and non-null usernames.

Retrieving SQL Text:

*   `for y in (select sql_text from v$sqlarea where ...) loop`: Iterates through SQL statements for each session:
    
    *   Joins with `v$sqlarea` based on SQL address to retrieve the actual SQL text.

Filtering and Displaying:

*   `if (y.sql_text not like ... and ...) then`: Excludes internal system SQL statements for clarity.
*   `dbms_output.put_line(...)`: Displays formatted information for each relevant session and SQL statement:
    
    *   Separator for readability.
    *   Session details (username, logon time, current time, SQL hash value).
    *   First 250 characters of the SQL text.

Key Points:

• Real-Time Monitoring: Provides a snapshot of current database activity.
• User-Centric Focus: Highlights active users and their SQL statements for targeted analysis.
• Key Information: Includes session details, timestamps, and SQL text for understanding workload patterns and resource usage.
• Filtering for Relevance: Excludes internal system SQL statements to focus on user-driven activity.
• Dynamic Views: Uses views like v$session and v$sqlarea for real-time data.

Insights and Explanations:

• Troubleshooting:

  • Pinpointing sessions executing problematic SQL statements can aid in diagnosing performance issues or errors.

• Performance Tuning:

  • Identifying resource-intensive SQL statements can guide optimization efforts, such as rewriting queries, adding indexes, or adjusting database configuration.

• Security Auditing:

  • Monitoring active sessions and SQL statements can help detect unauthorized activity or potential vulnerabilities.

• Customization:

  • Can be modified to filter for specific users, session characteristics, or SQL patterns based on analysis needs.

• Integration: Consider incorporating this code into regular database monitoring and alerting systems for proactive issue identification.