15 | 03 | 2024
Latest Articles
Popular Articles

Scripts

Which session is generating the redo ?

User Rating:  / 0
PoorBest 

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.