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

From: Joseph Amalraj <joseph_at_amalrajinc.com>
Date: Sat, 17 Jun 2006 18:17:45 -0700 (PDT)
Message-ID: <20060618011745.83882.qmail@web601.biz.mail.mud.yahoo.com>

Try event 10046 tracing.    

  as mentioned earlier in a oracle-l thread not all SQL statements are present in the shared pool.    

  Regards    

  Joseph

eagle fan <eagle.f_at_gmail.com> wrote:

    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 - 20:17:45 CDT