Oracle 10g删除用户后如何不删除表空间即可回收对应占用空间

问题1：删除用户/schema后，该用户占用的表空间是否会自动清空？ #

Oracle 10g中，如果用户下存在schema对象，直接执行DROP USER 用户名;会触发报错，必须添加CASCADE参数才能成功删除用户，即完整命令为DROP USER devuser CASCADE;。

• 执行带CASCADE参数的删除命令时，该用户下所有存储在prod表空间的对象（表、索引、存储段等）会被同步删除，这些对象占用的存储空间会被标记为表空间的空闲可用空间，可直接被同表空间下的其他用户（如produser）的新数据写入、对象扩展请求使用，无需额外操作。
• 注意：此处的「自动清空」仅指空间回归表空间的空闲资源池，不会自动缩小表空间对应数据文件的物理大小，数据文件的物理尺寸不会主动变更。

问题2：不删除表空间及对应数据文件的前提下，回收已删除用户占用的表空间操作方案 #

前置校验 #

首先使用DBA权限账号执行以下SQL，确认已删除用户的对象是否全部清理：

SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb 
FROM dba_segments 
WHERE owner = 'DEVUSER' AND tablespace_name = 'PROD';

如果返回结果为空，说明该用户的所有对象已被删除，占用空间已经回归表空间空闲池。

分场景操作方案 #

场景1：仅需要空闲空间可被同表空间其他用户使用

无需额外操作，删除用户时的CASCADE参数已经完成空间释放，后续同表空间的业务操作会直接使用这些空闲块。

场景2：需要合并表空间碎片、提升空间使用效率（可选）

如果删除的用户占用空间较大，产生了大量离散的空闲碎片，可执行以下操作合并碎片：

1. 查询表空间当前空闲碎片情况：
   SELECT SUM(bytes)/1024/1024 AS free_mb, COUNT(*) AS fragment_count FROM dba_free_space WHERE tablespace_name = 'PROD';
2. 执行在线碎片合并操作，不影响现有业务运行：
   ALTER TABLESPACE prod COALESCE;

该操作仅会合并相邻的空闲扩展区，不会修改数据文件物理大小，也不会影响produser的正常业务数据。

场景3：需要释放数据文件空闲空间给操作系统（可选，不删除数据文件）

如果确认表空间未来不需要占用当前的物理尺寸，可在不删除表空间和数据文件的前提下缩小数据文件的物理大小：

1. 查询表空间数据文件路径、当前大小、可收缩的最小尺寸：

SELECT file_name, 
       bytes/1024/1024 AS current_size_mb,
       (bytes - free_space)/1024/1024 AS used_size_mb,
       CEIL((bytes - free_space)/1024/1024) + 10 AS min_resize_mb -- 预留10M余量避免执行报错
FROM dba_data_files f
JOIN (SELECT file_id, SUM(bytes) AS free_space FROM dba_free_space WHERE tablespace_name = 'PROD' GROUP BY file_id) fs
ON f.file_id = fs.file_id
WHERE f.tablespace_name = 'PROD';

2. 执行数据文件收缩操作，将查询得到的参数替换到对应位置：
   ALTER DATABASE DATAFILE '替换为上一步查询到的prod表空间数据文件全路径' RESIZE 替换为上一步得到的min_resize_mb数值M;

若执行时报错，说明数据文件尾部存在已使用的业务块，需要先迁移对应段的位置后再执行收缩，刚删除用户的场景下一般可直接执行成功。

内容的提问来源于stack exchange，提问作者Balkrishna