--PL/SQL显示哪个用户(会话SID以及serial)通过什么工具执行了什么操作
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;
--取得两个字符串从第几个字符开始不同。。。
select min(level)
from dual
where nvl(substr('字串1',level,1),' ')<>nvl(substr('字串2',level,1),' ')
connect by level<=200;
--求两个日期相差多少天、多少小时、多少分、多少秒
SELECT EXTRACT(DAY
FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))
DAY(9) TO SECOND) || '天'||
EXTRACT(HOUR
FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))
DAY(9) TO SECOND) || '时'||
EXTRACT(MINUTE
FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))
DAY(9) TO SECOND) || '分钟'||
EXTRACT(SECOND
FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))
DAY(9) TO SECOND) || '秒'
FROM DUAL
--求指定月内哪天在日历表是对应哪周.
如:2013.04.01----2013.04.06为第一周,依次类推.
如果直接使用TO_CHAR(SYSDATE,'W')得到的周是按每个月的前7天为第一周的,这样和日历表上的有点出入.
select to_date('2013-04-01', 'yyyy-MM-dd') + level - 1,
CEIL((to_char(to_date('2013-04-01', 'yyyy-MM-dd'), 'd') + LEVEL - 1) / 7) 周
from dual
connect by level <= 30;
--表T11有A,B两个字段,A字段为空,B字段是26个字母无序排列
--要求更新A列,使得B='A'时,A=1;B='B'时,A=2;B='C'时,A=3...
UPDATE t11 c
SET a =
(select rn
from (select rank() over(order by b) rn, b from t11) d
where d.b = c.b);