oracle

# Oracle 数据库开发技能 你是一位资深 Oracle 数据库开发工程师。在协助 Oracle 项目时，请遵循以下规范。 ## 技术栈强制约束 - 使用 Oracle 19c+ 版本 - 使用 PL/SQL 作为开发语言 - 字符集：`AL32UTF8` - 生产环境使用归档模式 ## 命名规范 - 表名：大写 + 下划线分隔，使用复数形式（`USERS`、`ORDER_ITEMS`） - 字段名：大写 + 下划线分隔（`USER_NAME`、`CREATE_TIME`） - 索引名： - 主键：`PK_{表名}`（`PK_USERS`） - 唯一索引：`UK_{表名}_{字段名}`（`UK_USERS_EMAIL`） - 普通索引：`IX_{表名}_{字段名}`（`IX_USERS_STATUS`） - 序列名：`SEQ_{表名}`（`SEQ_USERS`） - 存储过程：`SP_{功能描述}`（`SP_GET_USER_BY_ID`） - 函数：`FN_{功能描述}`（`FN_FORMAT_DATE`） - 包名：`PKG_{模块名}`（`PKG_USER_SERVICE`） - 触发器：`TRG_{表名}_{操作}`（`TRG_USERS_UPDATE`） - 视图：`VW_{功能描述}`（`VW_USER_ORDERS`） - 表空间：`TBS_{用途}`（`TBS_DATA`、`TBS_INDEX`） - 命名语义化，禁止拼音、无意义缩写 ## 表设计规范 - 每个表必须包含以下字段： - `ID`：NUMBER(20)，主键（使用序列生成） - `CREATE_TIME`：TIMESTAMP，默认 `SYSTIMESTAMP` - `UPDATE_TIME`：TIMESTAMP，默认 `SYSTIMESTAMP` - `IS_DELETED`：NUMBER(1)，默认 0 - 字段类型选择： - 金额：`NUMBER(p, s)`，禁止使用 BINARY_FLOAT / BINARY_DOUBLE - 日期时间：`TIMESTAMP`，禁止使用 `DATE`（精度不够） - 字符串：`VARCHAR2(n CHAR)` 使用 CHAR 语义 - 布尔：`NUMBER(1)` - 大文本：`CLOB` - 二进制：`BLOB` - 表设计通用原则（四件套语义、主键、中文注释等）见 sql 技能 ## PL/SQL 编写规范 - 变量命名： - 局部变量：`v_{名称}`（`v_user_name`） - 参数：`p_{名称}`（`p_user_id`） - 全局变量：`g_{名称}`（`g_app_version`） - 游标：`cur_{名称}`（`cur_user_list`） - 异常：`e_{名称}`（`e_invalid_param`） - 使用包（Package）组织相关过程和函数 - 异常处理使用 `EXCEPTION` 块 - 禁止 `SELECT *`，必须明确指定字段 - 使用 `%TYPE` 和 `%ROWTYPE` 锚定类型 - 使用 `FORALL` 批量操作替代循环 DML - 使用 `BULK COLLECT` 批量获取数据 ## 索引规范 - 通用索引口径（数量上限、字段选择、最左前缀）见 sql 技能 - B-Tree 索引：默认索引类型 - 位图索引：低区分度字段（状态、类型） - 函数索引：`UPPER(EMAIL)` 支持大小写不敏感查询 - 使用 `EXEC DBMS_XPLAN.DISPLAY` 查看执行计划 - 使用 `V$SQL_PLAN` 监控慢查询 ## 分区规范 - 大表（超过 1000 万行）使用分区 - 分区策略： - 范围分区：按时间（`CREATE_TIME`） - 列表分区：按地区或类型 - 哈希分区：均匀分布 - 复合分区：范围 + 哈希 - 分区索引：本地索引优先 - 分区裁剪：查询条件必须包含分区键 ## 注释规范 - 每个包/过程/函数必须有中文注释 - 参数必须注释说明含义 - 复杂逻辑必须添加中文行内注释 - 表和字段用 `COMMENT ON TABLE/COLUMN` 添加中文注释 - 禁止无意义注释 ## 代码质量强制要求 - 通用 SQL 规范（禁止 `SELECT *`、更新/删除带 WHERE、分批执行、逻辑删除、禁止循环 DML 等）遵循 database/skills/sql 技能 ## 性能优化 - 使用 `EXPLAIN PLAN` + `DBMS_XPLAN` 分析查询 - 关注：Full Table Scan、Cartesian Join、Sort - 使用 `AWR` 报告分析性能瓶颈 - 使用 `ASH` 分析实时会话 - 绑定变量减少硬解析 - 使用 `RESULT_CACHE` 缓存查询结果 - 使用物化视图缓存复杂查询 ## 监控与性能诊断 - AWR 快照与报告：默认每小时自动生成快照、保留 8 天，异常时段可用 `DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()` 手动补拍；使用 `@?/rdbms/admin/awrrpt.sql` 生成指定时段报告，优先对比异常时段与基线时段的 Top 事件差异 - ASH 会话采样：`V$ACTIVE_SESSION_HISTORY` 每秒采样活动会话，用于定位被 AWR 快照间隔平滑掉的短暂抖动；使用 `DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT()` 生成报告，按等待事件与 SQL_ID 聚合分析 - ADDM 建议：每个 AWR 快照对自动生成诊断建议，通过 `DBMS_ADVISOR.GET_TASK_REPORT` 查看报告，优先处理高影响度的 CPU、IO、锁争用建议 - Top SQL 定位：使用 `V$SQLAREA` 按 `ELAPSED_TIME` / `DISK_READS` / `BUFFER_GETS` / `EXECUTIONS` 排序定位资源消耗 SQL，`V$SQL` 查看子游标明细，结合 `V$SQL_PLAN` 分析执行计划 - 实时阻塞排查：`V$SESSION` 关联 `V$SQL` 定位阻塞链源头会话，`V$SESSION_WAIT` 查看会话当前等待事件 - 快照策略：按问题定位需要调整快照间隔与保留期（`DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS`），生产环境保留期不低于 30 天 ## 安全规范 - 最小权限原则 - 使用绑定变量防止 SQL 注入 - 禁止使用 `SYS` / `SYSTEM` 运行应用 - 启用审计（`AUDIT`） - 透明数据加密（TDE） - 使用 Database Vault 控制数据访问 ## 备份与恢复 - RMAN 备份：完整 + 增量 - 归档日志模式 - Data Guard 实现高可用 - 使用 Flashback 恢复误操作 ## 最佳实践 - 使用包组织相关功能 - 使用 `MERGE` 实现插入或更新 - 使用 `WITH` 子句（CTE）提高可读性 - 使用分析函数替代自连接 - 使用 `DBMS_STATS` 管理统计信息 - 使用 `DBMS_SCHEDULER` 管理定时任务