LLI Wiki — My Digital Knowledge Base

Oracle: table shrink and archived log volume

Shrink a table with SHRINK SPACE and measure archived redo volume per day, hour and instance.

Reclaim space in a table and measure how much archived redo log a database generates.

Shrink a table

Row movement must be enabled before the segment can be shrunk.

alter table mytable enable row movement;
alter table mytable shrink space;

Archived log volume per day

SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME)
 ORDER BY 1;

Archived log volume per hour

alter session set nls_date_format = 'YYYY-MM-DD HH24';

SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME, 'HH')
 ORDER BY 1;

Archived log volume per day and instance (RAC)

SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
       THREAD#,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME), THREAD#
 ORDER BY 1, 2;

Average number and size of archived logs on working days

Average over the last 7 days, excluding Saturday and Sunday:

SELECT TRUNC(AVG(cnt/5)) NumberOfLogs, ROUND(AVG(GByte/5), 2) AverageLogSizeGByte
FROM (SELECT COUNT(*) Cnt, ROUND(SUM(blocks*block_size)/(1024*1024*1024), 2) GByte
FROM v$archived_log
WHERE completion_time BETWEEN SYSDATE-7 AND SYSDATE
AND to_char(completion_time, 'DY') NOT IN ('SAT', 'SUN') -- Working Days
)

Notes

Mastodon LLIMastodon SocialFosstodon