过程如下:
# sqlplu / as sysdba
SQL> select userenv('sid') from dual;
131
SQL> select 1 from dual;
SQL> select 2 from dual;
SQL> select sid,username,sql_address from v$session c,v$sqlarea s
2 where c.sql_address = s.address(+) and sid = 131
3 ;只查到一条记录!
# sqlplu / as sysdba
SQL> select userenv('sid') from dual;
131
SQL> select 1 from dual;
SQL> select 2 from dual;
SQL> select sid,username,sql_address from v$session c,v$sqlarea s
2 where c.sql_address = s.address(+) and sid = 131
3 ;只查到一条记录!
--------------
146SQL> select 1 from dual; 1
----------
1SQL> select 2 from dual; 2
----------
2SQL> select sid,username,sql_address from v$session c,v$sqlarea s
2 where c.sql_address = s.address(+) and sid = 131;未选定行SQL> SELECT sql_text FROM v$sqlarea
2 WHERE sql_text LIKE '%select%dual%';SQL_TEXT
--------------------------------------------------------------------------------select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000))
l2 from dualselect null from dual
select 1 from dual
select TRUNC(SYSDATE+1)+12/24 from dual
select user from dual
select sysdate + 1 / (24 * 60) from dual
SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
select userenv('sid') from dualSQL_TEXT
--------------------------------------------------------------------------------SELECT * FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
select 2 from dual
select 'x' from dual已选择12行。SQL>s.address是正在执行的SQL address,执行过了,一般为'00'。
所以对于历史记录,它们俩无关联性。
select 1 from dual
select TRUNC(SYSDATE+1)+12/24 from dual
select user from dual
select sysdate + 1 / (24 * 60) from dual
SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
select userenv('sid') from dual SQL_TEXT
-------------------------------------------------------------------------------- SELECT * FROM v$sqlarea WHERE sql_text LIKE '%select%dual%'
select 2 from dual
select 'x' from dual 请参见1楼。
你要查询的历史,是包括SQL语句及其sid的历史???
如果是这样,很遗憾,无法做到,因为SID可以重用,过一段时间,该SID已经被其他用户占用,所以即使查出来,也无法回放该语句执行的场景。如果你只是想查询SQL历史记录,则只要该SQL未被清除出shared_pool,完全可以办到。最后的办法是用like,毛估估SQL内容。
希望查到某个session或者user的sql历史记录