How to know the object that active transaction is manipulating

The study of this problem is step-by-step.

At first, the customer wanted to know how long it would take for a database session that had been killed to roll back the transactions that had been done before the Smon session, in which case the session would be completely killed. Because the transaction opened by the killed session has not been able to be queried in the v$transation view, it needs to be queried from the database base table X$ktuxe.

SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != 'INACTIVE';

You can get the number of undo blocks that are still in use in this session, and then after 10 seconds, query again, and then subtract the value twice, that is, the number of successful undo blocks for the Smon rollback in 10 seconds, which is compared with the current Ktuxesiz value, You can estimate how long it will take to complete the job.

Metalink note:43653.1 describes X$ktuxe's various columns in detail, and an article in Eygle describes this approach.

Then I experimented on the base table on my own machine, and suddenly I found a transaction still active on my machine. But the database on my own machine I didn't do any DML operations, why would there be an active transaction? This transaction also uses 2 undo blocks.

SQL> SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != 'INACTIVE';

KTUXESIZ
----------
2

Continue to check the situation on your own machine, there is indeed an active transaction.

SQL> select addr,xidusn,xidslot,xidsqn,status from v$transaction ;

ADDR　　　　 XIDUSN　　 XIDSLOT　　 XIDSQN STATUS
-------- ---------- ---------- ---------- ----------------
45270E48　　　　　4　　　　 14　　　　371 ACTIVE

Find out what this transaction is currently executing sql,sql_id is null (sql_id is a oracle10g field), that is, it is impossible to determine what the transaction is doing.

SQL> select sid,username,sql_id,sql_hash_value from v$session where taddr='45270E48';

SID USERNAME　　　　　　　　　　　 SQL_ID　　　　 SQL_HASH_VALUE
---------- ------------------------------ ------------- --------------
132 SCOTT　　　　　　　　　　　　　　　　　　　　　　　　　　 0

In fact, later found that although sql_id has no value, but prev_sql_id still have a value, and through this value from the V$sql can find out just what this transaction did.

In fact, SQL is: DELETE from plan_table WHERE statement_id=:1

Well, yes, delete the record from the Plan_table, which is a recursive SQL.

It didn't occur to me to check the prev_sql_id value (because the SQL found in this field is often a less meaningful recursive SQL), but from username = Scott I can personally tell that basically because I did SQL Trace, Because I only logged in to the Scott user, only to see the autotrace of the SQL execution plan, because the Autotrace function is to use the Plan_table table to store the intermediate results, and then delete the corresponding records after the execution plan is displayed.

So if Oracle does not commit after displaying the execution plan and deleting the corresponding record of plan_table, then an active transaction exists. But this is only a guess, how to verify it?

What you can think of is the dump undo block to see what's in it.

SQL> SELECT * FROM V$ROLLNAME WHERE USN = 4;

USN NAME
---------- -------------- ----------------
4 _SYSSMU4_1195301203$

--仅仅是为了查看undo block的dump， 不需要dump出undo heander，这里只是给出语法
SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU4_1195301203$";

System altered

SQL> ALTER SYSTEM DUMP UNDO BLOCK "_SYSSMU4_1195301203$" XID 4 14 371;

System altered