Oracle 数据库与表

Oracle 数据库是一个复杂的系统，由多个层次的对象组成。理解数据库和表的结构对于 DBA 进行日常维护、性能调优和数据管理至关重要。

数据库架构

传统数据库架构（Oracle 11g 及之前）

数据库（Database）：包含数据文件、控制文件、redo log 文件等物理文件
实例（Instance）：由内存结构（SGA）和后台进程组成
表空间（Tablespace）：数据库的逻辑存储单元，包含一个或多个数据文件
段（Segment）：表、索引等对象的存储空间
区（Extent）：段的基本存储单位，由多个连续的数据块组成
块（Block）：Oracle 存储的最小单位，对应磁盘上的物理块

多租户架构（Oracle 12c 及以上）

CDB（容器数据库）：包含根容器（CDB$ROOT）、种子容器（PDB$SEED）和一个或多个 PDB
PDB（可插拔数据库）：独立的数据库环境，可以像传统数据库一样使用
CDB$ROOT：存储 CDB 级别的元数据和公共用户
PDB$SEED：用于创建新 PDB 的模板

表空间管理

表空间类型

系统表空间（SYSTEM）：存储数据字典和系统元数据
SYSAUX 表空间：辅助系统表空间，存储 Oracle 组件数据
用户表空间：存储用户数据和对象
临时表空间：存储临时数据，如排序结果
撤销表空间（UNDO）：存储撤销数据，用于事务回滚和一致性读

表空间创建与管理

创建表空间：

sql

-- 创建永久表空间
CREATE TABLESPACE users 
DATAFILE '+DATA' SIZE 100M 
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE temp 
TEMPFILE '+DATA' SIZE 50M 
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

-- 创建撤销表空间
CREATE UNDO TABLESPACE undo 
DATAFILE '+DATA' SIZE 100M 
AUTOEXTEND ON NEXT 20M MAXSIZE 500M;

表空间监控：

sql

-- 查看表空间使用情况
SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_gb, 
       ROUND(used_space, 2) AS used_gb, 
       ROUND(free_space, 2) AS free_gb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024/1024 AS total_space,
         SUM(bytes - NVL(free_bytes, 0))/1024/1024/1024 AS used_space,
         SUM(NVL(free_bytes, 0))/1024/1024/1024 AS free_space
  FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
  )
  GROUP BY tablespace_name
)
ORDER BY used_percent DESC;

表空间维护：

sql

-- 扩展表空间数据文件
ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.259.1012345678' RESIZE 200M;

-- 添加数据文件
ALTER TABLESPACE users ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

-- 切换撤销表空间
ALTER SYSTEM SET undo_tablespace=undo2 SCOPE=BOTH;

-- 删除表空间
DROP TABLESPACE old_users INCLUDING CONTENTS AND DATAFILES;

表设计

数据类型选择

数据类型	用途	版本差异	最佳实践
VARCHAR2	可变长字符数据	Oracle 12c+ 支持最大 32767 字节	优先使用 VARCHAR2 而非 CHAR
NUMBER	数值数据	所有版本支持	根据实际数值范围选择精度和小数位
DATE	日期和时间	所有版本支持	Oracle 11g 引入 TIMESTAMP 类型，更精确
TIMESTAMP	带时区的日期时间	Oracle 11g 引入	存储精确时间时使用
CLOB	大字符数据	所有版本支持	Oracle 12c+ 支持 SecureFiles LOB
BLOB	二进制数据	所有版本支持	Oracle 12c+ 支持 SecureFiles LOB
JSON	JSON 数据类型	Oracle 19c 引入	存储和查询 JSON 数据时使用

表创建与管理

创建表：

sql

CREATE TABLE employees (
  employee_id   NUMBER(6) PRIMARY KEY,
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25) NOT NULL,
  email         VARCHAR2(100) NOT NULL UNIQUE,
  phone_number  VARCHAR2(20),
  hire_date     DATE NOT NULL,
  job_id        VARCHAR2(10) NOT NULL,
  salary        NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id    NUMBER(6),
  department_id NUMBER(4)
) TABLESPACE users;

分区表创建：

sql

-- 按范围分区
CREATE TABLE sales (
  sale_id      NUMBER PRIMARY KEY,
  sale_date    DATE NOT NULL,
  product_id   NUMBER NOT NULL,
  quantity     NUMBER NOT NULL,
  amount       NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION sales_future VALUES LESS THAN (MAXVALUE)
) TABLESPACE users;

临时表创建：

sql

-- 会话级临时表
CREATE GLOBAL TEMPORARY TABLE temp_sales (
  sale_id      NUMBER,
  sale_date    DATE,
  amount       NUMBER(10,2)
) ON COMMIT PRESERVE ROWS;

