Scripts
Which session is generating the redo ?
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 19 March 2015 16:45
-
Last Updated on Thursday, 19 March 2015 16:51
-
Published on Thursday, 19 March 2015 16:45
-
Hits: 8210
Which session is generating the redo ?
See also Archivelogs in the flash recovery area
You are a DBA and received
ORA-00257 : archiver error. Connect internal only, untill freed
Or you received an alert stating that disk space thresholds were exceeded. What happens ? Somebody probably launched a weird script, but who ?
select username, sid, value, sql_id, prev_sql_id
from ( select username,s.sid, value, sql_id, prev_sql_id
from v$session u, v$sesstat s, v$statname n
where n.name = 'redo size'
and s.statistic# = n.statistic#
and u.sid = s.sid order by value desc )
where rownum <= 10
order by value desc
/
sample output
#####################################################################################################
start session_redo
USERNAME SID VALUE SQL_ID PREV_SQL_ID
------------------------------ ---------------- ---------------- ------------- -------------
USER_11 36 79883973168 d8q3s5d280yxc bsa0wjtftg3uw
421 2487496784 chsyr0gssbuqf
301 2279584880
332 2253177672
1 189188352 fvkynu9xgck8h
124 78606792 3rw49yhahg984
331 11434928 5ms6rbzdnq16t
USER_12 430 3827844 6aukdjcd080n7
283 372028 g91c5sb5k8w3r
61 146380
#####################################################################################################
You can off course add some v$session information like osuser, program action.