Oracle custom process to obtain complete SQL statements

Source: Internet
Author: User
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

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.