印刻数据库

外观

Oracle 序列和同义词管理 ​

序列管理 ​

序列定义

  • 序列是Oracle数据库中用于生成唯一数字值的数据库对象
  • 主要用于生成主键值、唯一标识符等
  • 序列是独立于表的对象，可以被多个表共享
  • 序列值是按顺序生成的，默认递增

序列特点

  • 自动生成唯一数字
  • 支持递增和递减
  • 支持循环生成
  • 可以设置起始值、步长、最大值、最小值
  • 序列值存储在内存中，提高访问性能

序列创建 ​

基本语法

sql
CREATE SEQUENCE sequence_name
[START WITH n]
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE]
[ORDER | NOORDER];

参数说明

  • START WITH：序列的起始值
  • INCREMENT BY：序列的步长，正数为递增，负数为递减
  • MAXVALUE：序列的最大值
  • MINVALUE：序列的最小值
  • CYCLE：达到最大值后是否循环
  • CACHE：预缓存的序列值数量
  • ORDER：确保序列值按请求顺序生成

创建示例

sql
-- 创建基本序列
CREATE SEQUENCE emp_seq
START WITH 1000
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- 创建递减序列
CREATE SEQUENCE dept_seq
START WITH 100
INCREMENT BY -1
MINVALUE 1
NOCYCLE
CACHE 10;

-- 创建带循环的序列
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 9999
MINVALUE 1
CYCLE
CACHE 50;

-- 创建带ORDER选项的序列（适合RAC环境）
CREATE SEQUENCE rac_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20
ORDER;

序列使用 ​

获取序列值

sql
-- 获取下一个序列值
SELECT emp_seq.NEXTVAL FROM dual;

-- 获取当前序列值
SELECT emp_seq.CURRVAL FROM dual;

在INSERT语句中使用

sql
-- 在插入语句中使用序列
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

-- 在多个表中共享序列
INSERT INTO orders (order_id, customer_id)
VALUES (order_seq.NEXTVAL, 101);

INSERT INTO order_items (item_id, order_id, product_id)
VALUES (order_seq.NEXTVAL, 1001, 201);

在PL/SQL中使用

sql
DECLARE
  v_emp_id NUMBER;
BEGIN
  -- 获取序列值
  v_emp_id := emp_seq.NEXTVAL;
  
  -- 使用序列值
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (v_emp_id, 'Jane', 'Smith');
  
  DBMS_OUTPUT.PUT_LINE('Generated employee ID: ' || v_emp_id);
END;
/

序列修改 ​

修改序列

sql
-- 修改序列的步长和缓存大小
ALTER SEQUENCE emp_seq
INCREMENT BY 2
CACHE 50;

-- 修改序列的最大值
ALTER SEQUENCE order_seq
MAXVALUE 999999;

-- 启用循环
ALTER SEQUENCE dept_seq
CYCLE;

-- 修改序列为NOORDER
ALTER SEQUENCE rac_seq
NOORDER;

注意事项

  • 不能修改序列的起始值
  • 修改序列的最小值不能大于当前序列值
  • 修改序列的最大值不能小于当前序列值
  • 修改序列会影响后续生成的序列值
  • 在RAC环境中，修改序列可能需要额外考虑

序列删除 ​

删除序列

sql
-- 删除序列
DROP SEQUENCE emp_seq;

-- 删除多个序列
DROP SEQUENCE dept_seq;
DROP SEQUENCE order_seq;

注意事项

  • 删除序列前确认不再需要该序列
  • 删除序列会影响依赖该序列的代码
  • 删除序列后无法恢复序列的当前值
  • 定期清理无用的序列，减少数据库对象数量

序列监控和管理 ​

查看序列信息

sql
-- 查看用户拥有的序列
SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size
FROM user_sequences;

-- 查看所有序列（需要DBA权限）
SELECT owner, sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size
FROM dba_sequences
WHERE owner NOT IN ('SYS', 'SYSTEM');

-- 查看序列的详细信息
SELECT * FROM user_sequences WHERE sequence_name = 'EMP_SEQ';

序列性能监控

sql
-- 查看序列的使用情况
SELECT object_name, statistic_name, value
FROM v$segment_statistics
WHERE object_type = 'SEQUENCE'
  AND object_name = 'EMP_SEQ';

