印刻数据库

外观

Oracle 表空间管理 ​

概述 ​

表空间是Oracle数据库中存储数据的基本单位，合理的表空间管理是确保数据库高效运行的重要因素。表空间管理涉及到表空间的创建、扩展、收缩、监控和维护等方面。通过有效的表空间管理，可以优化存储资源使用，提高数据库性能，降低运维成本。

表空间类型 ​

1. 按管理方式分类 ​

本地管理表空间 ​

  • 使用位图管理表空间的分配和释放
  • 减少了数据字典的竞争
  • 提高了表空间的管理效率
  • 推荐用于所有新创建的表空间
  • 支持自动段空间管理

字典管理表空间 ​

  • 使用数据字典管理表空间的分配和释放
  • 存在数据字典竞争问题
  • 管理效率较低
  • 不推荐使用，仅用于向后兼容

2. 按内容分类 ​

永久表空间 ​

  • 存储永久数据，如用户表、索引等
  • 数据不会自动删除
  • 如SYSTEM、SYSAUX、USERS表空间

临时表空间 ​

  • 存储临时数据，如排序、哈希操作的中间结果
  • 数据在会话结束或事务完成后自动删除
  • 如TEMP表空间

撤销表空间 ​

  • 存储回滚数据，用于事务回滚和读一致性
  • 支持自动撤销管理
  • 如UNDO表空间

3. 按文件类型分类 ​

小文件表空间 ​

  • 最多包含1022个数据文件
  • 每个数据文件最大32GB（默认块大小）
  • 适用于大多数场景

大文件表空间 ​

  • 每个表空间只能包含一个数据文件
  • 数据文件最大可达128TB（默认块大小）
  • 适用于大型数据库
  • 减少了数据文件的管理复杂度

表空间创建 ​

1. 本地管理表空间创建 ​

sql
-- 创建本地管理的永久表空间
CREATE TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
SIZE 10G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE 100G
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建本地管理的临时表空间
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' 
SIZE 5G 
AUTOEXTEND ON NEXT 500M 
MAXSIZE 20G
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 128M;

-- 创建本地管理的撤销表空间
CREATE UNDO TABLESPACE undo
DATAFILE '/u01/app/oracle/oradata/ORCL/undo01.dbf' 
SIZE 5G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE 30G
EXTENT MANAGEMENT LOCAL;

2. 大文件表空间创建 ​

sql
-- 创建大文件永久表空间
CREATE BIGFILE TABLESPACE big_users
DATAFILE '/u01/app/oracle/oradata/ORCL/big_users.dbf' 
SIZE 100G 
AUTOEXTEND ON NEXT 10G 
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建大文件临时表空间
CREATE BIGFILE TEMPORARY TABLESPACE big_temp
TEMPFILE '/u01/app/oracle/oradata/ORCL/big_temp.dbf' 
SIZE 50G 
AUTOEXTEND ON NEXT 5G 
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 256M;

3. 表空间创建最佳实践 ​

  • 使用本地管理表空间
  • 为不同类型的数据创建专用表空间
  • 合理设置初始大小和扩展参数
  • 考虑使用大文件表空间管理大型数据库
  • 为临时表空间使用统一的区大小
  • 为撤销表空间设置合理的大小

表空间扩展 ​

1. 手动扩展表空间 ​

扩展数据文件大小 ​

sql
-- 扩展现有数据文件大小
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
RESIZE 20G;

添加新的数据文件 ​

sql
-- 为表空间添加新的数据文件
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf' 
SIZE 10G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE 100G;

2. 自动扩展表空间 ​

sql
-- 启用数据文件自动扩展
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
AUTOEXTEND ON 
NEXT 1G 
MAXSIZE 100G;

-- 禁用数据文件自动扩展
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
AUTOEXTEND OFF;

3. 表空间扩展最佳实践 ​

  • 监控表空间使用率，避免空间不足
  • 为重要表空间启用自动扩展
  • 合理设置自动扩展参数
  • 定期检查自动扩展的数据文件
  • 考虑使用大文件表空间减少管理复杂度
  • 避免频繁的自动扩展操作

表空间收缩 ​

1. 数据文件收缩 ​

sql
-- 收缩数据文件（仅收缩到HWM以下）
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
RESIZE 10G;

-- 收缩前先整理表空间
ALTER TABLESPACE users COALESCE;

-- 收缩前先重建表（如果HWM过高）
ALTER TABLE scott.emp MOVE;
ALTER INDEX scott.emp_idx REBUILD;

2. 表空间释放未使用空间 ​

sql
-- 释放表空间中未使用的空间
ALTER TABLESPACE users SHRINK SPACE;

-- 释放表空间并调整数据文件大小
ALTER TABLESPACE users SHRINK SPACE KEEP 5G;

-- 单独收缩数据文件
ALTER TABLESPACE users SHRINK DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf';

3. 表空间收缩最佳实践 ​

  • 仅在必要时收缩表空间
  • 收缩前先整理表空间或重建表
  • 注意收缩操作可能影响性能
  • 避免在业务高峰期收缩表空间
  • 收缩后验证数据完整性

