SET SERVEROUTPUT ON
DECLARE
CURSOR IS_MON IS
SELECT
b.name, p.spid, s.sid, p.serial#, s.osuser,
s.username, s.terminal, DECODE(a.name, 'UNKNOWN', '-----', a.name)
FROM
v$process p, v$session s, v$bgprocess b,
sys.audit_actions a
WHERE
p.addr=s.paddr(+) AND b.paddr(+) = s.paddr AND
a.action = NVL(s.action, 0)
ORDER BY
sid;
p_spid v$process.spid%TYPE;
p_serial v$process.serial#%TYPE;
s_sid v$session.sid%TYPE;
s_osuser v$session.osuser%TYPE;
s_username v$session.username%TYPE;
s_terminal v$session.terminal%TYPE;
b_name v$bgprocess.name%TYPE;
a_name sys.audit_actions.name%TYPE;
BEGIN
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line('BGProg'||' '||'SPID'||' '||'SID'||' '||'Serial'||
' '||'OSUser'||' '||'UserName'||' '||'Terminal'||' '||'DB Action');
DBMS_OUTPUT.put_line('------'||' '||'----'||' '||'---'||' '||'------'||
' '||'------'||' '||'--------'||' '||'--------'||' '||'---------');
OPEN IS_MON;
LOOP
FETCH IS_MON INTO b_name, p_spid, s_sid, p_serial, s_osuser, s_username, s_terminal, a_name;
EXIT WHEN IS_MON%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD(NVL(b_name,'NULL'),8,' ')||RPAD(NVL(p_spid,'NULL'),6,' ')||
RPAD(TO_CHAR(NVL(s_sid, 0)),5,' ')||RPAD(TO_CHAR(NVL(p_serial,0)),9,' ')||
RPAD(NVL(s_osuser,'NULL'),16,' ')||RPAD(NVL(s_username,'NULL'),10,' ')||
RPAD(NVL(s_terminal,'NULL'),10,' ')||a_name);
END LOOP;
CLOSE IS_MON;
END;
/
Telegram: DB developers channel