How to check where the stored procedure is executed in Oracle

You can view the execution status of Oracle stored procedures through the following methods: Use the DBMS_APPLICATION_INFO package to view the currently executing operations. Use the V$SESSION_LONGOPS view to view details of the stored procedures being executed. Use the V$SQL_EXECUTE view to view execution information related to the specified stored procedure.

How to check where the Oracle stored procedure is executed

In the Oracle database, you can check the storage through the following method Where the process is executed:

Use the DBMS_APPLICATION_INFO package

<code class="sql">SELECT action FROM DBMS_APPLICATION_INFO;</code>

The result will display the currently executing operation, for example:

• BEGIN - Stored procedure begins execution
• END - Stored procedure execution ends
• EXECUTE STATEMENT - Statement is being executed
• COMMIT - Transaction is being submitted

Use V$SESSION_LONGOPS view

<code class="sql">SELECT sid, event, p1text, rows_processed, elapsed_seconds
FROM V$SESSION_LONGOPS
WHERE event LIKE '%EXECUTE PL/SQL%'
AND sid = <会话 ID>;</code>

The result will display the details of the currently executing stored procedure, including:

• sid - Session ID
• event - event type
• p1text - statement being executed
• rows_processed - number of rows processed
• elapsed_seconds - elapsed time in seconds

Use V$SQL_EXECUTE view

<code class="sql">SELECT sql_text, row_count, elapsed_time
FROM V$SQL_EXECUTE
WHERE sql_text LIKE '%<存储过程名称>%'
AND sid = <会话 ID>;</code>

The result will display execution information related to the specified stored procedure, including:

• sql_text - Storage SQL text of the procedure
• row_count - Number of rows affected
• elapsed_time - Time elapsed in microseconds

Example

Suppose there is a stored procedure named "GET_EMPLOYEES". To view its execution status, you can use the following query:

<code class="sql">SELECT sid, event, p1text
FROM V$SESSION_LONGOPS
WHERE event LIKE '%EXECUTE PL/SQL%'
AND p1text LIKE 'GET_EMPLOYEES%';</code>

The results will display the following information:

<code>sid  event                                           p1text
123  EXECUTE PL/SQL                               GET_EMPLOYEES(...)</code>

This indicates that the stored procedure "GET_EMPLOYEES" is executing and the session ID is 123.

The above is the detailed content of How to check where the stored procedure is executed in Oracle. For more information, please follow other related articles on the PHP Chinese website!