hi:

 

When database hang, I got the sql hash value from v$session_wait view.

 

Then I wanted to find the sql_text from v$sql. But I can't find it there.

 

SQL> select sql_text from  stats$sql_summary where hash_value=750036270;

no rows selected

But I can find the sql_text from v$open_cursor. And the sql text is like this:

SQL> select distinct sql_text from v$open_cursor where hash_value=750036270;

SQL_TEXT
------------------------------------------------------------
table_4_2000_f4e_0_0_0

I searched the web and found the article on asktom web for this:

http://asktom.oracle.com/pls/ask/f?p=4950:8:7226994307843891043::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3311300131016

As the link says,

SQL> select to_number('f4e','xxxxx') from dual;

TO_NUMBER('F4E','XXXXX')
------------------------
                    3918

3918 object_id is a table which contains two clob columns.

I tried to find the sql_text from v$sql something like this

select sql_text where sql_text like '%table_name%", but no rows returned

Any ideas?

How can I find the sql text?

Thanks

--
Eagle Fan

Oracle DBA