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

From: eagle fan <eagle.f_at_gmail.com>
Date: Sat, 17 Jun 2006 13:14:02 +0800
Message-ID: <4415a5ed0606162214r7d9ba49fq681024f3966e8128@mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jun 17 2006 - 00:14:02 CDT