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
- The
DYday abbreviations depend on the session language (NLS_DATE_LANGUAGE).