Oracle--表空间Tablespace

在 Oracle 数据库中，表空间（Tablespace） 是一种逻辑存储结构，用于组织和管理数据库中物理存储数据文件的方式。以下是表空间相关操作的详细介绍，包括创建、修改、删除、查询以及常见问题处理。

1. 表空间的作用

• 提供逻辑存储结构，便于管理和分配数据库的物理存储。
• 允许不同的用户或应用程序存储各自的数据。
• 提高存储灵活性，例如对表空间进行备份、扩展、压缩等操作。

2. 表空间的类型

1. 永久表空间：存储永久对象（如表、索引）。
   • 例：USERS、DATA。
2. 临时表空间：存储排序或查询中间结果。
   • 例：TEMP。
3. 撤销表空间（Undo Tablespace）：用于事务回滚或恢复。
   • 例：UNDOTBS1。

3. 表空间的操作

(1) 创建表空间

使用 CREATE TABLESPACE 语句创建一个永久表空间。

示例：创建普通表空间

CREATE TABLESPACE my_tablespace DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

• DATAFILE：指定物理数据文件路径。
• SIZE：初始大小。
• AUTOEXTEND：开启自动扩展功能。
• MAXSIZE：设置表空间最大容量。
• EXTENT MANAGEMENT：指定段空间管理方式（LOCAL 表示本地管理）。

示例：创建临时表空间

CREATE TEMPORARY TABLESPACE temp_tablespace TEMPFILE '/u01/app/oracle/oradata/mydb/temp01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

示例：创建撤销表空间

CREATE UNDO TABLESPACE undo_tablespace DATAFILE '/u01/app/oracle/oradata/mydb/undo01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

(2) 修改表空间

可以调整表空间的大小、状态或属性。

1. 扩展表空间

通过增加数据文件或扩展现有文件来增加容量。

• 扩展数据文件大小：

  ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace01.dbf' RESIZE 200M;

• 添加新的数据文件：

  ALTER TABLESPACE my_tablespace ADD DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace02.dbf' SIZE 100M AUTOEXTEND ON;

2. 修改表空间状态

• 使表空间只读：

  ALTER TABLESPACE my_tablespace READ ONLY;

• 使表空间可写：

  ALTER TABLESPACE my_tablespace READ WRITE;

3. 更改默认临时表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_tablespace;

4. 更改默认撤销表空间

ALTER SYSTEM SET UNDO_TABLESPACE = undo_tablespace;

(3) 删除表空间

删除表空间时需要注意安全性。

删除表空间及其内容

• 包括物理文件：

  DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES;

• 仅删除表空间逻辑结构：

  DROP TABLESPACE my_tablespace;

删除临时表空间

DROP TABLESPACE temp_tablespace INCLUDING CONTENTS AND DATAFILES;

(4) 查询表空间信息

查询表空间使用情况

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb, autoextensible FROM dba_data_files;

查询表空间使用率

SELECT tablespace_name, ROUND((total_bytes - free_bytes) / total_bytes * 100, 2) AS used_percent FROM ( SELECT tablespace_name, SUM(bytes) AS total_bytes, SUM(DECODE(free_space, NULL, 0, bytes)) AS free_bytes FROM ( SELECT tablespace_name, bytes, NULL AS free_space FROM dba_data_files UNION ALL SELECT tablespace_name, NULL, bytes FROM dba_free_space ) GROUP BY tablespace_name );

查询临时表空间使用情况

SELECT tablespace_name, used_blocks, free_blocks FROM v$temp_space_header;

4. 常见问题及解决方案

(1) 表空间满

• 现象：插入数据时报错 ORA-01653: unable to extend table … in tablespace …。
• 解决办法：
  • 检查表空间使用情况。
  • 增加数据文件或扩展现有文件：

    ALTER DATABASE DATAFILE '/path/to/file.dbf' RESIZE 500M;

(2) 数据文件损坏

• 现象：启动数据库或查询表时报错 ORA-01157: cannot identify/lock data file。
• 解决办法：
  • 检查损坏的文件路径：

    SELECT * FROM v$datafile WHERE status = 'RECOVER';

  • 从备份中恢复文件。

(3) 表空间无法自动扩展

• 现象：文件达到大小限制。
• 解决办法：
  • 检查并启用自动扩展：

    ALTER DATABASE DATAFILE '/path/to/file.dbf' AUTOEXTEND ON;

5. 管理建议

1. 规划表空间：根据应用需求划分表空间，避免所有对象集中在一个表空间。
2. 启用自动扩展：为数据文件启用自动扩展功能，减少人工干预。
3. 监控使用率：定期监控表空间的使用情况，及时扩展或优化。
4. 做好备份：确保关键数据表空间的备份策略完备，防止文件丢失或损坏。

通过合理管理表空间，可以提升数据库的性能和可维护性，确保业务运行稳定。