表空间监控 ​

1. 表空间使用情况监控 ​

sql
-- 查看表空间使用率
SELECT 
  ts.tablespace_name,
  ROUND((df.total_space - fs.free_space) / df.total_space * 100, 2) "Used %",
  ROUND(df.total_space / 1024 / 1024 / 1024, 2) "Total (GB)",
  ROUND((df.total_space - fs.free_space) / 1024 / 1024 / 1024, 2) "Used (GB)",
  ROUND(fs.free_space / 1024 / 1024 / 1024, 2) "Free (GB)"
FROM 
  (SELECT tablespace_name, SUM(bytes) free_space
   FROM dba_free_space
   GROUP BY tablespace_name) fs,
  (SELECT tablespace_name, SUM(bytes) total_space
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  dba_tablespaces ts
WHERE 
  fs.tablespace_name(+) = df.tablespace_name
  AND df.tablespace_name = ts.tablespace_name
ORDER BY "Used %" DESC;

2. 数据文件监控 ​

sql
-- 查看数据文件使用情况
SELECT 
  df.file_name,
  df.tablespace_name,
  ROUND(df.bytes / 1024 / 1024 / 1024, 2) "Size (GB)",
  ROUND(fs.bytes / 1024 / 1024 / 1024, 2) "Free (GB)",
  ROUND((df.bytes - fs.bytes) / df.bytes * 100, 2) "Used %",
  df.autoextensible,
  ROUND(df.maxbytes / 1024 / 1024 / 1024, 2) "Max Size (GB)"
FROM 
  dba_data_files df,
  (SELECT file_id, SUM(bytes) bytes FROM dba_free_space GROUP BY file_id) fs
WHERE 
  df.file_id = fs.file_id(+)
ORDER BY df.tablespace_name, df.file_name;

3. 表空间增长趋势监控 ​

sql
-- 查看表空间增长趋势（使用AWR数据）
SELECT 
  to_char(sn.end_interval_time, 'YYYY-MM-DD') "Date",
  ts.tablespace_name,
  ROUND(ts.used_space * dt.block_size / 1024 / 1024 / 1024, 2) "Used (GB)"
FROM 
  dba_hist_tbspc_space_usage ts,
  dba_hist_snapshot sn,
  dba_tablespaces dt
WHERE 
  ts.snap_id = sn.snap_id
  AND ts.tablespace_id = dt.tablespace_id
  AND ts.tablespace_name = 'USERS'
  AND sn.end_interval_time > SYSDATE - 30
ORDER BY 
  sn.end_interval_time, ts.tablespace_name;

表空间管理 ​

1. 表空间重命名 ​

sql
-- 重命名表空间
ALTER TABLESPACE old_name RENAME TO new_name;

2. 表空间读写状态修改 ​

sql
-- 将表空间设置为只读
ALTER TABLESPACE users READ ONLY;

-- 将表空间设置为读写
ALTER TABLESPACE users READ WRITE;

-- 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces;

3. 表空间脱机和联机 ​

sql
-- 将表空间脱机
ALTER TABLESPACE users OFFLINE;

-- 正常关闭表空间（等待事务完成）
ALTER TABLESPACE users OFFLINE NORMAL;

-- 立即关闭表空间（不等待事务完成）
ALTER TABLESPACE users OFFLINE IMMEDIATE;

-- 临时关闭表空间（用于介质恢复）
ALTER TABLESPACE users OFFLINE TEMPORARY;

-- 将表空间联机
ALTER TABLESPACE users ONLINE;

4. 表空间删除 ​

sql
-- 删除空表空间（不删除数据文件）
DROP TABLESPACE users;

-- 删除表空间并删除数据文件
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

-- 删除表空间并删除所有相关对象
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

5. 表空间传输 ​

sql
-- 自包含检查表
EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE);

-- 查看自包含检查结果
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

-- 将表空间设置为只读
ALTER TABLESPACE users READ ONLY;

-- 复制数据文件到目标服务器
-- 在目标数据库中导入表空间
CREATE TABLESPACE users 
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
SIZE 10G;

-- 导入表空间
IMPDP system/password@target_db 
TRANSPORT_DATAFILES='/u01/app/oracle/oradata/ORCL/users01.dbf' 
TRANSPORT_TABLESPACES=users;

-- 将表空间设置为读写
ALTER TABLESPACE users READ WRITE;

表空间优化 ​

1. 表空间布局优化 ​

  • 分离系统数据和用户数据：将系统表空间和用户表空间存储在不同的存储设备上
  • 分离热数据和冷数据：将频繁访问的数据和不频繁访问的数据存储在不同的表空间
  • 分离索引和表数据：将索引和表数据存储在不同的表空间，提高I/O性能
  • 分离日志和数据：将日志文件和数据文件存储在不同的存储设备上

2. 区和段空间管理 ​

统一区大小 ​

sql
-- 创建使用统一区大小的表空间
CREATE TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
SIZE 10G
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 64M;

