Oracle resize收缩数据文件脚本
SELECT d.tablespace_name,
a.file#,
a.name,
ceil(hwm * a.block_size) / 1024 / 1024 resizeto, --可以resize的目标大小
a.bytes / 1024 / 1024 currentmb, --当前大小
(a.bytes / 1024 / 1024) - (ceil(hwm * a.block_size) / 1024 / 1024) saveing_size_mb, --resize可以节省出的空间
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(ceil(hwm * a.block_size) / 1024 / 1024) || 'M;' resize_script --resize脚本,需要ceil取整数
FROM v$datafile a,
(SELECT file_id,
MAX(block_id + blocks - 1) hwm
FROM dba_extents de
--- WHERE de.tablespace_name = 'PDMS_MIG_DATA'
GROUP BY file_id) b,
dba_data_files d
WHERE a.file# = b.file_id
AND d.file_id = a.file#
-- AND d.tablespace_name = 'PDMS_MIG_DATA'
AND (a.bytes / 1024 / 1024) - (ceil(hwm * a.block_size) / 1024 / 1024) > 128 --释放空间大于128MB
ORDER BY 1,
2;
上述方法计算不准确时可采用下列sql
--https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/
WITH hwm AS
(
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
SELECT /*+ materialize */
ktfbuesegtsn ts#,
ktfbuefno relative_fno,
MAX(ktfbuebno + ktfbueblks - 1) hwm_blocks
FROM sys.x$ktfbue
GROUP BY ktfbuefno,
ktfbuesegtsn),
hwmts AS
(
-- join ts# with tablespace_name
SELECT NAME tablespace_name,
relative_fno,
hwm_blocks
FROM hwm
JOIN v$tablespace
USING (ts#)),
hwmdf AS
(
-- join with datafiles, put 5M minimum for datafiles with no extents
SELECT tablespace_name,
file_name,
nvl(hwm_blocks * (bytes / blocks),
5 * 1024 * 1024) hwm_bytes,
bytes,
autoextensible,
maxbytes
FROM hwmts
RIGHT JOIN dba_data_files
USING (tablespace_name, relative_fno)
-- where tablespace_name='APPS_DATA'
)
SELECT tablespace_name,
CASE
WHEN autoextensible = 'YES'
AND maxbytes >= bytes THEN -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim ' || to_char(ceil((bytes - hwm_bytes) / 1024 / 1024),
999999) || 'M from ' ||
to_char(ceil(bytes / 1024 / 1024),
999999) || 'M */ ' || 'alter database datafile ''' ||
file_name || ''' resize ' || ceil(hwm_bytes / 1024 / 1024) || 'M;'
ELSE -- generate only a comment when autoextensible is off
'/* reclaim ' || to_char(ceil((bytes - hwm_bytes) / 1024 / 1024),
999999) || 'M from ' ||
to_char(ceil(bytes / 1024 / 1024),
999999) ||
'M after setting autoextensible maxsize higher than current size for file ' ||
file_name || ' */'
END SQL
FROM hwmdf
where 1=1
and tablespace_name not like '%UNDO%'
and bytes - hwm_bytes > 1024 * 1024 * 128 -- resize only if at least 128MB can be reclaimed
ORDER BY bytes - hwm_bytes DESC
