Oracle常用函数

https://blog.csdn.net/weixin_39805338/category_7739587.html

 

 

 

select e.LAST_LOAD_TIME,e.child_address,DBMS_LOB.substr(SQL_FULLTEXT)
from V$sql e WHERE
 DBMS_LOB.substr(SQL_FULLTEXT,2000,1) LIKE '%汤臣倍健维%'
and DBMS_LOB.substr(SQL_FULLTEXT,2000,1) not LIKE '%SQL_FULLTEXT%'

-- and to_date(e.LAST_LOAD_TIME,'yyyy-mm-dd hh24:mi:ss' )   >= to_date('2022-03-20 12:00:00','yyyy-mm-dd hh24:mi:ss')

 and to_date(e.LAST_LOAD_TIME,'yyyy-mm-dd hh24:mi:ss' )   >=  (select sysdate - interval '1' MINUTE from dual)  --一分钟前

ORDER BY LAST_LOAD_TIME desc;

 

--V$SQL视图中SQL_TEXT的SQL语句是绑定变量，常量在 v$sql_bind_capture 视图的VLUE_STRING字段中可以查到，两个视图可以用child_address进行关联。

 

 

SELECT SQL_ID, NAME , POSITION,datatype_string, value_string, ANYDATA.accesstimestamp (value_anydata)
From V$sql_Bind_Capture A
Where child_address='00007FFE4128F7B0';

 

 /* Formatted on 2022/3/20 13:54:47 (QP5 v5.256.13226.35538) */

SELECT *
FROM (SELECT ROWNUM r, t.*
FROM ( SELECT *
FROM GSP_SYSPJYSPXX sysp
WHERE 1 = 1
AND sysp.ZFBZ = 0
AND ( sysp.SPBM LIKE '%汤臣倍健维%'
OR sysp.PYM LIKE '%' || UPPER ('汤臣倍健维') || '%'
OR sysp.TYM LIKE '%汤臣倍健维%'
OR sysp.SPMC LIKE '%汤臣倍健维%')
AND sysp.CJRQ >= to_date('2022-02-18','yyyy-mm-dd')
AND sysp.CJRQ <=to_date('2022-03-20','yyyy-mm-dd')
AND (1 = 1 OR 1 = 1)
ORDER BY ID DESC) t)
WHERE r >= 1 AND r <= 7;