自动段空间管理 ​

sql
-- 创建使用自动段空间管理的表空间
CREATE TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
SIZE 10G
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

手动段空间管理 ​

sql
-- 创建使用手动段空间管理的表空间
CREATE TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
SIZE 10G
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT MANUAL
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

3. 表空间碎片管理 ​

sql
-- 检查表空间碎片
SELECT 
  tablespace_name,
  COUNT(*) "Extent Count",
  ROUND(SUM(bytes) / 1024 / 1024, 2) "Total (MB)",
  ROUND(MAX(bytes) / 1024 / 1024, 2) "Largest Extent (MB)"
FROM 
  dba_free_space
GROUP BY 
  tablespace_name
ORDER BY 
  tablespace_name;

-- 合并表空间碎片
ALTER TABLESPACE users COALESCE;

-- 重建表以减少碎片
ALTER TABLE scott.emp MOVE;
ALTER INDEX scott.emp_idx REBUILD;

19c与21c版本表空间管理差异 ​

1. 表空间功能差异 ​

特性Oracle 19cOracle 21c
大文件表空间支持支持增强的大文件表空间支持，最大大小可达128TB
自动表空间管理支持增强的自动表空间管理功能
表空间收缩支持基本功能增强的表空间收缩功能，支持更多选项
表空间加密支持透明数据加密增强的表空间加密功能
表空间压缩支持基本压缩增强的表空间压缩，支持更多压缩类型

2. 表空间管理工具差异 ​

工具Oracle 19cOracle 21c
Enterprise Manager基本表空间管理功能增强的表空间管理功能，包括可视化表空间设计
SQL Developer基本表空间管理增强的表空间管理，支持更多操作
AWR报告包含基本表空间信息增强的表空间信息，支持更详细的分析
ADDM报告包含基本表空间建议增强的表空间建议，提供更详细的优化建议

表空间管理最佳实践 ​

1. 设计阶段最佳实践 ​

  • 为不同类型的数据创建专用表空间
  • 使用本地管理表空间
  • 考虑使用大文件表空间管理大型数据库
  • 合理设置初始大小和扩展参数
  • 为临时表空间使用统一的区大小
  • 为撤销表空间设置合理的大小

2. 日常管理最佳实践 ​

  • 监控表空间使用率，避免空间不足
  • 定期检查自动扩展的数据文件
  • 监控表空间增长趋势
  • 定期清理无用数据
  • 优化表空间布局
  • 管理表空间碎片

3. 性能优化最佳实践 ​

  • 分离热数据和冷数据
  • 分离索引和表数据
  • 优化区和段空间管理
  • 考虑使用表分区
  • 考虑使用数据压缩
  • 优化存储结构

4. 高可用性最佳实践 ​

  • 为重要表空间使用冗余存储
  • 定期备份表空间
  • 测试表空间恢复
  • 考虑使用Oracle Data Guard
  • 实施存储级别的冗余

常见问题(FAQ) ​

Q: 如何选择表空间的区大小？ ​

A: 选择表空间区大小的方法：

  • 小表空间：使用较小的区大小（如64KB-128KB）
  • 大表空间：使用较大的区大小（如1MB-128MB）
  • 临时表空间：使用统一的区大小（如128MB-256MB）
  • 考虑数据库块大小
  • 考虑表的大小和增长趋势
  • 避免过多的区数量

Q: 如何确定表空间的初始大小？ ​

A: 确定表空间初始大小的方法：

  • 根据初始数据量确定
  • 考虑数据增长趋势
  • 考虑索引和其他数据库对象的大小
  • 为自动扩展留有余地
  • 避免设置过小的初始大小

Q: 如何处理表空间碎片？ ​

A: 处理表空间碎片的方法：

  • 合并表空间碎片（仅适用于字典管理表空间）
  • 重建表和索引
  • 移动表到新的表空间
  • 考虑使用本地管理表空间
  • 优化区和段空间管理

Q: 如何监控表空间的增长？ ​

A: 监控表空间增长的方法：

  • 使用AWR报告分析历史增长趋势
  • 使用Oracle Enterprise Manager监控增长趋势
  • 创建自定义监控脚本
  • 设置表空间使用率告警
  • 定期检查数据文件大小

Q: 如何优化临时表空间的性能？ ​

A: 优化临时表空间性能的方法：

  • 使用本地管理的临时表空间
  • 使用统一的区大小
  • 为临时表空间分配足够的空间
  • 考虑创建多个临时表空间
  • 将临时表空间存储在高速存储设备上
  • 监控临时表空间使用率

总结 ​

Oracle表空间管理是数据库运维的重要组成部分，合理的表空间设计和管理可以提高数据库性能，优化存储资源使用，降低运维成本。本文介绍了Oracle表空间的类型、创建、扩展、收缩、监控和管理等方面的内容，以及19c与21c版本的差异和最佳实践，希望能够帮助DBA建立有效的表空间管理体系，保障数据库系统的稳定运行和可持续发展。