Oracle数据库基础知识——表空间

小小怪amp

已于 2023-12-22 19:29:24 修改

阅读量1.5k

点赞数 30

文章标签：数据库 oracle

于 2023-12-22 19:12:13 首次发布

本文链接：https://blog.csdn.net/2301_79104080/article/details/135089158

版权

一、表空间知识点

1.表空间类型

数据表空间（Data Tablespaces）：存储用户数据和对象，如表和索引等。
临时表空间（Temporary Tablespaces）：用于存储临时数据，如排序和临时表等。
索引表空间（Index Tablespaces）：专门用于存储索引。
系统表空间（System Tablespaces）：存储系统数据字典和元数据信息。

2.表空间组成部分

数据文件（Data Files）：物理文件，存储实际数据对象。一个表空间可以由一个或多个数据文件组成。
段（Segments）：表空间中的逻辑存储区域，用于存储表、索引或其他对象。
区（Extents）：段由区组成，每个区由连续的数据块组成。
数据块（Data Blocks）：表空间中的最小存储单位，存储实际数据。

3.表空间及其管理

3.1创建表空间

使用 'CREATE TABLESPACE'命令可以创建一个新的表空间，并指定数据文件的大小、路径和属性。

创建表空间语法：

CREATE TABLESPACE tablespace_name
  DATAFILE 'path_to_data_file' SIZE size_clause
  [AUTOEXTEND {ON | OFF} [NEXT size_clause] [MAXSIZE max_size_clause]];

'tablespace_name'：要创建的表空间的名称。
'datafile_path_and_name.dbf'：指定数据文件的路径和名称。
'size_in_MB'：指定表空间的初始大小（单位为MB）。
'AUTOEXTEND ON'：允许表空间自动扩展。
'AUTOEXTEND OFF'：禁止表空间自动扩展。
'NEXT size_in_MB'：设置自动扩展时增长的大小。
'MAXSIZE max_size_in_MB'：设置表空间的的最大大小限制。

示例：

以下是一个示例，创建一个名为'users_space'的用户表空间，包含一个初始大小为100MB的数据文件，并允许数据文件自动扩展，最大不超过500MB：

CREATE TABLESPACE users_space
DATAFILE '/u01/oracle/data/users_space.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 500M;

3.2表空间相关信息

Oracle数据库中关于表空间的相关信息可以通过系统视图来获取。以下是一些常用的系统视图以及它们提供的表空间信息：

3.2.1 DBA_TABLESPACES 视图

这个视图包含了数据库中所有的表空间信息。

查询所有表空间名称：

SELECT tablespace_name FROM dba_tablespaces;

以下是该视图中各个字段所代表的含义：

TABLESPACE_NAME： 表空间的名称。
BLOCK_SIZE： 表空间的数据块大小（以字节为单位）。
INITIAL_EXTENT： 表空间的初始大小。
NEXT_EXTENT： 下一个分配的大小。
MIN_EXTENTS： 表空间允许的最小扩展数目。
MAX_EXTENTS： 表空间允许的最大扩展数目。
PCT_INCREASE： 表空间的百分比增长。
MIN_EXTLEN： 表空间中的最小区长度。
STATUS： 表空间的状态（

ONLINE：表空间在线，意味着表空间可用，可以被数据库访问和使用。表空间中的对象可以被查询、插入、更新或删除。

OFFLINE：表空间处于离线状态，这表示表空间不可用，不能被数据库访问和使用。表空间的对象无法被查询和修改。将表空间设置为离线状态可以用于维护、备份或修复表空间。

）。

CONTENTS： 表空间的内容类型（PERMANENT、TEMPORARY 等）。
LOGGING： 表空间是否记录日志（LOGGING、NOLOGGING）。
SEGMENT_SPACE_MANAGEMENT： 表空间中段空间管理方式，可能是 MANUAL 或 AUTO。
BIGFILE： 表空间是否为大型文件表空间，若是，值为 YES；否则为 NO。
BYTES： 表空间中所有数据文件的总字节数。
MAX_BYTES： 表空间中所有数据文件的最大总字节数。
BLOCKS： 表空间中所有数据文件的总数据块数。
MAX_BLOCKS： 表空间中所有数据文件的最大数据块数。
DROPPED： 表空间是否已被删除，若是，值为 YES；否则为 NO。

