In database management, Oracle is a very commonly used and important database. In operation and maintenance, it is often necessary to monitor the execution of the database, especially the executed SQL statements, which can greatly help us track and optimize SQL performance. In this article, we will introduce how to query the SQL statements being executed in the Oracle database.
Tools for querying active SQL statements
Oracle provides some tools to help us query active SQL statements. These tools include:
SQL script to query the SQL statement being executed
In the Oracle database, we can use the two system views V$SESSION and V$SQL to query the active SQL statement .
The V$SESSION view contains all currently active session information, including the SQL_ID of each session, which is the identifier of the SQL statement being executed by each session.
V$SQL view contains information about all compiled SQL statements, including SQL_ID, SQL_TEXT, etc.
Here are some SQL scripts for querying the SQL statement being executed:
SELECT s. sid, s.serial#, s.sql_id, s.sql_child_number, t.sql_text
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';
This script will return the SQL_ID and corresponding SQL text executed by all active sessions.
SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t .sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';
This script will return the SQL_ID executed by the active session and detailed SQL information, including user name , status, recent running time, program name, Action, SQL text, etc.
SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t .sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username = 'your_username';
This script will return the SQL statement being executed by a user and the corresponding detailed information.
SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text ,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.STATUS = 'ACTIVE'
AND s.username <> 'SYS'
AND s.sql_id IN (
SELECT MIN(sql_id)
FROM v$session
WHERE username <> 'SYS'
AND STATUS = 'ACTIVE'
GROUP BY username
);
This script will return the first executed SQL statement, including user name and status , recent running time, program name, Action, SQL text, etc.
SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.STATUS = 'ACTIVE'
AND s.username <> 'SYS'
AND s.sql_id IN (
SELECT MAX(sql_id)
FROM v$session
WHERE username <> 'SYS'
AND STATUS = 'ACTIVE'
GROUP BY username
);
This script will return the last executed SQL statement, including user name, status, recent Running time, program name, Action, SQL text, etc.
Summary
In Oracle database management, we often need to query the SQL statements being executed. Oracle provides system views for querying active SQL statements. By querying these views and using SQL scripts, we can easily query active SQL statements and related information to optimize and monitor SQL performance. At the same time, Oracle Enterprise Manager and Oracle SQL Developer also provide the function of querying SQL statements, which can be selected and used according to personal needs.
The above is the detailed content of oracle query execution sql. For more information, please follow other related articles on the PHP Chinese website!