Oracle custom process to obtain complete SQL statements

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

,