<think>我们被要求检查
Oracle数据库
表空间中的
碎片化情况。根据引用[1]，
表空间碎片主要指的是空间浪费问题，而不是性能问题。因此，我们的目标是找出
表空间中存在的
碎片，即那些无法被有效利用的空闲空间。
在
Oracle中，
表空间碎片通常表现为：
- 存在大量小的空闲区间（extents），这些区间太小以至于无法被分配给新的段（segment）或扩展已有段。
- 虽然总的空闲空间可能很大，但由于分散成许多小片，导致无法分配连续的大块空间。
检查
表空间碎片的方法通常包括：
1.
查看表空间的空闲空间分布情况。
2. 计算
碎片程度（如通过空闲空间的数量和大小分布）。
我们可以通过以下步骤进行：
### 方法1：使用DBA_FREE_SPACE视图分析
碎片
```sql
SELECT tablespace_name,
COUNT(*) AS fragments, --
碎片数量
SUM(bytes) AS total_bytes,
MAX(bytes) AS max_bytes,
MIN(bytes) AS min_bytes
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY fragments DESC;
```
### 方法2：详细分析每个
表空间的空闲
碎片分布
```sql
SELECT tablespace_name,
block_id,
bytes,
blocks,
CASE WHEN bytes < 1024*1024 THEN '小于1M'
WHEN bytes < 10*1024*1024 THEN '1M-10M'
ELSE '大于10M'
END AS fragment_size_category
FROM dba_free_space
ORDER BY tablespace_name, bytes DESC;
```
### 方法3：计算每个
表空间的
碎片化程度
我们可以使用一个更复杂的查询，计算每个
表空间的
碎片化指数（例如，空闲空间的标准差与平均值的比值，或者最大
碎片占整个空闲空间的比例等）。
```sql
SELECT tablespace_name,
COUNT(*) AS fragment_count,
SUM(bytes) AS total_free_bytes,
MAX(bytes) AS largest_free_chunk,
ROUND(MAX(bytes) / SUM(bytes) * 100, 2) AS pct_largest,
ROUND(AVG(bytes), 2) AS avg_free_bytes,
ROUND(STDDEV(bytes) / AVG(bytes), 2) AS fragmentation_index --
碎片化指数（变异系数）
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY fragmentation_index DESC;
```
**解释**：
- `fragmentation_index`（变异系数）表示
碎片大小的离散程度，值越大表示
碎片大小差异越大，
碎片化程度可能越高（但注意，这个指标只能作为参考，因为如果有很多小
碎片，即使大小相似，也会导致无法分配大段空间）。
- `pct_largest`：最大空闲块占总空闲空间的百分比。这个值越小，说明空闲空间越分散。
### 方法4：使用
Oracle提供的脚本（utlfsmg.sql）
Oracle提供了一个脚本`utlfsmg.sql`（通常在`$
ORACLE_HOME/rdbms/admin`目录下）来检查
表空间碎片。我们可以运行这个脚本，它会生成一个报告，显示每个
表空间的空闲空间分布情况。
### 注意事项
1. 需要DBA权限才能访问`dba_free_space`视图。
2.
表空间碎片化严重可能导致无法分配新的扩展（extent），尤其是当需要分配一个较大的连续空间时。
3. 对于自动段空间管理的
表空间（ASSM），
碎片问题通常由
Oracle自动处理，但自由空间
碎片（即
表空间级别的
碎片）仍然需要关注。
###
碎片整理建议
如果发现
碎片化严重，可以考虑以下操作：
- 合并空闲空间（Coalesce）：使用`ALTER TABLESPACE <tablespace_name> COALESCE;`命令（仅对字典管理的
表空间有效）。
- 对于本地管理的
表空间，如果使用统一分配（uniform size）则
碎片较少，如果是系统自动分配（autoallocate），则可能需要重建
表空间（将数据导出，删除
表空间，再重新创建并导入数据）。
- 使用`ALTER DATABASE DATAFILE '<datafile_path>' RESIZE;`命令来缩小数据文件（如果有大量空闲空间在文件末尾）。
### 输出示例
```
TABLESPACE_NAME FRAGMENT_COUNT TOTAL_FREE_BYTES LARGEST_FREE_CHUNK PCT_LARGEST AVG_FREE_BYTES FRAGMENTATION_INDEX
--------------- -------------- ---------------- ------------------ ----------- -------------- --------------------
SYSAUX 15 524288000 104857600 20.00 34952533.33 0.85
USERS 8 209715200 104857600 50.00 26214400 0.6
SYSTEM 1 104857600 104857600 100.00 104857600 0
```
### 相关问题
§§相关问题§§
1. 如何合并
Oracle表空间的
碎片？
2.
Oracle本地管理和字典管理
表空间在
碎片管理上有何区别？
3. 如何自动监控
Oracle表空间碎片？
4.
表空间碎片与段
碎片（行链接、行迁移）有何不同？</think>### 检查
Oracle 表空间碎片化情况的方法
#### 方法1：使用 DBA_FREE_SPACE 视图（推荐）
```sql
SELECT
tablespace_name AS "
表空间",
COUNT(*) AS "
碎片数量",
SUM(bytes) / 1024 / 1024 AS "总空闲空间(MB)",
MAX(bytes) / 1024 / 1024 AS "最大空闲块(MB)",
ROUND(MAX(bytes) / SUM(bytes) * 100, 2) AS "最大块占比(%)",
ROUND((SUM(bytes) - MAX(bytes)) / SUM(bytes) * 100, 2) AS "
碎片化率(%)"
FROM dba_free_space
WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX') -- 排除系统
表空间
GROUP BY tablespace_name
HAVING COUNT(*) > 10 -- 只显示
碎片较多的
表空间
ORDER BY "
碎片化率(%)" DESC;
```
**关键指标说明**：
| 指标 | 警戒值 | 说明 |
|------|--------|------|
|
碎片数量 | >50 |
碎片数量越多，空间利用率越低 |
|
碎片化率 | >30% | 表示空闲空间被分散成小
碎片 |
| 最大块占比 | <20% | 最大连续空间占比较小需关注 |
#### 方法2：详细
碎片分布分析
```sql
SELECT
tablespace_name,
block_id,
bytes / 1024 / 1024 AS size_mb,
blocks,
CASE
WHEN bytes < 1048576 THEN '小于1MB'
WHEN bytes BETWEEN 1048576 AND 5242880 THEN '1-5MB'
WHEN bytes BETWEEN 5242881 AND 10485760 THEN '5-10MB'
ELSE '大于10MB'
END AS fragment_size
FROM dba_free_space
WHERE tablespace_name = 'YOUR_TABLESPACE' -- 指定
表空间
ORDER BY bytes DESC;
```
#### 方法3：自动生成
碎片报告
```sql
SET SERVEROUTPUT ON
DECLARE
v_tbs_name VARCHAR2(30) := 'USERS'; -- 指定
表空间名
v_frag_count NUMBER;
v_total_free NUMBER;
v_max_chunk NUMBER;
BEGIN
SELECT COUNT(*), SUM(bytes), MAX(bytes)
INTO v_frag_count, v_total_free, v_max_chunk
FROM dba_free_space
WHERE tablespace_name = v_tbs_name;
DBMS_OUTPUT.PUT_LINE('
表空间: ' || v_tbs_name);
DBMS_OUTPUT.PUT_LINE('
碎片数量: ' || v_frag_count);
DBMS_OUTPUT.PUT_LINE('总空闲空间: ' || ROUND(v_total_free/1024/1024) || ' MB');
DBMS_OUTPUT.PUT_LINE('最大连续空间: ' || ROUND(v_max_chunk/1024/1024) || ' MB');
DBMS_OUTPUT.PUT_LINE('
碎片化率: ' ||
ROUND((v_total_free - v_max_chunk) / v_total_free * 100, 2) || '%');
END;
/
```
####
碎片化判断标准
1. **轻度
碎片化** (<20%)：不需要立即处理
2. **中度
碎片化** (20%-40%)：建议监控
3. **严重
碎片化** (>40%)：需要立即处理[^1]
####
碎片整理建议
1. **合并空闲空间**：
```sql
ALTER TABLESPACE users COALESCE; -- 合并
碎片
```
2. **重建大表**（针对段
碎片）：
```sql
ALTER TABLE large_table MOVE TABLESPACE users;
```
3. **使用在线重定义**（业务不中断）：
```sql
EXEC DBMS_REDEFINITION.start_redef_table('SCHEMA','TABLE','TEMP_TABLE');
```
4. **调整存储参数**：
```sql
ALTER TABLE my_table STORAGE (INITIAL 64K NEXT 64K);
```
> ⚠️ **重要提示**：
Oracle 的 ASSM（自动段空间管理）
表空间通常不需要手动处理
碎片，系统会自动优化空间利用率[^2]。
###
碎片监控自动化脚本
```sql
CREATE TABLE tbs_frag_history AS
SELECT
SYSDATE AS check_date,
tablespace_name,
COUNT(*) AS frag_count,
SUM(bytes) AS total_free,
MAX(bytes) AS max_chunk
FROM dba_free_space
GROUP BY tablespace_name;
```
###
扫一扫
150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
