oracle10g_v$sqltext之對等檢視v$sqltext_with_newlines

--v$sqltext_with_newlines是v$sqltext等同體，較之易讀性強(由sql_address及sql_hash_value唯一確認一個cached cursor中的sql)
SQL> select b.sql_text from v$session a,v$sqltext_with_newlines b where a.sql_address=b.address and a.sql_hash_value=b.hash_value;

SQL_TEXT
----------------------------------------------------------------
re a.sql_address=b.address and a.sql_hash_value=b.hash_value
select b.sql_text from v$session a,v$sqltext_with_newlines b whe

SQL> select b.sql_text from v$session a,v$sqltext_with_newlines b where a.prev_sql_addr=b.address and a.prev_hash_value=b.hash_value and a.sid=145;--用prev找到剛剛執行過的sql

SQL_TEXT
----------------------------------------------------------------
select deptno,dname from test where deptno in(10,20) for update

SQL> /

SQL_TEXT
----------------------------------------------------------------
delete from test

 

小結
     以後關聯查sql就用v$session連線v$sqltext_with_newlines吧，方便快速

     v$sqltext_with_newlines各列含義：address  --sql（cached cursor)的在共享池(library cache)地址,
                                      hash_value --連同上列唯一確定一個cached cursor
                                      sql_id  --cached cursor的標識
                                      command_type  --sql語句型別（select ,insert 等）
                                      piece   --一個大sql會分為好多行，可理解為行號
                                      sql_text  --每個piece的sql內容