A Comprehensive Guide to Viewing Current Database Session Details in Oracle SQL*Plus

Introduction

In Oracle database management or development work, it is common to maintain multiple SQL*Plus sessions simultaneously, connecting to different database instances. Quickly and accurately identifying the current connection details of each session is crucial to avoid operational errors and improve work efficiency. Based on community Q&A data, particularly Tanel Poder's high-scoring solution, this article systematically introduces various methods for viewing SQL*Plus session details, from simple commands to comprehensive scripts, meeting needs in different scenarios.

Basic Session Information Query Methods

For beginners or quick checks, Oracle SQL*Plus provides some built-in commands and simple queries to obtain basic session information. For example, using the SHOW USER command displays the username of the current connection, which is the most direct identifier of session identity. A code example is as follows:

SQL> SHOW USER
USER is "SYSTEM"

Additionally, querying data dictionary views can yield more instance-level information. For instance, SELECT instance_name FROM v$instance; returns the name of the currently connected database instance. Another common query is SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;, which uses the SYS_CONTEXT function to obtain the same information, suitable for scenarios requiring dynamic context. For the global database name, SELECT * FROM global_name; can be used. These methods are simple and easy to use but provide scattered information without integration.

Advanced Comprehensive Script: Tanel Poder's 'Who am I' Solution

To comprehensively obtain session details, Tanel Poder developed a SQL*Plus script called 'Who am I'. This script uses multi-table join queries to retrieve key parameters such as user, instance, host, SID, serial number, and process IDs in one go, and supports custom window titles, greatly facilitating multi-window management. The script's core logic is based on join queries of the V$SESSION, V$INSTANCE, and V$PROCESS views.

The script first defines a series of variables to store query results, such as mysid for SID and _i_serial for serial number. Through the NEW_VALUE clause of the COLUMN command, query results are assigned to these variables. The main query part is as follows:

SELECT 
    s.username          i_username, 
    i.instance_name i_instance_name, 
    i.host_name         i_host_name, 
    TO_CHAR(s.sid)          i_sid, 
    TO_CHAR(s.serial#)      i_serial, 
    (SELECT SUBSTR(banner, INSTR(banner, 'Release ')+8,10) FROM v$version WHERE rownum = 1) i_ver,
    TO_CHAR(startup_time, 'YYYYMMDD') i_startup_day, 
    p.spid              i_spid, 
    TRIM(TO_CHAR(p.pid))        i_opid, 
    s.process           i_cpid, 
    s.saddr             saddr, 
    p.addr              paddr,
    LOWER(s.username) "_i_user",
    UPPER('&_connect_identifier') "_i_conn"
FROM 
    v$session s, 
    v$instance i, 
    v$process p
WHERE 
    s.paddr = p.addr
AND 
    sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

This query associates sessions and processes via s.paddr = p.addr and uses a subquery sid = (SELECT sid FROM v$mystat WHERE rownum = 1) to ensure only current session information is returned. The script also includes platform-specific commands, such as using host title in Windows CMD to modify window titles, displaying session details (e.g., user@connect identifier, SID, serial number) in the title bar for visual differentiation. Sample output shows a complete session detail table, including fields like username, instance name, host name, SID, serial number, Oracle version, startup time, SPID, OPID, and CPID.

Customization and Integration of the Script

This script is highly customizable. Users can adjust query fields or output formats as needed. For example, if only core parameters are of interest, the SELECT list can be simplified. The script suggests automatic execution via the glogin.sql file, so it runs automatically with each connection, ensuring session information is updated immediately. Adding @me.sql (assuming the script is saved as me.sql) to glogin.sql achieves this. For temporary use, the script file can also be executed manually.

Window title customization is another highlight. In Windows environments, the script uses the command host title %CP% &_i_user@&_i_conn [sid=&mysid #=&_i_serial] to embed key information into the title. On Unix/Linux systems, xterm escape sequences like host echo -ne "\033]0;&_i_user@&_i_inst &mysid[&_i_spid]\007" can achieve similar effects. This visual feedback is particularly useful in multi-window environments for quickly identifying the current active session.

Other Practical Tips and Considerations

Beyond the main script, other answers provide supplementary methods. For example, the SHOW PARAMETER instance_name command directly displays the instance name parameter, suitable for certain environments. However, note that this may return the instance name from all parameters, not specific to the current session. In code, characters like <br> in text descriptions should be escaped as <br> to avoid being parsed as HTML tags.

When using these methods, ensure sufficient privileges to access V$ views. Typically, DBA roles or specific system privileges (e.g., SELECT_CATALOG_ROLE) are required. In shared environments, protect sensitive information like SPID to avoid security risks. Variables such as &_connect_identifier in the script leverage SQL*Plus substitution variables, enhancing flexibility.

Conclusion

Viewing current session details in SQL*Plus is a fundamental skill in Oracle database management. From simple SHOW USER to complex 'Who am I' scripts, different methods suit different scenarios. Tanel Poder's script stands out as the preferred solution due to its comprehensiveness and practicality, especially through window title customization, addressing the pain points of multi-session management. It is recommended that users integrate the script into their workflows based on their needs and combine it with other commands like SELECT instance_name FROM v$instance for cross-verification. Mastering these techniques not only improves operational accuracy but also enhances overall efficiency in complex database environments.