数据库相关概念系列--1. 表空间（Oracle）

最新推荐文章于 2024-05-21 14:54:17 发布

ItsJingwen

最新推荐文章于 2024-05-21 14:54:17 发布

阅读量138

点赞数

分类专栏：数据库相关概念文章标签：数据库 oracle

本文链接：https://blog.csdn.net/qq_39030233/article/details/135072257

版权

数据库相关概念专栏收录该内容

2 篇文章 0 订阅

订阅专栏

表空间

一、概念

Oracle的表空间是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块（块是Oracle中最小的存储单位。块大小可以在创建表空间时指定，一旦指定就不能更改）。等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用）

一个表空间由多个数据文件（data file）组成，每个数据文件可以存储一个或多个表空间，这些数据文件可以位于同一个磁盘上或者不同的磁盘上。
每个表空间都有自己的名字和属性，表空间是由表空间名称、数据文件名称、数据文件大小、数据文件路径等组成的。
表空间还可以设置自动扩展属性，当表空间中的空间不足时，可以自动增加数据文件的大小。此外，表空间还可以设置存储参数，如表空间的初始大小、最大大小、增量大小等。
在 Oracle 11g 及之前的版本中，通常有四种类型的表空间：SYSTEM、SYSAUX、TEMP 和 USER，默认情况下每个数据库都会包含这四种表空间。从 Oracle 12c 开始，还增加了 UNDO 表空间。

二、分类

2.1按照管理权限方面划分

按照管理权限来划分时，将其分为系统表空间和用户表空间两种类型。

2.1.1系统表空间

系统表空间是被 Oracle 管理员使用的特殊表空间，存储了数据库自身运行所需的数据和对象，以及系统元数据等。系统表空间包括 SYSTEM 表空间和 SYSAUX 表空间两种类型，主要包含如下的数据库对象:

数据库元数据：这些元数据包括系统表、系统视图以及一些其他的元数据定义，用于实现 Oracle 数据库自身的基本功能。
系统表：Oracle 自带的一些常见表，通常存储有关 Oracle 实例、用户、安全性、备用设备等信息。
系统索引：实际上是一个特殊类型的表，用于加快系统表查询，提高系统效率。
控制文件：Oracle 数据库控制文件，它保存了数据库名称、创建日期、日志序列号、当前数据库日志组数等关键信息。

2.1.2用户表空间

用户表空间是由用户创建的，用于存储用户数据和对象，如表、索引、视图等。一般情况下，一个 Oracle 数据库中可以存在多个用户表空间。同时用户表空间中也包含如下的数据库对象:

数据表：是最常见的数据库对象类型之一。用户可以创建自己的数据表，并在其中存储数据。
索引：用于加快数据表中数据的检索速度，提高系统性能。
视图：类似于数据表，但是不能向其中直接插入新行或删除行。
序列：用于生成唯一标识符，常用于主键或其它数据列上。
存储过程和函数：由 PL/SQL 编写并编译为可执行代码的程序单元。
Package： PL/SQL 中对存储过程和函数进行组合，在数据库中作为一个单独的对象存在。
用户定义类型：是用户自定义数据类型，可以像基本类型一样使用

2.2按照分区策略方面划分

从分区策略方面考虑，Oracle 中表空间可以按照以下4种类型进行分类，包括 SYSTEM 表空间、SYSAUX 表空间、TEMP 表空间、UNDO 表空间等。

2.2.1 SYSTEM 表空间

系统表空间的重要组成部分，存储数据库元数据和控制信息等，是整个数据库结构中必不可少的一环。包含了所有的系统元数据，例如系统数据字典，Oracle 内核代码等，这些元素都是 Oracle 系统负责维护并不提供给用户修改和访问的。

2.2.2 SYSAUX 表空间

第二个重要的系统表空间，主要用于存储系统管理数据、高级队列、日志记录信息、数据挖掘模型等。也是存储Oracle数据库的一些辅助系统对象，如Oracle Enterprise Manager的性能监控数据、日志信息等。同时是环境数据的存放，当 Oracle 安装有更多的组件或者安装更多的应用程序时，使用 SYSAUX 表空间扩展系统表空间以支持 Oracle 11g 以后新的 Oracle 功能。

2.2.3 TEMP 表空间

临时表空间，用于存放查询操作过程中生成的临时表或排序表等中间结果，执行完成后会自动清空。用于存储排序操作、合并 JOIN 操作产生的临时文件和表，在查询非常复杂，且需要大量的临时空间支持的情况下，会涉及到读写 TEMP 表空间。因此主要是用于存储临时数据，如排序操作、连接操作等。

