oracle 收缩数据文件 datafile

SELECT df.tablespace_name, df.file_id, df.file_name, df.AUTOEXTENSIBLE, df.bytes / 1024 / 1024 / 1024 AS current_size_gb, e.max_block * 8192 /1024/ 1024 / 1024 AS current_used_gb, -- 实际用到的位置 ROUND((df.bytes - e.max_block * 8192) / 1024 / 1024 / 1024, 2) AS can_shrink_gb, -- 建议收缩到大小（实际使用 + 1G 安全余量） CEIL(e.max_block * 8192 /1024 / 1024 / 1024) + 1 AS resize_to_gb, 'alter database datafile '''||df.FILE_NAME||''' resize '||(CEIL(e.max_block * 8 / 1024 / 1024) + 1) || 'G;' as ddl_resize FROM dba_data_files df, ( SELECT file_id, MAX(block_id + blocks - 1) max_block FROM dba_extents GROUP BY file_id ) e WHERE 1=1 and df.file_id = e.file_id and df.tablespace_name NOT IN ('SYSTEM','SYSAUX','UNDOTBS1') ORDER BY can_shrink_gb DESC;

收缩前需要检查下回收站

select a.* from dba_recyclebin a where 1=1 ;

select 'PURGE TABLE "'||a.owner||'"."'||a.object_name||'";' as ddl_purge, a.* from dba_recyclebin a where 1=1 and a.type='TABLE' order by a.owner,a.original_name ;