After oracle shares the SQL statements, the intercepted SQL statements contain variables. Create a function jy_getsql to obtain the complete SQL statement CREATEORREPLACEF.
After oracle shares the SQL statements, the intercepted SQL statements contain variables. CREATE a function jy_getsql to obtain the complete SQL statement "CREATE OR REPLACE F" during execution.
Because after Oracle shares the SQL statements, the intercepted SQL statements contain variables.
Create a function jy_getsql to obtain the complete SQL statement during execution.
Create or replace function jy_getsql (my_ SQL _id in varchar2)
RETURN clob
IS
Result clob;
Cursor jl (p_ SQL _id in varchar2) is
Select decode (instr (B. BIND_NAME, 'sys '), 0,': '| B. BIND_NAME | '','' | chr (58) | chr (34) | B. BIND_NAME | chr (34) | '') name,
Decode (B. DATATYPE, 2, dbms_sqltune.extract_bind (. bind_data, B. POSITION ). value_string, ''' | dbms_sqltune.extract_bind (. bind_data, B. POSITION ). value_string | '''')
Value_string
From v $ sqlarea a, v $ SQL _bind_metadata B
Where a. LAST_ACTIVE_CHILD_ADDRESS = B. ADDRESS
And a. SQL _ID = p_ SQL _id
Order by B. POSITION desc;
BEGIN
Select a. SQL _FULLTEXT into Result
From v $ sqlarea a where a. SQL _ID = my_ SQL _id;
For r in jl (my_ SQL _id) loop
Result: = replace (Result, r. name, r. value_string );
End loop;
RETURN Result;
EXCEPTION
WHEN OTHERS
THEN
RETURN Result;
END;
The following query is the SQL statement used to query the database that is currently in the waiting state.
Select sw. sid, s. username, sw. event, sw. wait_time, sw. state, sw. seconds_in_wait, p. PROGRAM, s. MACHINE,
(Select c. SQL _FULLTEXT from v $ sqlarea c where c. SQL _ID = s. SQL _ID) SQL _fulltext,
(Select c. BIND_DATA from v $ sqlarea c where c. SQL _ID = s. SQL _ID) BIND_DATA, s. SQL _ID,
Jy_getsql (s. SQL _ID)
From v $ session s, v $ session_wait sw, v $ process p
Where s. username is not null and s. PADDR = p. ADDR
And sw. sid = s. sid and sw. event not like '% SQL * Net %'
Order by sw. wait_time desc
However, the disadvantage is that
Select 1 userid from dual
This 1 is not in v $ sqlarea. bind_data
But the binding variable in the where clause still has
,