3.2.2 DBA_DATA_FILES 视图

这个视图包含了表空间中的数据文件信息。

查看表空间的数据文件、大小和路径：

SELECT tablespace_name, file_name, bytes / 1024 / 1024 AS file_size_MB
FROM dba_data_files;

以下是该视图中各个字段表示的含义：

FILE_NAME： 数据文件的完整路径和文件名。
FILE_ID： 数据文件的唯一标识符。
TABLESPACE_NAME： 数据文件所属的表空间名称。
BYTES： 数据文件的大小（以字节为单位）。
BLOCKS： 数据文件的大小（以数据块为单位）。
STATUS： 数据文件的状态（

AVAILABLE： 数据文件是可用的，可以被数据库访问和使用。

SYSTEM： 表示数据文件是系统文件，通常包含数据库的核心信息和元数据。

ONLINE： 表示数据文件是在线的，可以被读取和写入）。

RELATIVE_FNO： 数据文件的相对文件号。
AUTOEXTENSIBLE： 数据文件是否支持自动扩展（YES 或 NO）。
MAXBYTES： 数据文件的最大大小（以字节为单位）。
MAXBLOCKS： 数据文件的最大大小（以数据块为单位）。
INCREMENT_BY： 数据文件的自动增长的块数。
USER_BYTES： 数据文件中用户可用空间的大小（以字节为单位）。
USER_BLOCKS： 数据文件中用户可用空间的大小（以数据块为单位）。

3.2.3 DBA_TEMP_FILES 视图

这个视图包含了临时表空间的临时文件信息

查询临时表空间的文件、大小和路径

SELECT tablespace_name, file_name, bytes / 1024 / 1024 AS file_size_MB
FROM dba_temp_files;

以下是该视图中常用字段表示的含义：

TABLESPACE_NAME： 空闲空间所属的表空间名称。
FILE_ID： 包含空闲空间的数据文件的 ID。
BLOCK_ID： 空闲空间的起始块号。
BYTES： 空闲空间的大小（以字节为单位）。
BLOCKS： 空闲空间的大小（以数据块为单位）。

3.2.3 DBA_FREE_SPACE 视图

这个视图包含了表空间中的可用空间信息。

查询表空间的可用空间：

SELECT tablespace_name, sum(bytes) / 1024 / 1024 AS free_space_MB
FROM dba_free_space
GROUP BY tablespace_name;

TABLESPACE_NAME： 表空间的名称，指示空闲空间所在的表空间。
FILE_ID： 数据文件的 ID，标识包含空闲空间的数据文件。
BLOCK_ID： 空闲空间的起始块号，表示空闲空间在数据文件中的起始位置。
BYTES： 空闲空间的大小，以字节为单位。
BLOCKS： 空闲空间的大小，以数据库块（block）为单位。

3.3剩余表空间不足处理办法

在日常使用过程中会出现剩余表空间不足的情况，当用户遇到这种情况时，可以采取以下措施来处理。

3.3.1 查看剩余表空间

查看剩余表空间，只需要关注tablespace_name为users的数据即可

以下是查看剩余表空间的SQL语句

select
  a.tablespace_name, total, free, 
  total-free as used, 
  substr(free/total * 100, 1, 5) as "FREE%", --剩余表空间
  substr((total - free)/total * 100, 1, 5) as "USED%" --已使用表空间
