I am trying to debug next oracle procedure:
create or replace PROCEDURE CASE_6_TRANS_2
AS
V_NUM NUMBER(38,0);
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(AE.ID) INTO V_NUM FROM AUDIT_EVENT AE WHERE ID=10;
COMMIT; --BREAKPOINT
END CASE_6_TRANS_2;
But when I want to see current transaction at breakpoint , the V$TRANSACTION table is empty.
The documentation says:
A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.
According to documentation SELECT query is the DML
The transaction starts with SET TRANSACTION, but the allocation of the transaction_id is delayed until first DML (real DML - in my experiance not a SELECT statement)
The same source says...
When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement.
It makes sense for me, there is no reason to commit or rollback a SELECT.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With