how to get hanged sessions sql statements?

[wchuacs]

Hi,

Sometimes there are some sessions hanged and need to kill them off. But how to get their sql statements (to know whether is doing SELECT,UPDATE,DELETE, etc...)?

I know there is a table v$sqlarea, and below is my simple query:
SQL> select executions, disk_reads, buffer_gets, sql_text from v$sqlarea where address='&address'

Is that correct ?

Thanks in advanced.

 

[Dagon]

V$session has columns:

SQL_ADDRESS
SQL_HASH_VALUE

These link back to:

ADDRESS
HASH_VALUE

in V$SQLAREA. In that way, you can trace which process is executing a piece of SQL. Note that address is a RAW value so, if you display it in SQL*Plus, it will be shown in hex. To convert it back to RAW, you need to apply the HEXTORAW function.