Oracle数据库节省空间功能全面解析

空间管理功能

Oracle数据库服务器自动管理空间。它生成关于潜在问题的警报，并推荐可能的解决方案。

空间管理功能包括：

1. Oracle管理文件（OMF）：自动管理数据库文件的位置和名称，减少人为干预。
2. 使用位图的空闲空间管理（本地管理）和自动数据文件扩展：提高空闲空间利用率和文件扩展的效率。
3. 主动空间管理（默认阈值和服务器生成的警报）：自动监控和管理空间使用情况，及时报警。
4. 空间回收（收缩段、在线表重定义）：通过收缩段和在线重定义表来回收未使用的空间。
5. 容量规划（增长报告）：提供数据库容量的增长报告，帮助规划未来的存储需求。

示例：

• OMF：通过以下设置启用OMF：

  ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata';
  

• 空间回收：通过收缩段来回收未使用的空间：

  ALTER TABLE employees SHRINK SPACE;
  

块空间管理

块空间管理通过指定的百分比（如PCTFREE）来控制块中保留的可用空间。例如，PCTFREE = 10 表示块中将保留10%的空闲空间，用于未来的更新操作。

示例：

• 插入和更新操作填充块空间，当删除记录时，会释放块中的空间：

  CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(50)
  ) PCTFREE 10;
  

段内空闲空间管理

段内空闲空间使用位图进行跟踪，具有更灵活的空间利用、运行时调整和多进程搜索位图块（BMB）的优点。

示例：

• 使用位图管理段内的空闲空间，提高空间利用效率：

  CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(50)
  ) TABLESPACE example_ts STORAGE (FREELISTS 1 FREELIST GROUPS 1);
  

分配区

分配区是通过搜索数据文件的位图以获取所需数量的相邻空闲块来实现的。分配区的大小可以使用UNIFORM或AUTOALLOCATE存储子句来指定。

示例：

• 创建表时指定分配区大小：

  CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(50)
  ) STORAGE (INITIAL 1M NEXT 1M);
  

使用不可用索引

考虑使用不可用索引以提高批量加载的性能。不可用索引不会被优化器使用。当索引被设为不可用时，段将被删除，不可用的索引可以重建以使其重新有效。

示例：

• 创建不可用索引：

  CREATE INDEX test_i1 ON seg_test(c) UNUSABLE;
  

• 将现有索引设为不可用：

  ALTER INDEX test_i UNUSABLE;
  

• 重建不可用索引：

  ALTER INDEX test_i REBUILD;
  ``*
  
  

使用临时表

临时表包含在事务或会话期间的数据。临时表类型包括全局临时表和私有临时表。临时表的段在第一次INSERT或CREATE TABLE AS SELECT语句时分配。

示例：

• 创建全局临时表：

  CREATE GLOBAL TEMPORARY TABLE trans_buff_area (date1 DATE) ON COMMIT DELETE ROWS;
  

• 创建私有临时表：

  CREATE PRIVATE TEMPORARY TABLE ORA$PTT_mine (c1 DATE) ON COMMIT PRESERVE DEFINITION;
  

创建全局临时表

创建全局临时表时，需要使用CREATE GLOBAL TEMPORARY TABLE语句，并指定该表是事务级别还是会话级别。事务级别的全局临时表在事务提交时删除数据，而会话级别的全局临时表在会话结束时保留数据。

示例：

• 创建事务级别的全局临时表：

  CREATE GLOBAL TEMPORARY TABLE trans_buff_area (date1 DATE) ON COMMIT DELETE ROWS;
  

• 创建会话级别的全局临时表：

  CREATE GLOBAL TEMPORARY TABLE session_buff_area (date1 DATE) ON COMMIT PRESERVE ROWS;
  

创建私有临时表

创建私有临时表时，需要使用CREATE PRIVATE TEMPORARY TABLE语句，表名必须以ORA$PTT_开头。私有临时表的定义和内容在会话或事务结束时自动删除。

示例：

• 创建私有临时表：

  CREATE PRIVATE TEMPORARY TABLE ORA$PTT_mine (c1 DATE, c2 VARCHAR2(20), c3 NUMBER(10,2));
  

• 设置私有临时表前缀：

  ALTER SESSION SET PRIVATE_TEMP_TABLE_PREFIX = 'ORA$PTT_';
  

