Oracle 表空间详解

表空间概述

表空间(Tablespace)是Oracle数据库中最大的逻辑存储单位，用于存储数据库对象(如表、索引等)。一个数据库通常包含多个表空间，每个表空间由一个或多个数据文件组成。

表空间类型

1. 永久表空间(Permanent Tablespaces)

存储永久性数据库对象，如表、索引等。

• SYSTEM表空间：存储数据字典等Oracle系统对象
• SYSAUX表空间：SYSTEM的辅助表空间
• 用户表空间：用户自定义的普通表空间

2. 临时表空间(Temporary Tablespaces)

用于存储临时数据，如排序操作产生的中间结果。

• TEMP表空间：默认临时表空间
• 用户可创建其他临时表空间

3. 撤销表空间(Undo Tablespaces)

存储撤销信息，用于事务回滚和读一致性。

• UNDOTBS1：默认撤销表空间

4. 大文件表空间(Bigfile Tablespaces)

单个大数据文件(最大可达32TB或128TB)的表空间。

5. 小文件表空间(Smallfile Tablespaces)

传统表空间，可包含多个数据文件(每个最大32GB)。

常用表空间操作

创建表空间

-- 创建永久表空间
CREATE TABLESPACE users01 
DATAFILE '/u01/oracle/data/users01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE temp01
TEMPFILE '/u01/oracle/data/temp01.dbf' SIZE 50M
AUTOEXTEND ON;

修改表空间

-- 添加数据文件
ALTER TABLESPACE users01 
ADD DATAFILE '/u01/oracle/data/users02.dbf' SIZE 200M;

-- 重设大小
ALTER DATABASE DATAFILE '/u01/oracle/data/users01.dbf' RESIZE 500M;

-- 设置为只读
ALTER TABLESPACE users01 READ ONLY;

删除表空间

DROP TABLESPACE users01 INCLUDING CONTENTS AND DATAFILES;

表空间管理最佳实践

1. 分离系统数据和用户数据：用户对象不应存储在SYSTEM或SYSAUX表空间
2. 分离表数据和索引：为表和索引创建不同的表空间
3. 考虑I/O分布：将频繁访问的表空间分布在不同的物理磁盘上
4. 合理设置自动扩展：避免空间不足问题，但也要控制最大大小
5. 定期监控空间使用：使用DBA_FREE_SPACE等视图监控空间使用情况

查询表空间信息

-- 查看所有表空间
SELECT tablespace_name, status, contents FROM dba_tablespaces;

-- 查看表空间使用情况
SELECT a.tablespace_name, 
       a.bytes/1024/1024 "Total MB",
       (a.bytes-b.bytes)/1024/1024 "Used MB",
       b.bytes/1024/1024 "Free MB",
       round(((a.bytes-b.bytes)/a.bytes)*100,2) "Percent Used"
FROM (SELECT tablespace_name, sum(bytes) bytes
      FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, sum(bytes) bytes
      FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name;

表空间与用户关系

每个用户都有一个默认表空间和临时表空间：

-- 创建用户时指定表空间
CREATE USER scott IDENTIFIED BY tiger
DEFAULT TABLESPACE users01
TEMPORARY TABLESPACE temp01
QUOTA UNLIMITED ON users01;

-- 修改用户默认表空间
ALTER USER scott DEFAULT TABLESPACE users02;