In Oracle, you can use the "V$SESSION" and "V$PROCESS" views to list the active or open connections in the database. The "V$SESSION" view contains information about all active sessions in the database, while the "V$PROCESS" view contains all background processes in the database.
To list the active connections and their related information, you can use the following query:
SELECT s.sid, s.username, s.status, s.program, s.machine, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE';
This query will return the following information for each active session:
- SID: the session ID
- USERNAME: the username of the connected user
- STATUS: the status of the session (ACTIVE)
- PROGRAM: the name of the program that is connected to the session
- MACHINE: the machine name where the session is connected from
- SPID: the operating system process ID of the session
You can also use the following query to list the active connections with additional information, such as the service name, the user, the terminal, and the current SQL statement.
SELECT s.sid, s.username, s.status, s.program, s.machine, s.service_name, s.osuser, s.terminal, s.sql_text
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE';
You can filter the result by adding a where clause to the query. For example, if you want to see the active connection for a specific user, you can use the following query:
SELECT s.sid, s.username, s.status, s.program, s.machine, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
AND s.username='your_user_name';
Please note that to access the "V$SESSION" and "V$PROCESS" views, you need to have the appropriate privileges granted to your user. Also, the query will only show the active sessions in the current instance and not the whole RAC.
0 Komentar