-- 查看序列的缓存命中率
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%sequence%';

序列管理最佳实践

  • 合理设置缓存大小：根据序列的使用频率设置合适的缓存大小
  • 使用ORDER选项：在RAC环境中使用ORDER选项确保序列值顺序
  • 避免频繁修改：频繁修改序列会影响性能
  • 定期监控：定期监控序列的使用情况和性能
  • 合理命名：使用有意义的序列名称，便于管理

同义词管理 ​

同义词定义

  • 同义词是数据库对象的别名
  • 用于简化对象访问，隐藏对象的实际位置
  • 可以为表、视图、序列、过程、函数等对象创建同义词
  • 分为私有同义词和公有同义词

同义词类型

  • 私有同义词：仅创建者和授权用户可以使用
  • 公有同义词：所有用户都可以使用

同义词用途

  • 简化SQL语句，减少输入
  • 隐藏对象的实际所有者
  • 隐藏对象的实际位置（如跨数据库链接）
  • 提供对象的向后兼容性
  • 简化权限管理

同义词创建 ​

创建私有同义词

sql
-- 为表创建私有同义词
CREATE SYNONYM emp FOR employees;

-- 为视图创建私有同义词
CREATE SYNONYM emp_vw FOR employee_view;

-- 为序列创建私有同义词
CREATE SYNONYM emp_id_seq FOR emp_seq;

-- 为过程创建私有同义词
CREATE SYNONYM hire_emp FOR hire_employee;

创建公有同义词

sql
-- 为表创建公有同义词
CREATE PUBLIC SYNONYM dept FOR departments;

-- 为视图创建公有同义词
CREATE PUBLIC SYNONYM dept_vw FOR department_view;

-- 为序列创建公有同义词
CREATE PUBLIC SYNONYM dept_id_seq FOR dept_seq;

-- 为函数创建公有同义词
CREATE PUBLIC SYNONYM get_salary FOR calculate_salary;

创建跨数据库链接的同义词

sql
-- 为远程表创建同义词
CREATE SYNONYM remote_emp FOR employees@remote_db;

-- 为远程视图创建公有同义词
CREATE PUBLIC SYNONYM remote_dept FOR departments@remote_db;

同义词使用 ​

使用同义词

sql
-- 使用表同义词
SELECT * FROM emp WHERE department_id = 10;

-- 使用视图同义词
SELECT * FROM emp_vw WHERE hire_date > SYSDATE - 30;

-- 使用序列同义词
INSERT INTO emp (employee_id, first_name, last_name)
VALUES (emp_id_seq.NEXTVAL, 'Tom', 'Jones');

-- 使用过程同义词
EXEC hire_emp(1001, 'Alice', 'Brown', 20);

-- 使用函数同义词
SELECT employee_id, first_name, get_salary(employee_id) AS salary
FROM emp;

在PL/SQL中使用同义词

sql
DECLARE
  v_emp_count NUMBER;
BEGIN
  -- 使用同义词查询
  SELECT COUNT(*) INTO v_emp_count FROM emp;
  
  -- 使用同义词调用过程
  hire_emp(emp_id_seq.NEXTVAL, 'Bob', 'Wilson', 30);
  
  DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_emp_count);
END;
/

同义词修改 ​

修改同义词

  • Oracle不支持直接修改同义词
  • 需要先删除同义词，然后重新创建

示例

sql
-- 修改私有同义词
DROP SYNONYM emp;
CREATE SYNONYM emp FOR hr.employees;

-- 修改公有同义词
DROP PUBLIC SYNONYM dept;
CREATE PUBLIC SYNONYM dept FOR hr.departments;

同义词删除 ​

删除私有同义词

sql
-- 删除私有同义词
DROP SYNONYM emp;
DROP SYNONYM emp_vw;

删除公有同义词

sql
-- 删除公有同义词
DROP PUBLIC SYNONYM dept;
DROP PUBLIC SYNONYM dept_vw;

注意事项

  • 删除同义词前确认不再需要该同义词
  • 删除同义词不会影响基础对象
  • 删除同义词会影响依赖该同义词的代码
  • 定期清理无用的同义词，减少数据库对象数量