2.2.4 UNDO 表空间

撤销表空间，用于存储在事务执行过程中会被修改或删除的旧版数据，在回滚操作时可以用来恢复之前的状态。常用于处理数据库中的回滚段管理，当事务被回滚时，根据需要进行回滚以前完成的工作，并将撤消所需信息放入 UNDO 表空间中。这些信息保留在 UNDO 表空间中，以便在需要的时候恢复这些文档。因此主要用于存储回滚段数据，支持事务的回滚操作。

2.3 按照功能类型方面划分

按照数据的持久性和功能类型来划分表空间，根据不同的功能需要和数据处理方式， Oracle 将表空间可分为永久表空间、撤销表空间和临时表空间三种类型。

2.3.1 永久表空间（Permanent tablespace）

用于存储用户定义的数据库对象，如数据表、索引、视图等。这些数据是长期存放在硬盘上供日常业务使用的，不会随着会话或者事务的结束而消失，即永久表空间中的数据在提交后会被持久化到磁盘上。常用于存储永久性数据，如表、索引等，在数据库关闭之后仍会存在。

2.3.2 撤销表空间（Undo tablespace）

撤销表空间用于实现事务的回撤和恢复功能。当事务处理过程中需要撤销某个事务时，Oracle 数据库就会从撤销表空间中获取相应的数据，并使用这些数据将撤销操作进行回退。因此常用于存放正在执行事务对数据所做的修改的详细信息。当事务需要回滚时，Oracle 回到这个表空间中找到 undo 数据，使用这些数据还原到之前的状态。它是与会话关联的表空间，每个活动的会话都会用到它。

2.3.3 临时表空间（Temporary tablespace）

临时表空间用来存储对 SQL 查询操作进行排序和处理等临时需求所创建的对象。这些对象在查询结束后会被自动删除。临时表空间主要用于临时数据库对象的存储，如排序时存储的临时文件、Hash Join 操作时的临时表等，以支持 OLAP 和大型复杂查询等高级操作。当语句需要较大量的内存进行排序、转储到磁盘等操作时，会自动使用临时表空间。完成操作后其内数据也会相应地释放掉，即临时表空间中的数据在会话结束后会被自动删除。

可以通过以下SQL语句查询表空间的类型：

 SELECT tablespace_name, contents FROM dba_tablespaces;

其中，contents列的值可以为PERMANENT、UNDO或TEMPORARY，分别对应永久表空间、撤销表空间和临时表空间。

三、表空间操作

3.1 创建表空间

语法格式如下：

CREATE TABLESPACE tablespace_name
    DATAFILE 'path/to/datafile' SIZE file_size [ REUSE ]
    [ EXTENT MANAGEMENT <LOCAL | DICTIONARY> ]
    [ AUTOEXTEND ON NEXT auto_extend_size MAXSIZE max_size ]
    [ DEFAULT STORAGE (storage_clause)]
    [ LOGGING | NOLOGGING]
    [ ONLINE | OFFLINE]
    [ PERMANENT | TEMPORARY]
    [ SEGMENT SPACE MANAGEMENT { MANUAL | AUTO } ];

各个参数含义如下：

tablespace_name:创建的表空间名称，即新表空间的名字。
DATAFILE: 物理文件路径及名称，这个文件将作为表空间的数据容器（可以有多个），用于指定数据文件的路径和文件名。
SIZE: 指定新创建的数据文件的初始大小，默认单位为 M。。
REUSE: 若数据文件已存在，可使用 REUSE 参数来重复利用该数据文件，不做任何命名修改操作。
EXTENT MANAGEMENT: 指定表空间的分配方式。DICTIONARY 表示使用数据字典管理， LOCAL 表示使用局部管理方式，可以继承 DEFAULT 数据库设置。
AUTOEXTEND: 对于需要自动扩展的数据文件，启用自动扩展功能。
NEXT: 指定自动扩展的下一个增量大小，默认单位为 M。
MAXSIZE: 数据文件最大允许的大小限制，默认也是以 M 作为单位。
DEFAULT STORAGE (storage_clause)：指定表空间的默认存储属性。
LOGGING：设置并激活日志记录，将数据更改所导致的事务记录在 Oracle 的重做日志文件中。
NOLOGGING：不启用日志记录，不写事务日志，提高性能。
ONLINE：允许并发连接到表空间中的对象，在创建表空间时，将其设置为在线状态。
OFFLINE ：禁止对表空间的所有活动，在创建表空间时，将其设置为离线状态。
PERMANENT：创建永久表空间。
TEMPORARY：创建临时表空间。
SEGMENT SPACE MANAGEMENT: 指定表空间中的段管理方式，MANUAL 表示手动管理，AUTO 表示自动管理。
示例：创建一个名为 USERS 的用户表空间，存储在目录 /u01/app/oracle/oradata/db1/ 下的 users01.dbf 文件中，初始大小为 100MB，启用自动扩展功能，每次自动以 10MB 为单位增加文件的容量，最大不能超过 2GB，语法如下：