from
  (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

3.3.2找到表空间占用较多的对象

使用以下查询来查找占用表空间较多的对象，例如表、索引等：

SELECT 
    owner,        --对象拥有者
    segment_name, --对象名称
    segment_type, --对象类别（TABLE：表, INDEX：索引, LOBINDEX：大对象（LOB）的索引段,ROLLBACK：回滚段, LOBSEGMENT：大型对象（LOB）的数据段 ）
    ROUND(bytes / (1024 * 1024), 2) AS size_mb
FROM 
    dba_segments
ORDER BY 
    bytes DESC;

这将列出数据库中占用空间最多的对象，你可以考虑清理或优化这些对象，例如删除不必要的数据，压缩表或者索引等。

3.3.3压缩表或索引

对于表和索引，你可以考虑执行压缩操作来释放未使用的表空间。

压缩表

使用在线重组：Oracle提供了在线重组的功能，可以使用'ALTER TABLE ... MOVE' 语句来重建表，并通过重组来释放未使用的表空间。ALTER TABLE your_table_name MOVE;

在Oracle数据库中，可以使用不同的方法来压缩表和索引。这里列出了一些常用的方法：

1. 压缩表

使用在线重组（Online Reorganization）：Oracle提供了在线重组的功能，可以使用 ALTER TABLE ... MOVE 语句来重组表，并通过重组来释放未使用的空间。

ALTER TABLE your_table_name MOVE;

这会重新组织表，导致表中的数据重新排列并释放未使用的空间。但这个操作可能需要一段时间，并且会占用较多的系统资源。

使用压缩操作：在某些情况下，你可以使用压缩操作来减小表的物理大小。例如，针对具有重复数据的表，可以使用COMPRESS选项。

ALTER TABLE your_table_name COMPRESS FOR ALL OPERATIONS;

2. 压缩索引

压缩索引段：可以通过 ALTER INDEX ... REBUILD 语句来重建索引，从而压缩索引段并释放未使用的空间。

ALTER INDEX your_index_name REBUILD;

使用压缩功能：Oracle也提供了压缩索引的功能，可以使用COMPRESS选项来压缩索引。

ALTER INDEX your_index_name REBUILD COMPRESS;

这个操作可能会减少索引的物理大小，并释放未使用的空间。

注：在执行这些操作之前，请务必对数据库进行备份，并在生产环境中小心谨慎地操作。压缩操作可能会影响数据库性能，并且可能需要一定的时间才能完成，因此最好在低峰期执行这些操作。

3.3.4增加表空间大小

可以通过增加数据文件或者扩展现有文件的大小来增加表空间的可用空间。

1.查看表空间文件信息

首先，你需要确定要增加大小的表空间的文件信息。以便知道应该增加哪个文件的大小。可以使用以下查询来查看表空间的文件信息：

SELECT file_name, tablespace_name, bytes / (1024 * 1024) AS current_size_mb
FROM dba_data_files
WHERE tablespace_name = 'your_tablespace_name';

2.扩展现有数据文件的大小

使用'ALTER DATABASE'命令来增加已有数据文件的大小。

示例：

ALTER DATABASE DATAFILE 'file_path_and_name.dbf' RESIZE 200M; -- 将文件大小调整为200兆字节

将'file_path_and_name.dbf'替换为需要扩展大小的数据文件的路径和名称。'200M '是你想要将文件调整的大小。

3.添加新的数据文件到表空间

ALTER TABLESPACE users ADD DATAFILE
'file_path_and_name.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 10000M MAXSIZE 30000M;

这会在指定表空间添加一个新的数据文件，初始大小为10000兆字节，并设置了自动扩张，每次扩张大小为10000兆字节，最大允许扩张到30000兆字节

3.3.5调整表空间的管理策略

如果可能的话，考虑更改表空的间的管理策略，以便更好地管理空间。

3.3.6数据迁移

将一些数据迁移到其他表空间或者归档数据可以释放当前表空间的空间。

3.3.7增加硬盘空间

如果硬盘空间充裕，考虑增加数据库所在的硬盘空间，这可以暂时解决表空间不足的问题。

3.3.8扩展表空间限制

如果数据库的增长趋势已经很明显，可以考虑增加表空间的大小显示，确保未来不会频繁遇到空间不足的问题。

小小怪amp

关注

30
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库基础知识——表空间

Oracle数据库基础知识——表空间
复制链接

扫一扫