Oracle 19c入门学习教程，从入门到精通， Oracle 表空间与数据文件管理详解(9）-酒店常州论坛

Oracle 表空间与数据文件管理详解

本章系统讲解 Oracle 数据库中表空间（Tablespace）与数据文件（Datafile）的结构、关系及管理方法，涵盖永久表空间、撤销表空间、临时表空间的创建、维护与优化，是数据库存储架构设计和性能调优的核心内容。

一、环境准备与前提条件

1. 安装要求

已安装Oracle Database Enterprise/Standard Edition（如 19c/21c）
具备SYSDBA 权限
操作系统有足够磁盘空间（建议/u01,/u02等挂载点）

2. 连接数据库

su- oracle sqlplus / as sysdba SQL>STARTUP;-- 若未启动

二、表空间与数据文件的关系

概念	说明
表空间（Tablespace）	逻辑存储单元，包含一个或多个数据文件
数据文件（Datafile）	物理文件（.dbf），存储实际数据
关系	1 个表空间 → N 个数据文件 1 个数据文件 → 仅属于 1 个表空间

✅设计原则：
将不同应用的数据分离到不同表空间
I/O 密集型表空间使用独立磁盘

三、Oracle 默认表空间

1. SYSTEM 表空间

作用：存储数据字典（如DBA_TABLES）、系统回滚段等
特点
：
- 创建数据库时自动创建
- 禁止存放用户对象
- 必须处于ONLINE状态

2. SYSAUX 表空间

作用：辅助 SYSTEM，存放 OEM、AWR、Statspack 等工具数据
优势：减轻 SYSTEM 负担，提高可维护性

-- 查看默认表空间SELECTproperty_name,property_valueFROMdatabase_propertiesWHEREproperty_nameLIKE'%DEFAULT%';

输出示例：

PROPERTY_NAME PROPERTY_VALUE --------------------------- -------------- DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_TEMP_TABLESPACE TEMP

四、创建表空间

1. 基本语法

CREATETABLESPACEtablespace_name DATAFILE'file_path'SIZE size[AUTOEXTENDONNEXTincrement MAXSIZE max_size][EXTENT MANAGEMENT {LOCAL|DICTIONARY }][SEGMENT SPACE MANAGEMENT { AUTO|MANUAL }];

2. 通过本地化管理方式创建（推荐）

本地管理表空间（LMT）：使用位图管理区（Extent），性能优于字典管理。

-- 创建本地管理表空间（默认）CREATETABLESPACEtbs_app DATAFILE'/u01/oradata/ORCL/tbs_app01.dbf'SIZE500M AUTOEXTENDONNEXT100M MAXSIZE2G EXTENT MANAGEMENTLOCAL;-- 显式指定（实际为默认）

✅ 优势：避免递归 SQL，减少争用。

3. 通过段空间管理方式创建

SEGMENT SPACE MANAGEMENT AUTO：使用位图管理段内空间（推荐）
MANUAL：使用空闲列表（Freelist）（旧方式，不推荐）

-- 创建自动段空间管理表空间（ASSM）CREATETABLESPACEtbs_user DATAFILE'/u01/oradata/ORCL/tbs_user01.dbf'SIZE1G EXTENT MANAGEMENTLOCALSEGMENT SPACE MANAGEMENT AUTO;-- 默认值

💡 ASSM 自动处理并发插入，避免“缓冲区忙等待”。

4. 创建非标准块表空间

Oracle 默认块大小由DB_BLOCK_SIZE决定（通常 8KB）。
可创建2KB、16KB、32KB等非标准块表空间（需先设置DB_nK_CACHE_SIZE）。

步骤1：设置缓存池

-- 设置 16KB 缓存池（动态）ALTERSYSTEMSETdb_16k_cache_size=100M;

步骤2：创建非标准块表空间

CREATETABLESPACEtbs_large_block DATAFILE'/u01/oradata/ORCL/tbs_large01.dbf'SIZE500M BLOCKSIZE16K;-- 必须匹配已配置的缓存池

📌 适用场景：数据仓库大行表、LOB 存储。

5. 建立大文件表空间（Bigfile Tablespace）

特点：
- 仅含1 个数据文件
- 文件最大可达128TB（8KB 块）
- 适用于超大数据库（VLDB）

-- 创建大文件表空间CREATEBIGFILETABLESPACEtbs_big DATAFILE'/u02/oradata/ORCL/tbs_big.dbf'SIZE10G AUTOEXTENDONNEXT1G;

⚠️ 注意：不能与小文件表空间混用；RMAN 备份策略需调整。

五、维护表空间与数据文件

1. 设置默认表空间

-- 设置数据库默认永久表空间ALTERDATABASEDEFAULTTABLESPACEtbs_user;-- 设置默认临时表空间ALTERDATABASEDEFAULTTEMPORARYTABLESPACEtemp_new;

新建用户若未指定DEFAULT TABLESPACE，将使用此默认值。

2. 更改表空间状态

状态	作用	命令
ONLINE	正常访问	`ALTER TABLESPACE tbs_name ONLINE;`
OFFLINE	不可用（用于维护）	`ALTER TABLESPACE tbs_name OFFLINE;`
READ ONLY	只读（用于备份）	`ALTER TABLESPACE tbs_name READ ONLY;`
READ WRITE	恢复读写	`ALTER TABLESPACE tbs_name READ WRITE;`

-- 示例：将表空间设为只读进行备份ALTERTABLESPACEtbs_appREADONLY;-- ... 执行备份 ...ALTERTABLESPACEtbs_appREADWRITE;

3. 重命名表空间

-- 重命名（Oracle 10g+ 支持）ALTERTABLESPACEtbs_oldRENAMETOtbs_new;

✅ 优点：无需导出/导入数据。

4. 删除表空间

-- 删除表空间及数据文件（谨慎！）DROPTABLESPACEtbs_test INCLUDING CONTENTSANDDATAFILES;

⚠️INCLUDING CONTENTS：删除所有对象
⚠️AND DATAFILES：同时删除操作系统文件

5. 维护数据文件

(1) 添加数据文件

ALTERTABLESPACEtbs_appADDDATAFILE'/u02/oradata/ORCL/tbs_app02.dbf'SIZE500M AUTOEXTENDON;

(2) 调整数据文件大小

-- 手动扩容ALTERDATABASEDATAFILE'/u01/oradata/ORCL/tbs_app01.dbf'RESIZE1G;-- 开启自动扩展ALTERDATABASEDATAFILE'/u01/oradata/ORCL/tbs_app01.dbf'AUTOEXTENDONNEXT100M MAXSIZE UNLIMITED;

(3) 移动/重命名数据文件

-- 1. OFFLINE 表空间ALTERTABLESPACEtbs_app OFFLINE;-- 2. 操作系统级移动文件-- !mv /old/path/file.dbf /new/path/file.dbf-- 3. 更新控制文件ALTERDATABASERENAMEFILE'/old/path/file.dbf'TO'/new/path/file.dbf';-- 4. ONLINE 表空间ALTERTABLESPACEtbs_app ONLINE;

六、管理撤销表空间（Undo Tablespace）

1. 撤销表空间的作用

存储DML 操作前的旧值（Before Image）
支持回滚（ROLLBACK）、读一致性（Read Consistency）、闪回查询

2. 初始化参数

参数	说明
`UNDO_MANAGEMENT`	`AUTO`（自动）或`MANUAL`（手动）
`UNDO_TABLESPACE`	指定当前撤销表空间
`UNDO_RETENTION`	保留时间（秒，默认 900）

-- 查看参数SHOWPARAMETER undo;

3. 基本操作

(1) 创建撤销表空间

CREATEUNDOTABLESPACEundotbs2 DATAFILE'/u01/oradata/ORCL/undotbs02.dbf'SIZE2G AUTOEXTENDON;

(2) 切换撤销表空间

-- 动态切换（无需重启）ALTERSYSTEMSETundo_tablespace=undotbs2;

(3) 删除旧撤销表空间

-- 确保未被使用DROPTABLESPACEundotbs1 INCLUDING CONTENTSANDDATAFILES;

💡 监控撤销使用：

SELECTtablespace_name,status,sum(bytes)/1024/1024ASmbFROMdba_undo_extentsGROUPBYtablespace_name,status;

七、管理临时表空间（Temporary Tablespace）

1. 临时表空间简介

存储排序（ORDER BY）、哈希连接、临时表的中间结果
数据在会话结束时自动清除
文件扩展名为.tmp

2. 创建临时表空间

-- 创建临时表空间CREATETEMPORARYTABLESPACEtemp_new TEMPFILE'/u01/oradata/ORCL/temp_new01.dbf'SIZE500M AUTOEXTENDON;-- 设置为默认临时表空间ALTERDATABASEDEFAULTTEMPORARYTABLESPACEtemp_new;

⚠️ 注意：使用TEMPFILE而非DATAFILE

3. 查询临时表空间信息

-- 临时表空间及文件SELECTtablespace_name,file_name,bytes/1024/1024ASsize_mbFROMdba_temp_files;-- 临时段使用情况SELECTs.username,u.tablespace,u.extents,u.blocksFROMv$sessions,v$tempseg_usage uWHEREs.saddr=u.session_addr;

4. 管理临时表空间组（Oracle 10g+）

允许多个临时表空间组成组（Group），提高并发排序能力。

(1) 创建临时表空间组

-- 创建时指定组CREATETEMPORARYTABLESPACEtemp1 TEMPFILE'/u01/oradata/ORCL/temp1.dbf'SIZE500MTABLESPACEGROUPtemp_group;CREATETEMPORARYTABLESPACEtemp2 TEMPFILE'/u01/oradata/ORCL/temp2.dbf'SIZE500MTABLESPACEGROUPtemp_group;

(2) 将用户分配到组

-- 用户使用整个组ALTERUSERscottTEMPORARYTABLESPACEtemp_group;

(3) 查看组信息

SELECT*FROMdba_tablespace_groups;

八、综合性实战案例

案例：为 OLTP 系统设计高可用存储架构

需求：

创建专用应用表空间（本地管理 + ASSM）
配置独立撤销和临时表空间
设置自动扩展与监控
验证配置

步骤1：创建应用表空间

-- 永久表空间CREATETABLESPACEtbs_oltp DATAFILE'/u01/oradata/ORCL/tbs_oltp01.dbf'SIZE2G AUTOEXTENDONNEXT200M MAXSIZE10G EXTENT MANAGEMENTLOCALSEGMENT SPACE MANAGEMENT AUTO;-- 添加第二数据文件（跨磁盘）ALTERTABLESPACEtbs_oltpADDDATAFILE'/u02/oradata/ORCL/tbs_oltp02.dbf'SIZE2G AUTOEXTENDON;

步骤2：创建撤销表空间

CREATEUNDOTABLESPACEundotbs_oltp DATAFILE'/u01/oradata/ORCL/undotbs_oltp.dbf'SIZE3G AUTOEXTENDON;-- 切换并设置保留时间ALTERSYSTEMSETundo_tablespace=undotbs_oltp;ALTERSYSTEMSETundo_retention=1800;-- 30分钟

步骤3：创建临时表空间组

CREATETEMPORARYTABLESPACEtemp_oltp1 TEMPFILE'/u01/oradata/ORCL/temp_oltp1.dbf'SIZE1G AUTOEXTENDONTABLESPACEGROUPtemp_oltp_group;CREATETEMPORARYTABLESPACEtemp_oltp2 TEMPFILE'/u02/oradata/ORCL/temp_oltp2.dbf'SIZE1G AUTOEXTENDONTABLESPACEGROUPtemp_oltp_group;-- 设置为默认ALTERDATABASEDEFAULTTEMPORARYTABLESPACEtemp_oltp_group;

步骤4：创建用户并分配表空间

CREATEUSERapp_user IDENTIFIEDBYsecure_passwordDEFAULTTABLESPACEtbs_oltpTEMPORARYTABLESPACEtemp_oltp_group QUOTA UNLIMITEDONtbs_oltp;GRANTCONNECT,RESOURCETOapp_user;

步骤5：验证配置

-- 表空间列表SELECTtablespace_name,contents,statusFROMdba_tablespaces;-- 数据文件分布SELECTtablespace_name,file_name,bytes/1024/1024ASsize_mbFROMdba_data_filesWHEREtablespace_nameLIKE'%OLTP%'UNIONALLSELECTtablespace_name,file_name,bytes/1024/1024FROMdba_temp_filesORDERBY1;-- 撤销表空间状态SHOWPARAMETER undo_tablespace;

步骤6：测试临时空间使用

-- 以 app_user 登录CONNECTapp_user/secure_password-- 执行大排序（触发临时段）SELECT*FROM(SELECTobject_id,object_nameFROMall_objectsORDERBYobject_name)WHEREROWNUM<=10000;

九、总结

表空间类型	关键命令	最佳实践
永久表空间	`CREATE TABLESPACEADD DATAFILE`	LMT + ASSM，多数据文件跨磁盘
撤销表空间	`CREATE UNDO TABLESPACEALTER SYSTEM SET undo_tablespace`	独立表空间，合理设置`UNDO_RETENTION`
临时表空间	`CREATE TEMPORARY TABLESPACETABLESPACE GROUP`	使用组提升并发排序性能

💡黄金法则：
绝不使用 SYSTEM/SYSAUX 存放用户数据
所有表空间启用 AUTOEXTEND（设上限）
定期监控空间使用（DBA_FREE_SPACE）

掌握本章内容，即可构建高性能、高可用的 Oracle 存储体系，为业务系统提供坚实基础。

企业官网建设流程全解析

Oracle 表空间与数据文件管理详解

一、环境准备与前提条件

1. 安装要求

2. 连接数据库

二、表空间与数据文件的关系

三、Oracle 默认表空间

1. SYSTEM 表空间

2. SYSAUX 表空间

四、创建表空间

1. 基本语法

2. 通过本地化管理方式创建（推荐）

3. 通过段空间管理方式创建

4. 创建非标准块表空间

步骤1：设置缓存池

步骤2：创建非标准块表空间

5. 建立大文件表空间（Bigfile Tablespace）

五、维护表空间与数据文件

1. 设置默认表空间

2. 更改表空间状态

3. 重命名表空间

4. 删除表空间

5. 维护数据文件

(1) 添加数据文件

(2) 调整数据文件大小

(3) 移动/重命名数据文件

六、管理撤销表空间（Undo Tablespace）

1. 撤销表空间的作用

2. 初始化参数

3. 基本操作

(1) 创建撤销表空间

(2) 切换撤销表空间

(3) 删除旧撤销表空间

七、管理临时表空间（Temporary Tablespace）

1. 临时表空间简介

2. 创建临时表空间

3. 查询临时表空间信息

4. 管理临时表空间组（Oracle 10g+）

(1) 创建临时表空间组

(2) 将用户分配到组

(3) 查看组信息

八、综合性实战案例

案例：为 OLTP 系统设计高可用存储架构

步骤1：创建应用表空间

步骤2：创建撤销表空间

步骤3：创建临时表空间组

步骤4：创建用户并分配表空间

步骤5：验证配置

步骤6：测试临时空间使用

九、总结

热门文章

文章分类

标签云

相关文章

需要专业的网站建设服务？