-- 事务级临时表
CREATE GLOBAL TEMPORARY TABLE temp_order (
  order_id     NUMBER,
  product_id   NUMBER,
  quantity     NUMBER
) ON COMMIT DELETE ROWS;

表维护

表结构修改：

sql

-- 添加列
ALTER TABLE employees ADD (middle_name VARCHAR2(15));

-- 修改列
ALTER TABLE employees MODIFY (email VARCHAR2(150));

-- 删除列
ALTER TABLE employees DROP COLUMN middle_name;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;

-- 启用约束
ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;

表空间迁移：

sql

-- 迁移表到新表空间
ALTER TABLE employees MOVE TABLESPACE new_users;

-- 迁移索引到新表空间
ALTER INDEX emp_pk REBUILD TABLESPACE new_indexes;

表统计信息收集：

sql

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'EMPLOYEES', CASCADE => TRUE);

-- 收集架构统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'HR');

多租户环境管理

PDB 创建与管理

创建 PDB：

sql

-- 从 PDB$SEED 创建
CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY Password123
FILE_NAME_CONVERT = ('+DATA/orcl/pdbseed/', '+DATA/orcl/pdb1/');

-- 启动 PDB
ALTER PLUGGABLE DATABASE pdb1 OPEN;

-- 关闭 PDB
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

PDB 监控：

sql

-- 查看 PDB 状态
SELECT pdb_name, status FROM dba_pdbs;

-- 查看 PDB 数据文件
SELECT name FROM v$datafile WHERE con_id = 3;

-- 切换到 PDB
ALTER SESSION SET CONTAINER = pdb1;

CDB 与 PDB 资源管理

设置 PDB 资源限制：

sql

-- 创建资源计划
BEGIN
  DBMS_RESOURCE_MANAGER_CREATE_PLAN(PLAN => 'PDB_PLAN', COMMENT => 'PDB Resource Plan');
END;
/

-- 创建资源分配指令
BEGIN
  DBMS_RESOURCE_MANAGER_CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PDB_GROUP', COMMENT => 'PDB Consumer Group');
END;
/

-- 分配资源
BEGIN
  DBMS_RESOURCE_MANAGER_CREATE_PLAN_DIRECTIVE(
    PLAN => 'PDB_PLAN',
    GROUP_OR_SUBPLAN => 'PDB_GROUP',
    COMMENT => 'PDB Directive',
    CPU_P1 => 50,
    CPU_P2 => 30
  );
END;
/

表空间与表监控

关键监控指标

表空间使用率 > 90% 时需要告警
表空间自动扩展次数过多
大表（> 100GB）的增长趋势
临时表空间使用率
撤销表空间大小和使用率

监控脚本示例

sql

-- 表空间使用率监控
SELECT tablespace_name,
       ROUND((used_space/total_space)*100, 2) AS used_percent,
       ROUND(total_space, 2) AS total_gb,
       ROUND(used_space, 2) AS used_gb
FROM (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024/1024 AS total_space,
         SUM(bytes - NVL(free_bytes, 0))/1024/1024/1024 AS used_space
  FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
  )
  GROUP BY tablespace_name
)
WHERE ROUND((used_space/total_space)*100, 2) > 80
ORDER BY used_percent DESC;

-- 大表监控
SELECT owner, table_name, ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE segment_type = 'TABLE'
AND bytes/1024/1024/1024 > 50
ORDER BY bytes DESC;

-- 临时表空间使用监控
SELECT tablespace_name, session_id, sql_id, ROUND(blocks*8/1024/1024, 2) AS mb_used
FROM v$tempseg_usage
ORDER BY mb_used DESC;

最佳实践

表空间设计最佳实践

分离系统和用户数据：将 SYSTEM、SYSAUX 与用户表空间分离
分离数据和索引：将表和索引存储在不同的表空间和磁盘上
使用本地管理表空间：Oracle 10g 及以上推荐使用本地管理表空间
使用自动段空间管理：提高空间利用率和性能
合理设置表空间大小：避免频繁自动扩展，建议初始大小足够大
为大表创建独立表空间：便于管理和性能调优

表设计最佳实践

选择合适的数据类型：根据实际数据选择最适合的数据类型
避免使用 CHAR 类型：优先使用 VARCHAR2，节省存储空间
使用 NUMBER 类型时指定精度：如 NUMBER(10,2) 而非 NUMBER
为大表创建分区：提高查询性能和管理效率
合理使用索引：避免过度索引，根据查询需求创建
使用约束确保数据完整性：主键、外键、唯一约束等
定期收集统计信息：确保优化器生成最佳执行计划

多租户环境最佳实践