CREATE TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/db1/users01.dbf' SIZE 100M
    EXTENT MANAGEMENT LOCAL
    AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

3.2 修改表空间

可以使用 ALTER TABLESPACE 语句来修改现有的表空间。ALTER TABLESPACE 语句的基本语法格式如下：

ALTER TABLESPACE tablespace_name {
      [ ADD DATAFILE 'path/to/new_datafile' SIZE file_size ]
      [ RESIZE DATAFILE 'path/to/datafile' SIZE file_size ]
      [ AUTOEXTEND ON NEXT auto_extend_size integer[K|M|G|T] MAXSIZE max_size 
                                                             integer[K|M|G|T] ]
      [ RENAME TO new_tablespace_name ]--RENAME DATAFILE 'old_file_name' TO 'new_file_name'
      [ RESIZE integer[K|M|G|T] 
      [ OFFLINE| OFFLINE | READ ONLY | READ WRITE NORMAL/IMMEDIATE ] 
      {BEGIN BACKUP | END BACKUP} |
      {BEGIN/END} {BACKUP | COPY | RECOVER} [DATAFILE 'file_spec'] |
      {BEGIN/END} {BACKUP | COPY | RECOVER} [TEMPFILE 'file_spec'] |
      {BEGIN/END} {BACKUP | COPY | RECOVER} TABLESPACE tablespace_name |
      {BEGIN/END} {BACKUP | COPY | RECOVER} DATABASE |
      {BEGIN/END} {BACKUP | COPY | RECOVER} CONTROLFILE |
      {BEGIN/END} {BACKUP | COPY | RECOVER} ARCHIVELOG |
      {BEGIN/END} {BACKUP | COPY | RECOVER} LOGFILE 'file_spec' |
      {BEGIN/END} {BACKUP | COPY | RECOVER} REDOLOG group integer |
      {BEGIN/END} {BACKUP | COPY | RECOVER} ALL}};

各个参数含义如下：

tablespace_name: 要修改的表空间名字。
ADD DATAFILE: 用来添加一个新的数据文件到该表空间中。
RESIZE DATAFILE: 用来调整已有数据文件的大小。
AUTOEXTEND: 对于需要自动扩展的数据文件，启用自动扩展功能。
NEXT: 指定自动扩展的下一个增量大小。
MAXSIZE: 数据文件最大允许的大小限制。
RENAME TO: 修改表空间的名字。
OFFLINE: 可以将表空间脱机，使其不再接收新事务，并允许执行 DBA 级别操作。
ONLINE：在线表空间。
READ ONLY/READ WRITE：只读/读写表空间。
RENAME DATAFILE：重命名数据文件。
RESIZE：调整表空间大小。
BEGIN/END BACKUP：开始/结束备份。
BEGIN/END BACKUP/COPY/RECOVER：开始/结束备份/复制/恢复操作。
示例：

ALTER TABLESPACE users
    ADD DATAFILE '/u01/app/oracle/oradata/db1/users02.dbf' SIZE 100M;

3.3删除表空间

可以使用 DROP TABLESPACE 语句来删除一个现有的表空间。DROP TABLESPACE 语句的基本语法格式如下：

DROP TABLESPACE tablespace_name 
      [ INCLUDING CONTENTS ] 
      [ AND DATAFILES [CASCADE|KEEP]];

各个参数含义如下：

tablespace_name: 要删除的表空间名字。
INCLUDING CONTENTS(可选): 如果带有此选项，则表空间中的所有对象都会被删除，即删除表空间中的所有对象和数据。
AND DATAFILES CASCADE(可选): 如果带有此选项，则与该表空间关联的数据文件也将被删除，即级联删除该表空间使用的所有数据文件。
AND DATAFILES KEEP(可选)：保留该表空间使用的所有数据文件。
备注：[CASCADE|KEEP]根据情况可选择是否添加。
示例：
删除 USERS 表空间（无论是否存在该表空间中的任何数据库对象）及其关联数据文件 users01.dbf，

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

3.4 查询表空间

3.4.1 查询表空间使用率信息