同义词监控和管理 ​

查看同义词信息

sql
-- 查看用户拥有的私有同义词
SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms;

-- 查看公有同义词
SELECT synonym_name, table_owner, table_name, db_link
FROM all_synonyms
WHERE owner = 'PUBLIC';

-- 查看所有同义词（需要DBA权限）
SELECT owner, synonym_name, table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner NOT IN ('SYS', 'SYSTEM');

检查无效同义词

sql
-- 检查无效的私有同义词
SELECT synonym_name, table_owner, table_name
FROM user_synonyms s
WHERE NOT EXISTS (
  SELECT 1 FROM all_objects o
  WHERE o.owner = s.table_owner
  AND o.object_name = s.table_name
);

-- 检查无效的公有同义词
SELECT synonym_name, table_owner, table_name
FROM all_synonyms s
WHERE s.owner = 'PUBLIC'
  AND NOT EXISTS (
    SELECT 1 FROM all_objects o
    WHERE o.owner = s.table_owner
    AND o.object_name = s.table_name
  );

同义词管理最佳实践

  • 合理命名：使用有意义的同义词名称，便于识别
  • 适当使用：仅为频繁使用的对象创建同义词
  • 权限控制：公有同义词需要谨慎创建，注意权限控制
  • 定期清理：定期清理无用的同义词
  • 文档化：记录同义词的用途和对应关系

版本差异考虑 ​

Oracle 11g

  • 序列特性：支持基本的序列功能
  • 同义词特性：支持基本的同义词功能
  • 最佳实践：合理设置序列缓存大小，适当使用同义词

Oracle 12c

  • 序列特性：支持标识列（IDENTITY COLUMN），可自动生成序列值
  • 同义词特性：支持跨PDB的同义词
  • 最佳实践：使用标识列简化序列管理，利用跨PDB同义词

Oracle 19c

  • 序列特性：增强了序列的性能和可靠性
  • 同义词特性：支持更多对象类型的同义词
  • 最佳实践：利用自动序列管理，合理使用同义词简化访问

Oracle 21c

  • 序列特性：进一步增强了序列的功能和性能
  • 同义词特性：支持更多跨数据库的同义词功能
  • 最佳实践：利用新的序列特性，简化同义词管理

常见问题（FAQ） ​

Q1: 序列值出现间隙的原因是什么？ ​

A1: 序列值出现间隙的原因：

  • 缓存丢失：数据库重启或实例崩溃时，缓存中的序列值会丢失
  • 回滚操作：使用序列值的事务回滚后，序列值不会回滚
  • 显式获取：显式调用NEXTVAL但未使用
  • 并行操作：RAC环境中多个节点可能导致间隙
  • 序列修改：修改序列的步长或其他参数

Q2: 如何避免序列值出现间隙？ ​

A2: 避免序列值间隙的方法：

  • 使用NOCACHE：禁用序列缓存，但会影响性能
  • 使用ORDER：在RAC环境中使用ORDER选项
  • 避免回滚：确保使用序列值的事务不回滚
  • 合理设计：接受序列值可能有间隙的特性
  • 使用标识列：在Oracle 12c+中使用标识列

Q3: 如何在RAC环境中优化序列性能？ ​

A3: 在RAC环境中优化序列性能的方法：

  • 合理设置缓存大小：增大缓存大小减少节点间争用
  • 使用NOORDER：非严格要求顺序时使用NOORDER
  • 使用高步长：为每个节点分配不同的步长范围
  • 监控性能：定期监控序列性能，调整参数
  • 考虑使用标识列：在Oracle 12c+中使用标识列

Q4: 如何为现有表添加序列生成的主键？ ​

A4: 为现有表添加序列生成主键的方法：

  • 创建序列：为表创建对应的序列
  • 修改表：添加主键列（如果不存在）
  • 更新数据：为现有数据生成主键值
  • 创建触发器：创建触发器自动生成主键值
  • 测试验证：测试插入操作是否自动生成主键

Q5: 如何管理大量序列？ ​

