oracle表空间收缩一则处理案例

场景：操作系统相关文件系统占用马上100%，查看发现文件都是数据库的数据文件，想着把对应的表空间进行收缩下，不然100%会出现问题。

1.识别占用大量文件系统空间的表空间

-- 需关注 size_mb 大但是 pct_used 低的表空间。这些表空间实际使用率不高，是收缩的目标。

--free_mb 显示的是数据文件内当前标记为“空闲”的空间，但这部分空间通常不是能通过 RESIZE 释放给操作系统的空间。真正能释放的是 HWM 之上的空间（需要通过重组表来回收）。

SELECT
    tablespace_name,
    file_name,
    ROUND(bytes / 1024 / 1024, 2) AS size_mb,
    ROUND((bytes - NVL(free_space, 0)) / 1024 / 1024, 2) AS used_mb,
    ROUND(NVL(free_space, 0) / 1024 / 1024, 2) AS free_mb,
    ROUND((bytes - NVL(free_space, 0)) / bytes * 100, 2) AS pct_used
FROM
    (SELECT file_id, file_name, tablespace_name, bytes FROM dba_data_files)
    JOIN
    (SELECT file_id, SUM(bytes) AS free_space FROM dba_free_space GROUP BY file_id)
    USING (file_id)
ORDER BY size_mb DESC;

查看user_simis表空间下所有的段大小合计为601g，和userd_mb几乎一样。

select sum(bytes/1024/1024/1024) TSize_GB from dba_segments where tablespace_name='USER_SIMIS' ;

2.解读dba_free_space

free_mb：已经分配给oracle数据文件，尚未被数据库对象(表、索引)占用的空间。这些空间可以直接用来存储新数据或扩展现有对象，不需要向操作系统申请新的文件系统空间。现在还有89751M没有使用

文件系统空间：如创建100g数据文件，操作系统会立即在文件系统上分配这100g

数据文件内部：初始可能是只有一小部分数据库的段占用，比如10g。剩余90g是空闲的记录在free_mb

3.是否可以收缩表空间

resize只能释放数据文件物理末尾、连续的、未被使用的空间，无法释放文件中间或开头的空闲空间。

比如大部分空闲遍布在不同位置，高水位线后连续未被使用的只有很小的1g

--查找指定数据文件末尾的最大连续空闲空间 (单位 MB)：
SELECT
    ddf.file_id,
    file_name,
    ROUND((blocks * block_size) / (1024 * 1024), 2) AS current_size_mb,
    ROUND((NVL(hwm_blocks, 1) * block_size) / (1024 * 1024), 2) AS hwm_position_mb,
    ROUND((blocks - NVL(hwm_blocks, 1)) * block_size / (1024 * 1024), 2) AS resizable_space_mb -- 这就是能RESIZE释放的最大空间
FROM
    dba_data_files ddf,
    (SELECT
        file_id, MAX(block_id + blocks) AS hwm_blocks
     FROM
        dba_extents
     GROUP BY
        file_id) de,
    (SELECT value AS block_size FROM v$parameter WHERE name = 'db_block_size') p
WHERE
    ddf.file_id = de.file_id(+)
ORDER BY
    file_id;

可以发现USERSIMIS最多能缩小1.3M（文件末尾连续空闲空间），太小了，无法resize

resize可释放的空间=hwm之后的空间，即使hwm之前大量空闲块如free_mb不能释放

注意：resize不会移动hwm，释放hwm需要重组对象

4.查看碎片化较高的表

此处我们选择表：SIMIS.AC60有数据行64445542

注意：move需要2倍的空间，如果空间不够会报错。

--查找高碎片化表（按碎片空间排序）    
      SELECT owner, table_name, 
           ROUND((blocks * block_size) / 1024 / 1024, 2) AS allocated_mb,
           ROUND((num_rows * avg_row_len) / 1024 / 1024, 2) AS actual_data_mb,
           ROUND((blocks * block_size - num_rows * avg_row_len) / 1024 / 1024, 2) AS fragment_mb,
           (blocks * block_size - num_rows * avg_row_len) / (blocks * block_size) * 100 AS fragment_pct
    FROM dba_tables
    JOIN dba_tablespaces ON (dba_tables.tablespace_name = dba_tablespaces.tablespace_name)
    WHERE dba_tables.tablespace_name = 'USER_SIMIS'
      AND blocks > 0   
    ORDER BY fragment_mb DESC;