SELECT tablespace_name, 
       ROUND((total_space - free_space) / total_space * 100, 2) "Used%",
       ROUND(total_space / 1024 / 1024, 2) "Total(MB)",
       ROUND(free_space / 1024 / 1024, 2) "Free(MB)",
       ROUND((total_space - free_space) / 1024 / 1024, 2) "Used(MB)"
  FROM (SELECT tablespace_name,
               SUM(bytes) / 1024 / 1024 total_space,
               SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) / 1024 / 1024 free_space
          FROM dba_data_files 
         GROUP BY tablespace_name);
 
--或者使用下面语句
--使用外连接（等价于隐式内连接），以及 SELECT 子句中的运算符和函数（例如 ROUND、/）
select a.tablespace_name,
a.total/1024 total,
round(100-b.free/a.total*100,2) "%Used"
from
(select tablespace_name,sum(bytes) total
from dba_data_files
group by tablespace_name
) a,
(select tablespace_name,sum(bytes) free
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name=b.tablespace_name;

解析第二个查询语句：

首先，从 dba_data_files 表中查询出每个表空间所占用的总空间大小，结果保存在名为 a 的嵌套子查询中。

然后，从 dba_free_space 表中查询出每个表空间中当前可用空间的总大小，保存在名为 b 的嵌套子查询中。

最后，将两个嵌套子查询连接起来，在主查询中计算出当前每个表空间已经使用了的百分比。具体来说，计算公式为：100 - (当前表空间中可用空间 / 当前表空间总空间) * 100，结果保留两位小数。同时，查询结果包括表空间名称（tablespace_name）、表空间总大小（total）和表空间当前已使用的百分比（%Used）。

3.4.2 查询指定表空间内各数据文件的使用情况

SELECT file_id, 
       file_name,
       tablespace_name, 
       autoextensible, 
       ceil(bytes / 1024 / 1024) "Size(MB)", 
       ceil(maxbytes / 1024 / 1024) "MaxSize(MB)",
       ceil(((maxbytes - bytes) / allocate_extent) + 1) "Free Extents"
  FROM dba_data_files 
 WHERE tablespace_name = '<tablespace_name>';

3.4.3 检查当前用户空间分配情况

SELECT * 
FROM dba_segments 
WHERE owner = '<username>'

部分应替换为实际的用户名。该 SQL 语句将从 dba_segments 视图中查询出属于特定用户的所有段（例如表、索引等），并显示它们的详细信息，包括名称、类型、大小、空间限制等。需要注意的是，如果不具备访问 dba_segments 等系统视图的权限，则无法查询到相关信息。

或者使用如下语句查询当前用户空间使用情况：

SELECT segment_type, sum(bytes)/1024/1024 MB_used
FROM user_segments
GROUP BY segment_type;

3.4.4 检查各个用户的空间分配情况

SELECT username, default_tablespace, temporary_tablespace, profile FROM dba_users;

从 dba_users 视图中获取当前数据库中所有用户的信息，包括用户名 (username)、默认表空间 (default_tablespace)、临时表空间 (temporary_tablespace) 和配置文件 (profile) 等。其中，默认表空间表示该用户创建对象时使用的默认表空间，而临时表空间则表示该用户的临时表空间。

四、表空间常见问题

4.1 表空间满了如何解决？

当 Oracle 数据库中的表空间满了时，一般需要采取以下措施：
1）扩展数据文件：可以通过增加数据文件的大小来扩展表空间。具体步骤是使用 ALTER TABLESPACE 命令加上关键字 ADD DATAFILE 来添加一个或多个数据文件。
例如：

ALTER TABLESPACE tablespace_name ADD DATAFILE '/path/to/datafile.dbf' SIZE 50M;

这个命令会向指定的表空间中添加一个新的数据文件，其大小为 50MB。需要注意的是，在执行此操作前必须保证磁盘空间足够。
2）压缩数据表：当表空间中存在不必要的数据时，可以考虑对数据表进行压缩，从而释放一些空间。一种常见的做法是删除一些不再使用的数据行，比如历史数据或者备份数据等。
3）移动数据对象：可以将一些不常用到的数据对象移动到其他的表空间中。例如，将一些历史数据对象或者稀少使用的辅助表（如日志表）移动到硬盘速度较慢但容量较大的表空间中，从而腾出更多的存储空间。
4）压缩重建索引：重建索引可以消除分裂的块并缩小存储区的碎片，从而释放一些空间。一般情况下，重建完索引后，表空间大小会被自动收缩。
注意：
在执行上述操作时，请务必备份好数据以防止意外丢失。同时，要根据实际情况选择对应的措施，并充分评估可能产生的风险和影响。