合理规划 PDB 数量：根据服务器资源和业务需求规划
为每个 PDB 分配独立表空间：便于管理和资源隔离
使用资源计划管理 PDB 资源：避免个别 PDB 消耗过多资源
定期备份 PDB：可以单独备份和恢复 PDB
使用 PDB 快照：快速创建测试环境

版本差异

版本	特性
Oracle 11g	引入 SecureFiles LOB，增强分区表功能
Oracle 12c	引入多租户架构（CDB/PDB），支持 VARCHAR2 最大 32767 字节
Oracle 18c	增强多租户功能，引入 PDB 快照
Oracle 19c	引入 JSON 数据类型，增强分区表功能
Oracle 23c	增强 JSON 支持，引入向量数据库功能

常见问题（FAQ）

Q1: 如何查看表空间的使用情况？

A1: 使用以下查询查看表空间使用情况：

sql

SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_gb, 
       ROUND(used_space, 2) AS used_gb, 
       ROUND(free_space, 2) AS free_gb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024/1024 AS total_space,
         SUM(bytes - NVL(free_bytes, 0))/1024/1024/1024 AS used_space,
         SUM(NVL(free_bytes, 0))/1024/1024/1024 AS free_space
  FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
  )
  GROUP BY tablespace_name
);

Q2: 如何扩展表空间？

A2: 可以通过以下两种方式扩展表空间：

调整现有数据文件大小：

sql

ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.259.1012345678' RESIZE 200M;

添加新的数据文件：

sql

ALTER TABLESPACE users ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

Q3: 如何创建分区表？

A3: 示例：

sql

CREATE TABLE sales (
  sale_id      NUMBER PRIMARY KEY,
  sale_date    DATE NOT NULL,
  product_id   NUMBER NOT NULL,
  quantity     NUMBER NOT NULL,
  amount       NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

Q4: 如何在多租户环境中创建 PDB？

A4: 示例：

sql

CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY Password123
FILE_NAME_CONVERT = ('+DATA/orcl/pdbseed/', '+DATA/orcl/pdb1/');

Q5: 如何迁移表到新表空间？

A5: 示例：

sql

-- 迁移表
ALTER TABLE employees MOVE TABLESPACE new_users;

-- 迁移索引
ALTER INDEX emp_pk REBUILD TABLESPACE new_indexes;

Q6: 如何监控临时表空间使用？

A6: 示例：

sql

SELECT tablespace_name, session_id, sql_id, ROUND(blocks*8/1024/1024, 2) AS mb_used
FROM v$tempseg_usage
ORDER BY mb_used DESC;

命令	用途
`CREATE TABLESPACE`	创建表空间
`ALTER TABLESPACE`	修改表空间
`DROP TABLESPACE`	删除表空间
`CREATE TABLE`	创建表
`ALTER TABLE`	修改表
`DROP TABLE`	删除表
`CREATE PLUGGABLE DATABASE`	创建 PDB
`ALTER PLUGGABLE DATABASE`	修改 PDB
`DROP PLUGGABLE DATABASE`	删除 PDB
`DBMS_STATS.GATHER_TABLE_STATS`	收集表统计信息
`SELECT * FROM dba_tablespaces`	查看表空间信息
`SELECT * FROM dba_data_files`	查看数据文件信息
`SELECT * FROM dba_tables`	查看表信息

总结

Oracle 数据库与表是数据库架构的核心组成部分，DBA 应该深入理解其结构和管理方法。合理的表空间设计、表设计和多租户环境管理对于数据库的稳定高效运行至关重要。DBA 应该根据实际业务需求和服务器资源，选择合适的设计方案，并定期进行监控和维护，以确保数据库的性能和可靠性。

Oracle 数据库与表 ​

数据库架构 ​

传统数据库架构（Oracle 11g 及之前） ​

多租户架构（Oracle 12c 及以上） ​

表空间管理 ​

表空间类型 ​

表空间创建与管理 ​

表设计 ​

数据类型选择 ​

表创建与管理 ​

表维护 ​

多租户环境管理 ​

PDB 创建与管理 ​

CDB 与 PDB 资源管理 ​

表空间与表监控 ​

关键监控指标 ​

监控脚本示例 ​

最佳实践 ​

表空间设计最佳实践 ​

表设计最佳实践 ​

多租户环境最佳实践 ​

版本差异 ​

常见问题（FAQ） ​

Q1: 如何查看表空间的使用情况？ ​

Q2: 如何扩展表空间？ ​

Q3: 如何创建分区表？ ​

Q4: 如何在多租户环境中创建 PDB？ ​

Q5: 如何迁移表到新表空间？ ​

Q6: 如何监控临时表空间使用？ ​

相关命令速查 ​

总结 ​