A5: 管理大量序列的方法：

  • 命名规范：使用统一的命名规范
  • 集中管理：建立序列管理文档
  • 定期审计：定期审计序列使用情况
  • 自动管理：使用脚本自动创建和管理序列
  • 清理无用：定期清理无用的序列

Q6: 同义词和视图的区别是什么？ ​

A6: 同义词和视图的区别：

  • 定义：同义词是对象别名，视图是查询结果集
  • 存储：同义词仅存储别名信息，视图存储查询定义
  • 功能：同义词简化访问，视图可以转换和过滤数据
  • 权限：同义词继承基础对象权限，视图有独立权限
  • 性能：同义词无性能开销，视图可能有查询开销

Q7: 如何解决同义词权限问题？ ​

A7: 解决同义词权限问题的方法：

  • 检查基础对象权限：确保用户有基础对象的访问权限
  • 使用授权：为用户授予基础对象的适当权限
  • 使用角色：通过角色管理同义词权限
  • 检查同义词定义：确保同义词指向正确的对象
  • 使用公有同义词：对于需要广泛访问的对象使用公有同义词

Q8: 如何使用同义词简化跨数据库访问？ ​

A8: 使用同义词简化跨数据库访问的方法：

  • 创建数据库链接：建立到远程数据库的链接
  • 创建同义词：为远程对象创建同义词
  • 简化访问：通过同义词直接访问远程对象
  • 权限管理：确保数据库链接有适当的权限
  • 性能优化：考虑远程访问的性能影响

Q9: 如何监控序列的使用情况？ ​

A9: 监控序列使用情况的方法：

  • 查询数据字典：通过user_sequences查看序列信息
  • 监控性能视图：通过v$segment_statistics查看序列统计信息
  • 审计使用：启用审计跟踪序列使用
  • 定期检查：定期检查序列的当前值和使用情况
  • 设置告警：为接近最大值的序列设置告警

Q10: 如何迁移序列和同义词？ ​

A10: 迁移序列和同义词的方法：

  • 导出导入：使用expdp/impdp或exp/imp迁移
  • 生成脚本：生成创建序列和同义词的脚本
  • 保持一致性：确保目标环境中的序列和同义词与源环境一致
  • 测试验证：迁移后测试序列和同义词的功能
  • 更新引用：更新依赖序列和同义词的代码

Q11: 如何处理序列达到最大值的情况？ ​

A11: 处理序列达到最大值的情况：

  • 使用CYCLE：如果业务允许，使用CYCLE选项
  • 修改序列：增加序列的最大值
  • 创建新序列：创建新的序列替换旧序列
  • 使用更大的数据类型：考虑使用更大范围的数据类型
  • 监控告警：设置序列接近最大值的告警

Q12: 如何使用同义词实现应用程序的模块化？ ​

A12: 使用同义词实现应用程序模块化的方法：

  • 抽象数据访问：通过同义词隐藏实际表结构
  • 简化重构：修改基础对象时无需修改应用代码
  • 支持多环境：不同环境使用不同的同义词映射
  • 权限隔离：通过同义词控制数据访问权限
  • 简化维护：集中管理数据访问点

Q13: 如何优化同义词的使用？ ​

A13: 优化同义词使用的方法：

  • 适当使用：仅为频繁使用的对象创建同义词
  • 合理命名：使用清晰、一致的命名规范
  • 避免嵌套：避免创建嵌套的同义词
  • 定期清理：清理无用的同义词
  • 文档化：记录同义词的用途和对应关系

Q14: 如何在不同Oracle版本中管理序列？ ​

A14: 在不同Oracle版本中管理序列的方法：

  • Oracle 11g：使用传统序列管理
  • Oracle 12c+：考虑使用标识列
  • 版本兼容：编写兼容不同版本的序列管理代码
  • 迁移策略：制定版本升级时的序列迁移策略
  • 测试验证：在不同版本中测试序列功能

Q15: 如何处理同义词指向的对象被删除的情况？ ​

A15: 处理同义词指向对象被删除的情况：

  • 定期检查：定期检查同义词的有效性
  • 使用视图：考虑使用视图代替同义词
  • 异常处理：在应用程序中添加异常处理
  • 自动修复：编写脚本自动修复无效同义词
  • 监控告警：设置无效同义词的监控告警