• 删除私有临时表：

  DROP TABLE ORA$PTT_mine;
  

表压缩概述

通过压缩所有数据来减少存储成本。直接路径插入操作的基本压缩比率为10倍，所有DML操作的高级行压缩比率为2-4倍。基本表压缩适用于数据仓库（DSS），高级行压缩适用于在线事务处理（OLTP）和数据仓库（DSS）。

示例：

• 创建一个压缩表：

  CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(50)
  ) COMPRESS FOR ALL OPERATIONS;
  

• 使用高级行压缩：

  CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(50)
  ) ROW STORE COMPRESS ADVANCED;
  

直接路径插入操作的压缩

1. 使用 CREATE TABLE ... COMPRESS BASIC 启用
2. 推荐用于批量加载数据仓库
3. 最大化块中连续空闲空间

示例：

• 创建一个启用了基本压缩的表，用于批量加载数据：

  CREATE TABLE bulk_load_table (
    id NUMBER,
    name VARCHAR2(50)
  ) COMPRESS BASIC;
  

指定表压缩

可以为以下内容指定表压缩：

1. 整个堆组织表
2. 分区表（每个分区可以有不同类型或级别的压缩）
3. 嵌套表的存储

不能为以下情况指定表压缩：

1. 超过255列的表不能同时指定基本和高级行压缩
2. 如果表已为直接加载进行压缩，不能删除列；但如果表为高级行压缩，则可以删除列

示例：

• 为堆组织表指定压缩：

  CREATE TABLE heap_table (
    id NUMBER,
    name VARCHAR2(50)
  ) COMPRESS FOR ALL OPERATIONS;
  

• 为分区表的不同分区指定不同的压缩级别：

  CREATE TABLE partitioned_table (
    id NUMBER,
    name VARCHAR2(50),
    partition_key DATE
  )
  PARTITION BY RANGE (partition_key) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')) COMPRESS BASIC,
    PARTITION p2 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) COMPRESS FOR OLTP
  );
  

用压缩顾问

1. 分析对象，提供不同压缩方法的空间节省估计
2. 帮助决定应用程序的正确压缩级别
3. 推荐各种压缩策略：
   • 为特定数据集选择正确的压缩算法
   • 在特定列上排序以增加压缩比率
   • 提出不同压缩算法之间的权衡

示例：

• 使用压缩顾问分析表以决定最佳压缩策略：

  DECLARE
    advice BLOB;
  BEGIN
    DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
      'SCHEMA_NAME',
      'TABLE_NAME',
      COMPRESS_FOR QUERY_HIGH,
      advice
    );
  END;
  

通过收缩段回收空间

1. 收缩是一种在线和就地操作
2. 仅适用于驻留在自动段空间管理（ASSM）表空间中的段
3. 适用的段类型：
   • 堆组织表和索引组织表
   • 索引
   • 分区和子分区
   • 物化视图和物化视图日志

示例：

• 收缩堆组织表以回收未使用的空间：

  ALTER TABLE example_table SHRINK SPACE;
  

管理可恢复的空间分配

1. 可恢复的语句允许暂停大型操作而不是接收到错误
2. 在操作暂停期间提供解决问题的机会，而不是重新开始
3. 以下条件下会暂停：
   • 空间不足
   • 达到最大分区
   • 超过空间配额
4. 可以多次暂停和恢复

示例：

• 启用可恢复会话并设置超时：

  ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
  

• 在空间不足时暂停操作：

  INSERT INTO large_table SELECT * FROM another_table;
  

使用可恢复的空间分配

1. 查询、DML操作和某些DDL操作在遇到空间不足错误时可以恢复
2. 可恢复语句可以通过SQL、PL/SQL、SQL*Loader和Data Pump实用程序或Oracle调用接口（OCI）发出
3. 仅当会话通过以下操作之一启用时，语句才在可恢复模式下执行：
   • 设置 RESUMABLE_TIMEOUT 初始化参数为非零值
   • 发出 ALTER SESSION ENABLE RESUMABLE 语句

示例：

• 启用会话的可恢复模式：

  ALTER SESSION ENABLE RESUMABLE;
  

• 设置可恢复超时：

  ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600;
  

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

让AI工具成为你的得力助手，感受AI工具的无限可能，让复杂的任务变得简单，让你的工作更加轻松和高效。