Can't find the sql text from v$sql via the hash value

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

Other related posts: