OracleRedo & archiving

Sessions generating the most redo

Connected sessions ranked by redo generated since logon. Finds the job behind a sudden flood of archive logs.

Not yet verified. How scripts are tested

ora-redo-sessions.sql

1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A144COLUMN username   FORMAT A155COLUMN program    FORMAT A35 TRUNCATE6COLUMN sql_id     FORMAT A137COLUMN redo_mb    HEADING "REDO MB" FORMAT 99,999,9908 9SELECT * FROM (10  SELECT s.sid || ',' || s.serial# AS sid_serial,11         s.username, s.program, s.sql_id,12         ROUND(st.value / 1048576) AS redo_mb13  FROM v$sesstat st14  JOIN v$statname n ON n.statistic# = st.statistic#15  JOIN v$session s ON s.sid = st.sid16  WHERE n.name = 'redo size'17    AND st.value > 018  ORDER BY st.value DESC19) WHERE ROWNUM <= 15;

Save it as ora-redo-sessions.sql and run it with SQL> @ora-redo-sessions.

Open in denrepo

More Oracle scripts: Redo & archiving

Online redo log groups and membersEvery redo log group with its thread, sequence, archived flag, status, member file and size. More than one CURRENT per thread, or members missing…
Log switches per hour, last 7 daysA day-by-hour grid of log switches. Aim for roughly four an hour at peak; consistently more means the redo logs are too small.
Archived redo generated per dayCount and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the…
Redo generated per hourActual redo volume per hour for the last 7 days, from archived log sizes on the local destination, so early log switches and Data Guard copies don't…