Oracle

Oracle Database — 企业级关系型数据库

Oracle Database 是全球领先的企业级关系型数据库管理系统，以其高可用性、高性能、强安全性及丰富的功能集（RAC、Data Guard、Flashback、高级分区、物化视图等）著称。

Workflow — 使用决策树

遇到 Oracle 相关需求时，按以下顺序决策:

Step 1: 明确场景
├── 编写 SQL 查询/DDL/DML?       → references/09-sql-syntax.md
├── 使用内置函数?                  → 字符串/日期/聚合 → references/01-functions-string.md / 02-functions-date.md
├── 窗口/分析函数?                 → references/03-analytic-functions.md
├── 编写 PL/SQL?                  → references/04-plsql-guide.md
├── 性能调优/执行计划?             → references/05-performance-tuning.md
├── 备份恢复?                     → references/06-backup-recovery.md
├── Data Guard / RAC?             → references/07-dataguard-rac.md
├── 安全/权限/审计?                → references/08-security.md
└── 分区/物化视图/Flashback/AQ?   → references/10-features.md

Step 2: 选择工具
├── 交互式查询 → SQL*Plus / SQL Developer / DBeaver
├── 批量脚本   → SQL*Plus 静默模式
├── PL/SQL 调试 → SQL Developer / TOAD / PL/SQL Developer
└── 自动化运维 → OEM / 脚本

Step 3: 确定环境
├── 版本 → 19c (LTS), 21c/23c (最新)
├── 架构 → 单实例 / RAC / Data Guard / RAC+DG
├── CDB/PDB? → 12c+ 多租户
└── 字符集 → AL32UTF8, ZHS16GBK

When to Use / When NOT to

Boundary — 能力边界

超出范围时请考虑：PostgreSQL（开源关系型）、MongoDB（文档）、Redis（缓存）、Elasticsearch（全文搜索）、MySQL（轻量 Web）。

---

SQL 语法速查

Oracle 的 SQL 差异主要体现在以下方面。完整内容见 references/09-sql-syntax.md。

函数速查

---

高级特性索引

---

Gotchas — 常见陷阱

---

FAQ

Q1: VARCHAR2 和 NVARCHAR2 区别？ VARCHAR2 使用数据库字符集（AL32UTF8/ZHS16GBK），NVARCHAR2 使用国家字符集（AL16UTF16）。推荐一般场景用 VARCHAR2，多语言用 NVARCHAR2。

Q2: ROWNUM 和 ROW_NUMBER() 区别？ ROWNUM 是伪列（先分配后排序），ROW_NUMBER() 是分析函数（排序后分配序号）。

Q3: Oracle vs PostgreSQL 主要差异？

Q4: UNDO 和 REDO 区别？ REDO 记录变更（重做/恢复），UNDO 记录变更前数据（回滚/一致性读/闪回）。

Q5: 何时用物化视图？ 查询大聚合可接受延迟、基表变更不频繁、需要跨数据库缓存、需要查询重写。

Q6: 分区表常见误区？ 分区不保证查询加速（需分区键）、不能解决所有大表问题、分区不是越多越好、OLTP 也适合分区。

Q7: 什么是读一致性？ Oracle 通过 UNDO 实现 SELECT 不加锁也不被写阻塞，查询使用查询开始时的 SCN 读取一致性版本。

Q8: 死锁如何处理？ Oracle 3 秒内自动检测，回滚牺牲品语句并抛 ORA-00060。最佳实践：统一访问顺序、事务简短。

Q9: CDB 和 PDB 是什么？ 12c+ 多租户：CDB = 容器数据库，PDB = 可插拔数据库。一个 CDB 最多 4096 个 PDB。

Q10: KILL SESSION 后连接未断开？ 标记为 KILLED，下次执行 SQL 时断开。KILL SESSION 'sid,serial#' IMMEDIATE 可立即断开。

Q11: REDO 日志切换太频繁？ 增加 REDO 日志大小（建议 15-30 分钟切换一次）、增加日志组数（至少 3-4 组）。

Q12: ORA-01555 "Snapshot Too Old"？ UNDO 数据被覆盖。增大 UNDO 表空间、减少 UNDO_RETENTION、优化长查询。

Q13: Oracle 中如何实现分页？ SELECT * FROM (SELECT t.*, ROWNUM AS rn FROM (SELECT ... ORDER BY col) t) WHERE rn BETWEEN 11 AND 20 或 12c+ OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY。

Q14: 什么是 FORCE LOGGING？ 强制所有 DML 写 REDO（即使是 NOLOGGING 操作），Data Guard 环境要求开启。

Q15: 如何查看当前数据库版本？ SELECT * FROM v$version; 或 SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

---

Keywords

oracle, Oracle Database, PL/SQL, SQL*Plus, RAC, Data Guard, ADG, RMAN, expdp, impdp, flashback, AWR, ASH, ADDM, DBMS_XPLAN, VARCHAR2, NUMBER, CLOB, SEQUENCE, SYNONYM, CONNECT BY, PIVOT, LISTAGG, MERGE, INSERT ALL, MODEL, MATCH_RECOGNIZE, 分析函数, 窗口函数, ROW_NUMBER, RANK, LAG, LEAD, 存储过程, 包, 触发器, 游标, REF CURSOR, 动态SQL, FORALL, BULK COLLECT, 分区表, 物化视图, 位图索引, 表空间, TDE, FGA, VPD, DBMS_STATS, SPM, CDB, PDB, 多租户, UNDO, REDO, 读一致性, ORA-01555

References

• references/01-functions-string.md — 字符串/数字/NULL 处理函数
• references/02-functions-date.md — 日期/转换函数
• references/03-analytic-functions.md — 分析函数（窗口函数）+ 聚合
• references/04-plsql-guide.md — PL/SQL 详解
• references/05-performance-tuning.md — 性能调优
• references/06-backup-recovery.md — 备份恢复
• references/07-dataguard-rac.md — Data Guard / RAC
• references/08-security.md — 安全与权限
• references/09-sql-syntax.md — SQL 语法详解
• references/10-features.md — 特有特性（分区/物化视图/Flashback/AQ）
• examples/01-plsql-procedure.md — PL/SQL 存储过程示例
• examples/02-awr-analysis.md — AWR 性能分析示例
• examples/03-rman-backup.md — RMAN 备份示例
• examples/04-dataguard-setup.md — Data Guard 搭建示例
• Oracle 19c 官方文档
• Oracle Live SQL (在线练习)

示例：PL/SQL 存储过程 — 员工薪资管理包

场景

创建一个完整的员工薪资管理包，支持涨薪、查询年收入、批量调整部门薪资。

包规范

CREATE OR REPLACE PACKAGE salary_mgmt AS
    -- 涨薪
    PROCEDURE give_raise(p_emp_id NUMBER, p_percent NUMBER);
    -- 查询年收入
    FUNCTION annual_income(p_emp_id NUMBER) RETURN NUMBER;
    -- 批量调整部门薪资
    PROCEDURE dept_raise(p_dept_id NUMBER, p_percent NUMBER);
    -- 获取部门薪资统计
    FUNCTION dept_stats(p_dept_id NUMBER) RETURN SYS_REFCURSOR;
END salary_mgmt;
/

包体

CREATE OR REPLACE PACKAGE BODY salary_mgmt AS

    PROCEDURE give_raise(p_emp_id NUMBER, p_percent NUMBER) AS
        v_old_sal employees.salary%TYPE;
    BEGIN
        SELECT salary INTO v_old_sal FROM employees WHERE employee_id = p_emp_id FOR UPDATE;
        UPDATE employees SET salary = salary * (1 + p_percent/100) WHERE employee_id = p_emp_id;
        DBMS_OUTPUT.PUT_LINE('员工 ' || p_emp_id || ': ' || v_old_sal || ' → ' || ROUND(v_old_sal*(1+p_percent/100),2));
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, '员工 ' || p_emp_id || ' 不存在');
        WHEN OTHERS THEN ROLLBACK; RAISE;
    END;

    FUNCTION annual_income(p_emp_id NUMBER) RETURN NUMBER AS
        v_sal employees.salary%TYPE;
        v_comm employees.commission_pct%TYPE;
    BEGIN
        SELECT salary, NVL(commission_pct, 0) INTO v_sal, v_comm
        FROM employees WHERE employee_id = p_emp_id;
        RETURN v_sal * 12 + v_sal * NVL(v_comm, 0);
    END;

    PROCEDURE dept_raise(p_dept_id NUMBER, p_percent NUMBER) AS
    BEGIN
        UPDATE employees SET salary = salary * (1 + p_percent/100)
        WHERE department_id = p_dept_id;
        DBMS_OUTPUT.PUT_LINE('部门 ' || p_dept_id || ' 已更新 ' || SQL%ROWCOUNT || ' 行');
        COMMIT;
    END;

    FUNCTION dept_stats(p_dept_id NUMBER) RETURN SYS_REFCURSOR AS
        c SYS_REFCURSOR;
    BEGIN
        OPEN c FOR SELECT employee_id, last_name, salary,
                          annual_income(employee_id) AS annual
                   FROM employees WHERE department_id = p_dept_id
                   ORDER BY salary DESC;
        RETURN c;
    END;

END salary_mgmt;
/

调用示例

-- 单员工涨薪 10%
BEGIN salary_mgmt.give_raise(100, 10); END;
/

-- 查询年收入
SELECT salary_mgmt.annual_income(100) FROM DUAL;

-- 部门批量涨薪 5%
BEGIN salary_mgmt.dept_raise(50, 5); END;
/

-- 获取部门薪资统计
VARIABLE c REFCURSOR;
EXEC :c := salary_mgmt.dept_stats(50);
PRINT c;

示例：AWR 性能分析 — 定位 Top SQL 与等待事件

场景

某生产数据库近期响应变慢，需要通过 AWR/ASH 分析找到性能瓶颈。

步骤 1：创建 AWR 快照

-- 在性能问题期间创建快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 等待一段时间（比如 30 分钟）后再次创建
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

步骤 2：生成 AWR 报告

-- 查找快照 ID
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC FETCH FIRST 5 ROWS ONLY;

-- 生成 HTML 格式 AWR 报告
-- 假设 snap_id 分别为 1250 和 1255
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid      => 1250,
    l_eid      => 1255,
    l_options  => 0
));

步骤 3：ASH 分析 — Top 等待事件

-- 最近 10 分钟的 Top 等待事件
SELECT event, wait_class, COUNT(*) AS session_seconds,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '10' MINUTE
GROUP BY event, wait_class
ORDER BY session_seconds DESC;

步骤 4：定位 Top SQL

-- 按消耗找 Top SQL
SELECT sql_id,
       ROUND(SUM(elapsed_time)/1000000, 2) AS total_sec,
       COUNT(*) AS executions,
       ROUND(AVG(elapsed_time)/1000, 2) AS avg_ms,
       SUBSTR(MAX(sql_text), 1, 100) AS sql_sample
FROM v$active_session_history ash
JOIN v$sql sq USING (sql_id)
WHERE sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY total_sec DESC
FETCH FIRST 5 ROWS ONLY;

步骤 5：分析特定 SQL 的执行计划

-- 查看 Top SQL 的执行计划
-- 假设 Top SQL 的 sql_id 为 'abc123xyz4567'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abc123xyz4567', format => 'ALLSTATS LAST'));

步骤 6：使用 SQL Tuning Advisor

DECLARE
    v_task VARCHAR2(30);
BEGIN
    v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => 'abc123xyz4567',
        scope  => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit => 300
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task);
    DBMS_OUTPUT.PUT_LINE('Task: ' || v_task);
END;
/

-- 查看建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => 'task_name_here') FROM DUAL;

分析要点

• db file sequential read — 单块读等待，通常是索引扫描
• log file sync — 提交等待，检查小事务频繁提交
• enq: TX - row lock contention — 行锁争用，检查并发更新相同行
• read by other session — 缓存争用，考虑调整 buffer cache

示例：RMAN 备份策略 — 每周全量 + 每日增量

场景

为生产库制定一套完整的备份策略：每周日凌晨 Level 0 全量备份，周一至周六 Level 1 增量备份，同时备份归档日志。

RMAN 配置

# 登录 RMAN
rman target /

# 配置备份策略（保留最近 7 天可恢复）
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

# 启用控制文件自动备份
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

# 启用备份优化（跳过未变更文件）
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

# 设置备份格式
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/orcl/%U';

# 设置设备类型和并行度
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

周日：Level 0 全量备份

rman target / <<EOF
RUN {
    ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
    BACKUP INCREMENTAL LEVEL 0 DATABASE
        TAG 'LEVEL0_WEEKLY'
        FORMAT '/backup/orcl/full_%d_%T_%s_%p.bkp';
    BACKUP ARCHIVELOG ALL DELETE INPUT
        FORMAT '/backup/orcl/arch_%d_%T_%s.bkp';
    BACKUP CURRENT CONTROLFILE
        FORMAT '/backup/orcl/ctrl_%d_%T_%s.bkp';
    RELEASE CHANNEL c1;
    RELEASE CHANNEL c2;
}
EOF

周一至周六：Level 1 增量备份

rman target / <<EOF
BACKUP INCREMENTAL LEVEL 1 DATABASE
    TAG 'LEVEL1_DAILY'
    FORMAT '/backup/orcl/incr_%d_%T_%s_%p.bkp';
BACKUP ARCHIVELOG ALL DELETE INPUT
    FORMAT '/backup/orcl/arch_%d_%T_%s.bkp';
EOF

验证备份

# 验证所有备份是否可恢复
rman target /
RMAN> RESTORE DATABASE VALIDATE;

# 列出备份集
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP OF DATABASE;

# 检查特定备份是否可用
RMAN> VALIDATE BACKUPSET <bs_key>;

模拟恢复

# 完全恢复（全量+增量自动应用）
rman target /
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

# 时间点恢复（恢复到某个时间点）
rman target /
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE UNTIL TIME "TO_DATE('2024-08-15 14:30:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2024-08-15 14:30:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> ALTER DATABASE OPEN RESETLOGS;

Cron 调度

# 编辑 crontab
# crontab -e

# 每周日凌晨 1:00 执行全量备份
0 1 * * 0 /u01/scripts/full_backup.sh >> /u01/logs/rman_full.log 2>&1

# 每天凌晨 2:00 执行增量备份（周日除外）
0 2 * * 1-6 /u01/scripts/incr_backup.sh >> /u01/logs/rman_incr.log 2>&1

# 每天凌晨 3:00 验证备份
0 3 * * * /u01/scripts/validate_backup.sh >> /u01/logs/rman_val.log 2>&1

示例：Data Guard 物理备库搭建

场景

生产库 PRIMARY (host1) 需要搭建一个物理备库 STANDBY (host2) 实现高可用，使用实时应用（Real-Time Apply）。

前提条件

• 主库已启用归档模式 (SELECT log_mode FROM v$database; 返回 ARCHIVELOG)
• 主备库 Oracle 版本一致
• 主备库网络互通（1521 端口）
• 主库已设置 FORCE LOGGING

步骤 1：主库参数配置

-- 设置 DB_UNIQUE_NAME
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)' SCOPE=BOTH;
ALTER SYSTEM SET DB_UNIQUE_NAME=PRIMARY SCOPE=SPFILE;

-- 设置归档目的地
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY' SCOPE=BOTH;

-- 备库归档传输（使用 ASYNC 模式，不影响主库性能）
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_host:1521/orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

-- 网络配置
ALTER SYSTEM SET FAL_CLIENT='PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER='STANDBY' SCOPE=BOTH;

-- 文件路径转换
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u02/oradata/orcl/' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u02/oradata/orcl/' SCOPE=SPFILE;

-- 启用手动备库文件管理
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

-- 重启数据库使 SPFILE 参数生效
SHUTDOWN IMMEDIATE;
STARTUP;

步骤 2：准备备库

# 备库 $ORACLE_HOME/network/admin/tnsnames.ora 配置
PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))
  )

# 备库 $ORACLE_HOME/network/admin/listener.ora 配置
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
  )

-- 备库参数文件（在备库创建 pfile，修改 db_unique_name）
-- 修改 /u01/app/oracle/admin/orcl/pfile/init.ora
-- *.db_unique_name='STANDBY'

步骤 3：使用 RMAN DUPLICATE 创建备库

# 在主库执行
rman target sys/password@PRIMARY auxiliary sys/password@STANDBY <<EOF
DUPLICATE TARGET DATABASE FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='STANDBY' COMMENT 'Standby'
    SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
    SET LOG_ARCHIVE_DEST_2='SERVICE=primary_host:1521/orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
    SET FAL_CLIENT='STANDBY'
    SET FAL_SERVER='PRIMARY'
    SET DB_FILE_NAME_CONVERT='/u02/oradata/orcl/','/u01/oradata/orcl/'
    SET LOG_FILE_NAME_CONVERT='/u02/oradata/orcl/','/u01/oradata/orcl/'
  NOFILENAMECHECK;
EOF

步骤 4：启动备库实时应用

-- 备库启动到 MOUNT 状态
STARTUP MOUNT;

-- 启用实时应用（备库自动应用归档日志）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

-- 验证实时应用状态
SELECT process, status, sequence# FROM v$managed_standby;
-- 预期看到 MRP0 进程状态为 APPLYING_LOG

步骤 5：启用 Active Data Guard（可选）

-- 备库只读打开（19c 及以前）
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
-- 此时备库以只读方式打开，同时应用日志

-- 验证备库可用
SELECT database_role, open_mode FROM v$database;
-- 预期返回: PHYSICAL STANDBY / READ ONLY WITH APPLY

步骤 6：验证同步状态

-- 主库查询
SELECT database_role, open_mode FROM v$database;
SELECT dest_name, status, error FROM v$archive_dest WHERE dest_name LIKE '%DEST_2';

-- 备库查询日志应用延迟
SELECT name, value, time_computed FROM v$dataguard_stats WHERE name LIKE '%lag%';
-- apply_lag: 应用延迟（秒）
-- transport_lag: 传输延迟（秒）

步骤 7：Switchover 切换（计划内）

# 主库操作
sqlplus / as sysdba
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

# 备库操作
sqlplus / as sysdba
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

字符串 / 数字 / NULL 处理函数

字符串函数

-- CONCAT / || — 字符串连接
SELECT 'Hello' || ' ' || 'World' FROM DUAL;          -- Hello World (推荐 ||)

-- SUBSTR — 截取子串
SELECT SUBSTR('Oracle Database', 1, 6) FROM DUAL;    -- Oracle
SELECT SUBSTR('Oracle Database', 8) FROM DUAL;       -- Database

-- INSTR — 查找子串位置
SELECT INSTR('oracleoracle', 'oracle') FROM DUAL;               -- 1
SELECT INSTR('oracleoracle', 'oracle', 1, 2) FROM DUAL;         -- 7（第 2 次出现）

-- LPAD / RPAD — 左/右填充
SELECT LPAD('123', 10, '*') FROM DUAL;                           -- *******123
SELECT RPAD('Oracle', 10, '-.-') FROM DUAL;                      -- Oracle-.-.-

-- TRIM / LTRIM / RTRIM — 去除空格/字符
SELECT TRIM('  Hello  ') FROM DUAL;                              -- Hello
SELECT LTRIM('xxxHello', 'x') FROM DUAL;                         -- Hello
SELECT RTRIM('Hello...', '.') FROM DUAL;                         -- Hello

-- REPLACE — 替换子串
SELECT REPLACE('Oracle Database 19c', '19c', '23c') FROM DUAL;  -- Oracle Database 23c

-- TRANSLATE — 字符级替换
SELECT TRANSLATE('12345', '123', 'abc') FROM DUAL;               -- abc45

-- 正则表达式系列
-- REGEXP_LIKE  — 正则匹配
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Z]');
-- REGEXP_SUBSTR — 正则提取子串
SELECT REGEXP_SUBSTR('contact@oracle.com', '@[^.]+\\.com') FROM DUAL;
-- REGEXP_REPLACE — 正则替换（手机号脱敏）
SELECT REGEXP_REPLACE('13812345678', '(\d{3})\d{4}(\d{4})', '\1****\2') FROM DUAL;
-- REGEXP_INSTR  — 正则查找位置
SELECT REGEXP_INSTR('Hello World', '[aeiou]') FROM DUAL;         -- 2

数字函数

-- ROUND — 四舍五入
SELECT ROUND(123.4567) FROM DUAL;      -- 123
SELECT ROUND(123.4567, 2) FROM DUAL;   -- 123.46
SELECT ROUND(123.4567, -2) FROM DUAL;  -- 100

-- TRUNC — 截断（不四舍五入）
SELECT TRUNC(123.4567) FROM DUAL;      -- 123
SELECT TRUNC(123.4567, 2) FROM DUAL;   -- 123.45

-- MOD — 取模
SELECT MOD(10, 3) FROM DUAL;           -- 1

-- ABS / CEIL / FLOOR
SELECT ABS(-10) FROM DUAL;             -- 10
SELECT CEIL(3.14) FROM DUAL;           -- 4
SELECT FLOOR(3.14) FROM DUAL;          -- 3

-- POWER / SQRT
SELECT POWER(2, 10) FROM DUAL;         -- 1024
SELECT SQRT(144) FROM DUAL;            -- 12

-- GREATEST / LEAST
SELECT GREATEST(10, 20, 5, 30) FROM DUAL;  -- 30
SELECT LEAST(10, 20, 5, 30) FROM DUAL;     -- 5

NULL 处理函数

-- NVL — 空值替换（2 个参数）
SELECT last_name, NVL(commission_pct, 0) AS commission FROM employees;

-- NVL2 — 空值条件（3 个参数）
SELECT last_name, NVL2(commission_pct, '有佣金', '无佣金') AS status FROM employees;

-- COALESCE — 返回第一个非空值（可变参数）
SELECT COALESCE(phone_number, email, '无联系方式') AS contact FROM employees;

-- NULLIF — 两值相等返回 NULL
SELECT NULLIF('A', 'B') FROM DUAL;  -- 'A'
SELECT NULLIF('A', 'A') FROM DUAL;  -- NULL

-- LNNVL — 反转条件结果（对 NULL 敏感）
SELECT * FROM employees WHERE LNNVL(commission_pct > 0.2);
-- 等价于: WHERE commission_pct IS NULL OR commission_pct <= 0.2

日期 / 转换函数

日期函数

-- 当前日期时间
SELECT SYSDATE FROM DUAL;              -- 当前系统日期时间
SELECT CURRENT_DATE FROM DUAL;         -- 当前会话时区日期时间
SELECT SYSTIMESTAMP FROM DUAL;         -- 当前系统时间戳（带时区）

-- EXTRACT — 提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL;

-- TO_DATE — 字符串转日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2024/01/15 14:30:00', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('15-JAN-24', 'DD-MON-YY') FROM DUAL;

-- TO_CHAR — 日期格式化
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'Dy') FROM DUAL;    -- 星期缩写

-- ADD_MONTHS — 加减月份
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -6) FROM DUAL;

-- MONTHS_BETWEEN — 月份间隔
SELECT MONTHS_BETWEEN(DATE '2024-12-31', DATE '2024-01-01') FROM DUAL;

-- LAST_DAY — 月末日期
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- NEXT_DAY — 下一个星期几
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;

-- TRUNC 日期版 — 截断到指定精度
SELECT TRUNC(SYSDATE) FROM DUAL;                          -- 当天 00:00:00
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;                 -- 当月第一天
SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;                  -- 当年第一天
SELECT TRUNC(SYSDATE, 'IW') FROM DUAL;                    -- 当周周一（ISO 周）

-- 时间间隔
SELECT SYSDATE + TO_YMINTERVAL('01-06') FROM DUAL;        -- 加 1年6个月
SELECT SYSDATE + TO_DSINTERVAL('3 12:00:00') FROM DUAL;   -- 加 3天12小时

转换函数

-- TO_CHAR — 数字格式化
SELECT TO_CHAR(1234567.89, 'FM999,999,999.00') FROM DUAL;  -- 1,234,567.89
SELECT TO_CHAR(1234567.89, 'FML999,999,999.00') FROM DUAL; -- $1,234,567.89

-- TO_NUMBER — 字符串转数字
SELECT TO_NUMBER('1,234.56', '999,999.99') FROM DUAL;     -- 1234.56

-- CAST — ANSI SQL 标准类型转换
SELECT CAST('12345' AS NUMBER) FROM DUAL;
SELECT CAST('2024-01-15' AS DATE) FROM DUAL;

-- CONVERT — 字符集转换
SELECT CONVERT('Oracle', 'AL32UTF8', 'ZHS16GBK') FROM DUAL;

-- SCN_TO_TIMESTAMP / TIMESTAMP_TO_SCN — SCN 与时间互转
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 100;

分析函数（窗口函数）+ 聚合

分析函数是 Oracle 最强大的功能之一，在不改变行数的情况下进行聚合计算。

聚合函数

-- 基础聚合
SELECT COUNT(*) FROM employees;                     -- 总行数
SELECT COUNT(commission_pct) FROM employees;        -- 非 NULL 行数
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(hire_date) FROM employees;
SELECT MIN(hire_date) FROM employees;

-- MEDIAN — 中位数
SELECT MEDIAN(salary) FROM employees;

-- STATS_MODE — 众数
SELECT STATS_MODE(department_id) FROM employees;

-- GROUP BY ROLLUP / CUBE（小计+总计）
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id IN (50, 80)
GROUP BY ROLLUP(department_id, job_id);   -- 小计 + 总计

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id IN (50, 80)
GROUP BY CUBE(department_id, job_id);    -- 所有维度小计

-- GROUPING — 区分 NULL 是数据值还是小计行
SELECT department_id, job_id, SUM(salary),
       CASE WHEN GROUPING(department_id)=1 THEN '总计'
            WHEN GROUPING(job_id)=1 THEN '小计'
            ELSE '明细'
       END AS rollup_level
FROM employees GROUP BY ROLLUP(department_id, job_id);

分析函数（窗口函数）

排序函数

-- ROW_NUMBER — 唯一序号
SELECT department_id, last_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS seq
FROM employees;

-- RANK / DENSE_RANK — 排名（允许并列）
SELECT department_id, last_name, salary,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;
-- RANK: 1,2,2,4   DENSE_RANK: 1,2,2,3

-- NTILE — 分桶
SELECT customer_id, amount,
       NTILE(5) OVER (ORDER BY amount DESC) AS bucket
FROM orders;

前后行访问

-- LAG — 访问前一行（环比计算）
SELECT hire_date, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary,
       salary - LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS diff
FROM employees;

-- LEAD — 访问后一行
SELECT hire_date, salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

窗口聚合

-- FIRST_VALUE / LAST_VALUE — 窗口首尾行
SELECT department_id, last_name, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id
           ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_in_dept
FROM employees;

-- 累计求和
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;

-- 移动平均（7 日）
SELECT sale_date, amount,
       AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- RATIO_TO_REPORT — 占比
SELECT department_id, last_name, salary,
       RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS pct_of_dept
FROM employees;

PL/SQL 详解

PL/SQL（Procedural Language/SQL）是 Oracle 的扩展 SQL，支持变量、条件、循环、异常处理等过程式编程。

块结构

DECLARE
    v_employee_id employees.employee_id%TYPE;
    v_salary      employees.salary%TYPE := 5000;
BEGIN
    SELECT employee_id, salary INTO v_employee_id, v_salary
    FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE('员工 ' || v_employee_id || ' 工资: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到员工');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

游标 (Cursor)

-- 隐式游标（SELECT INTO）
DECLARE v_name employees.last_name%TYPE;
BEGIN
    SELECT last_name INTO v_name FROM employees WHERE employee_id = 100;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
/

-- 显式游标
DECLARE
    CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id = 50;
    v_emp emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/

-- CURSOR FOR LOOP（最简洁）
BEGIN
    FOR rec IN (SELECT last_name, salary FROM employees WHERE department_id = 50)
    LOOP
        DBMS_OUTPUT.PUT_LINE(rec.last_name || ' 工资: ' || rec.salary);
    END LOOP;
END;
/

-- REF CURSOR（动态游标）
DECLARE
    TYPE refcur IS REF CURSOR;
    c_ref refcur;
    v_id   employees.employee_id%TYPE;
    v_name employees.last_name%TYPE;
BEGIN
    OPEN c_ref FOR 'SELECT employee_id, last_name FROM employees WHERE department_id = :d' USING 50;
    LOOP
        FETCH c_ref INTO v_id, v_name;
        EXIT WHEN c_ref%NOTFOUND;
    END LOOP;
    CLOSE c_ref;
END;
/

-- SYS_REFCURSOR 函数返回
CREATE OR REPLACE FUNCTION get_employees(p_dept_id NUMBER) RETURN SYS_REFCURSOR AS
    c SYS_REFCURSOR;
BEGIN
    OPEN c FOR SELECT * FROM employees WHERE department_id = p_dept_id;
    RETURN c;
END;
/

存储过程与函数

-- 存储过程
CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id IN employees.employee_id%TYPE,
    p_percent     IN NUMBER
) AS
    v_old_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_old_salary FROM employees WHERE employee_id = p_employee_id FOR UPDATE;
    UPDATE employees SET salary = salary * (1 + p_percent / 100) WHERE employee_id = p_employee_id;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, '员工不存在');
    WHEN OTHERS THEN ROLLBACK; RAISE;
END update_salary;
/

-- 函数（必须有返回值）
CREATE OR REPLACE FUNCTION get_annual_salary(p_employee_id NUMBER) RETURN NUMBER AS
    v_salary employees.salary%TYPE;
    v_commission employees.commission_pct%TYPE;
BEGIN
    SELECT salary, NVL(commission_pct, 0) INTO v_salary, v_commission
    FROM employees WHERE employee_id = p_employee_id;
    RETURN v_salary * 12 + v_salary * v_commission;
END get_annual_salary;
/

-- DETERMINISTIC 函数（可用于函数索引）
CREATE OR REPLACE FUNCTION calculate_tax(p_amount NUMBER) RETURN NUMBER DETERMINISTIC AS
BEGIN
    RETURN p_amount * 0.13;
END;
/

包 (Package)

-- 包规范（公开接口）
CREATE OR REPLACE PACKAGE emp_mgmt AS
    c_max_salary CONSTANT NUMBER := 50000;
    FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
    PROCEDURE raise_salary(p_emp_id NUMBER, p_pct NUMBER);
    PROCEDURE hire_employee(p_last_name VARCHAR2, p_email VARCHAR2, p_job_id VARCHAR2, p_salary NUMBER);
    PROCEDURE set_debug(p_mode BOOLEAN);
    PROCEDURE set_debug(p_mode VARCHAR2);  -- 重载
END emp_mgmt;
/

-- 包体（实现）
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
    v_last_action VARCHAR2(100);  -- 私有变量

    FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER IS
        v_sal employees.salary%TYPE;
    BEGIN
        SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
        RETURN v_sal;
    EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
    END;

    PROCEDURE raise_salary(p_emp_id NUMBER, p_pct NUMBER) IS
    BEGIN
        UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE employee_id = p_emp_id;
        v_last_action := 'Raised salary for ' || p_emp_id;
    END;

    PROCEDURE hire_employee(p_last_name VARCHAR2, p_email VARCHAR2, p_job_id VARCHAR2, p_salary NUMBER) IS
    BEGIN
        INSERT INTO employees(employee_id, last_name, email, job_id, salary, hire_date)
        VALUES (employees_seq.NEXTVAL, p_last_name, p_email, p_job_id, p_salary, SYSDATE);
        v_last_action := 'Hired ' || p_last_name;
    END;

    PROCEDURE set_debug(p_mode BOOLEAN) IS BEGIN g_debug_mode := p_mode; END;
    PROCEDURE set_debug(p_mode VARCHAR2) IS BEGIN g_debug_mode := UPPER(p_mode) = 'ON'; END;

    -- 包初始化（首次引用时执行一次）
    BEGIN
        v_last_action := 'Package initialized';
    END;
END emp_mgmt;
/

触发器 (Trigger)

-- DML 行级触发器（工资变更审计）
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
    BEFORE UPDATE OF salary ON employees FOR EACH ROW
    WHEN (OLD.salary != NEW.salary)
BEGIN
    INSERT INTO salary_audit_log(employee_id, old_salary, new_salary, changed_by, changed_at)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, USER, SYSDATE);
END;
/

-- 语句级触发器（非工作时间禁止修改）
CREATE OR REPLACE TRIGGER trg_no_dml_nonbusiness
    BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
    IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') OR
       TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 9 AND 18 THEN
        RAISE_APPLICATION_ERROR(-20001, '非工作时间禁止修改');
    END IF;
END;
/

-- INSTEAD OF 触发器（视图 DML）
CREATE OR REPLACE TRIGGER trg_v_emp_dept_ioi
    INSTEAD OF INSERT ON v_emp_dept FOR EACH ROW
BEGIN
    INSERT INTO employees(employee_id, last_name, salary, department_id)
    VALUES (:NEW.employee_id, :NEW.last_name, :NEW.salary,
            (SELECT department_id FROM departments WHERE department_name = :NEW.department_name));
END;
/

-- 登录审计触发器
CREATE OR REPLACE TRIGGER trg_logon_audit
    AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO logon_audit_log(session_id, user_name, logon_time)
    VALUES (SYS_CONTEXT('USERENV', 'SESSIONID'), USER, SYSDATE);
END;
/

异常处理

-- 预定义异常
EXCEPTION
    WHEN NO_DATA_FOUND THEN ...
    WHEN TOO_MANY_ROWS THEN ...
    WHEN DUP_VAL_ON_INDEX THEN ...
    WHEN VALUE_ERROR THEN ...
    WHEN ZERO_DIVIDE THEN ...
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); RAISE;

-- 自定义异常
DECLARE
    e_salary_too_high EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001);
BEGIN
    IF v_salary > 50000 THEN RAISE e_salary_too_high; END IF;
EXCEPTION WHEN e_salary_too_high THEN ... END;

-- RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR(-20001, '订单已取消', TRUE);

集合类型

-- 关联数组（Index-By Table）
DECLARE
    TYPE dept_name_tab IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
    t_dept_names dept_name_tab;
BEGIN
    FOR rec IN (SELECT department_id, department_name FROM departments)
    LOOP t_dept_names(rec.department_id) := rec.department_name; END LOOP;
END;
/

-- 嵌套表
CREATE OR REPLACE TYPE phone_list AS TABLE OF VARCHAR2(20);
/
DECLARE t_phones phone_list := phone_list('13800138000', '13900139000');
BEGIN t_phones.EXTEND(1); t_phones(3) := '13700137000'; END;
/

-- VARRAY（定长数组）
CREATE OR REPLACE TYPE score_list IS VARRAY(10) OF NUMBER;
/

-- 集合方法: EXISTS, COUNT, LIMIT, FIRST/LAST, PRIOR/NEXT, EXTEND, TRIM, DELETE

动态 SQL

-- EXECUTE IMMEDIATE（简单动态 SQL）
CREATE OR REPLACE FUNCTION count_rows(p_table_name VARCHAR2) RETURN NUMBER AS
    v_sql VARCHAR2(200); v_cnt NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    RETURN v_cnt;
END;
/

-- 带绑定变量（防 SQL 注入）
EXECUTE IMMEDIATE 'SELECT last_name FROM employees WHERE employee_id = :id' INTO v_name USING 100;

-- FORALL（批量 DML，提升性能）
DECLARE
    TYPE id_list IS TABLE OF employees.employee_id%TYPE;
    t_ids id_list := id_list(100, 101, 102);
BEGIN
    FORALL i IN t_ids.FIRST..t_ids.LAST
        UPDATE employees SET salary = salary * 1.1 WHERE employee_id = t_ids(i);
    COMMIT;
END;
/

-- BULK COLLECT（批量读取）
DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    t_emps emp_tab;
BEGIN
    SELECT * BULK COLLECT INTO t_emps FROM employees WHERE department_id = 50;
END;
/

性能调优 — AWR / ASH / ADDM / DBMS_XPLAN

EXPLAIN PLAN / DBMS_XPLAN

-- 生成执行计划
EXPLAIN PLAN FOR
SELECT d.department_name, e.last_name, e.salary
FROM departments d JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > 10000;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看实际执行计划（带统计信息）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abc123', format => 'ALLSTATS LAST'));

-- 格式化选项
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC'));    -- 基本
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));  -- 典型（默认）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));      -- 全部
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ADVANCED')); -- 高级（含提示）

-- 查找高消耗 SQL
SELECT sql_id, ROUND(elapsed_time/1000,2) AS elapsed_ms,
       cpu_time, buffer_gets, executions,
       SUBSTR(sql_text, 1, 200) AS sql_text_short
FROM v$sql
WHERE elapsed_time > 0 AND executions > 0
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

AWR（Automatic Workload Repository）

-- 生成 AWR 报告（HTML 格式）
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid      => 100,    -- 起始快照 ID
    l_eid      => 110,    -- 结束快照 ID
    l_options  => 0
));

-- 创建 AWR 快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 修改 AWR 保留策略（默认 8 天）
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 14400);   -- 10天
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60);       -- 间隔 60 分钟

ASH（Active Session History）

-- 最近 10 分钟的 Top 等待事件
SELECT event, COUNT(*) AS cnt,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '10' MINUTE
GROUP BY event ORDER BY cnt DESC;

-- Top SQL（基于 ASH）
SELECT sql_id, COUNT(*) AS hits,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '10' MINUTE AND sql_id IS NOT NULL
GROUP BY sql_id ORDER BY hits DESC FETCH FIRST 5 ROWS ONLY;

ADDM（Automatic Database Diagnostic Monitor）

DECLARE
    v_task_name VARCHAR2(30);
BEGIN
    v_task_name := 'MY_ADDM_TASK';
    DBMS_ADDM.ANALYZE_DB(
        task_name => v_task_name,
        begin_snapshot => 100,
        end_snapshot => 110
    );
END;
/

SQL Tuning Advisor

DECLARE
    v_task_name VARCHAR2(30);
BEGIN
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => 'abc123xyz4567',
        scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit  => 300,
        task_name   => 'tune_sql_abc123'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);
END;
/

-- 查看调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => 'tune_sql_abc123') FROM DUAL;

-- 接受 SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tune_sql_abc123');

DBMS_STATS — 统计信息

-- 收集表级统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR', tabname => 'EMPLOYEES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE, degree => DBMS_STATS.AUTO_DEGREE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

-- 收集模式级统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR', options => 'GATHER AUTO');

-- 锁定/解锁统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 恢复历史统计信息
EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'EMPLOYEES', SYSTIMESTAMP - 7);

-- 查看统计信息
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM dba_tab_statistics WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

SPM（SQL Plan Management）

-- 加载执行计划到 SPM
DECLARE v_plans_loaded PLS_INTEGER;
BEGIN
    v_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123xyz4567');
END;
/

-- 查看 SPM 基线
SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;

-- 演变计划
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_handle_here', verify => 'YES') FROM DUAL;

-- 固定计划
DECLARE v_fixed PLS_INTEGER;
BEGIN
    v_fixed := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_handle_here', plan_name => 'SQL_PLAN_xxxxx',
        attribute_name => 'FIXED', attribute_value => 'YES');
END;
/

-- SPM 配置
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = FALSE;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE;

备份与恢复 — RMAN / EXPDP / IMPDP / 归档

RMAN 备份

-- 连接 RMAN
-- rman target /

-- 全库备份（含归档日志）
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-- 增量备份 Level 0（基础）
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

-- 增量备份 Level 1（差异）
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- 表空间备份
RMAN> BACKUP TABLESPACE tbs_app_data;

-- 数据文件备份
RMAN> BACKUP DATAFILE '/u01/oradata/orcl/app_data01.dbf';

-- 控制文件/归档日志备份
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

RMAN 恢复

-- 全库恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

-- 时间点恢复（不完全恢复）
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE UNTIL TIME "TO_DATE('2024-08-15 14:00:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> RECOVER DATABASE UNTIL TIME "...";
RMAN> ALTER DATABASE OPEN RESETLOGS;

-- 表空间恢复
RMAN> SQL "ALTER TABLESPACE tbs_app_data OFFLINE IMMEDIATE";
RMAN> RESTORE TABLESPACE tbs_app_data;
RMAN> RECOVER TABLESPACE tbs_app_data;
RMAN> SQL "ALTER TABLESPACE tbs_app_data ONLINE";

-- 验证备份
RMAN> RESTORE DATABASE VALIDATE;

-- 备份策略配置
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

逻辑备份（EXPDP / IMPDP）

-- 导出全库
-- expdp system/password DIRECTORY=dp_dir DUMPFILE=full_export.dmp FULL=Y

-- 导出指定模式
-- expdp system/password DIRECTORY=dp_dir DUMPFILE=hr_export.dmp SCHEMAS=HR

-- 导出指定表
-- expdp hr/password DIRECTORY=dp_dir DUMPFILE=emp_export.dmp TABLES=employees,departments

-- 条件导出
-- expdp hr/password DIRECTORY=dp_dir DUMPFILE=emp_dept50.dmp TABLES=employees QUERY='employees:"WHERE department_id = 50"'

-- 并行导出
-- expdp hr/password DIRECTORY=dp_dir DUMPFILE=hr_%U.dmp SCHEMAS=HR PARALLEL=4

-- 全库导入
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=full_export.dmp FULL=Y

-- 导入并重映射表空间/模式
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=hr_export.dmp REMAP_SCHEMAS=HR:HR_NEW REMAP_TABLESPACE=USERS:TBS_APP_DATA

-- 跳过已存在对象
-- impdp hr/password DIRECTORY=dp_dir DUMPFILE=hr_export.dmp TABLE_EXISTS_ACTION=SKIP
-- TABLE_EXISTS_ACTION: SKIP / APPEND / TRUNCATE / REPLACE

归档日志模式

-- 查看当前日志模式
SELECT log_mode FROM v$database;

-- 启用归档日志模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 禁用归档日志模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

-- 归档日志管理
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/archivelog/orcl' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_format='orcl_%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SWITCH LOGFILE;

-- 查看归档日志
SELECT * FROM v$archived_log ORDER BY sequence#;
SELECT * FROM v$recovery_file_dest;
ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

Data Guard / RAC

Data Guard 物理备库

主库配置

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_host:1521/orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT='PRIMARY' SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER='STANDBY' SCOPE=BOTH;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u02/oradata/orcl/' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u02/oradata/orcl/' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

创建物理备库

-- 使用 RMAN DUPLICATE
-- DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

-- 备库启用实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

角色切换

-- Switchover（计划内切换，不丢数据）
-- 主库：
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SHUTDOWN IMMEDIATE; STARTUP MOUNT;
-- 备库：
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

-- Failover（故障切换）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

-- Active Data Guard（备库只读打开）
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

RAC（Real Application Clusters）

-- 查看 RAC 实例
SELECT instance_name, instance_number, host_name, status FROM gv$instance;

-- 查看 RAC 节点
SELECT * FROM gv$active_services;

-- 查看 ASM 磁盘组
SELECT * FROM gv$asm_diskgroup;

-- 全局等待事件
SELECT inst_id, event, COUNT(*) AS cnt, ROUND(AVG(wait_time_micro)) AS avg_wait_us
FROM gv$session WHERE wait_class != 'Idle'
GROUP BY inst_id, event ORDER BY cnt DESC;

-- RAC 序列配置（避免争用）
CREATE SEQUENCE seq_order_no START WITH 1 CACHE 1000 NOORDER;

-- RAC 关键初始化参数
-- cluster_database = TRUE
-- instance_number = 1
-- thread = 1
-- undo_tablespace = UNDOTBS1

安全与权限 — 用户 / FGA / VPD / TDE / 数据脱敏

用户 / 角色 / 权限

-- 创建用户
CREATE USER app_user IDENTIFIED BY "StrongPassword123!"
    DEFAULT TABLESPACE tbs_app_data
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON tbs_app_data;

-- 创建角色
CREATE ROLE app_read_role;
CREATE ROLE app_write_role;

-- 系统权限
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE VIEW, CREATE SEQUENCE TO app_user;

-- 对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_write_role;
GRANT SELECT ON hr.employees TO app_read_role;
GRANT EXECUTE ON hr.emp_mgmt TO app_admin_role;

-- 角色授予用户
GRANT app_read_role TO app_user;

-- 撤销
REVOKE DELETE ON hr.employees FROM app_write_role;

-- 查看用户权限
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';
SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';

-- 配置文件（Profile）
CREATE PROFILE app_profile LIMIT
    SESSIONS_PER_USER      5
    IDLE_TIME             30
    CONNECT_TIME         480
    FAILED_LOGIN_ATTEMPTS  5
    PASSWORD_LOCK_TIME     1
    PASSWORD_LIFE_TIME    90
    PASSWORD_GRACE_TIME    7;

-- 用户管理
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user PASSWORD EXPIRE;

FGA（Fine-Grained Auditing）— 细粒度审计

-- 创建 FGA 策略（审计高工资访问）
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'AUDIT_SALARY_ACCESS',
        audit_condition => 'salary > 10000',
        audit_column    => 'SALARY, COMMISSION_PCT',
        enable          => TRUE,
        statement_types => 'SELECT, UPDATE',
        audit_trail     => DBMS_FGA.XML + DBMS_FGA.EXTENDED
    );
END;
/

-- 查看 FGA 审计日志
SELECT timestamp, db_user, object_schema, object_name, sql_text
FROM dba_fga_audit_trail WHERE object_name = 'EMPLOYEES'
ORDER BY timestamp DESC;

-- 管理 FGA 策略
BEGIN
    DBMS_FGA.DISABLE_POLICY('HR', 'EMPLOYEES', 'AUDIT_SALARY_ACCESS');
    DBMS_FGA.ENABLE_POLICY('HR', 'EMPLOYEES', 'AUDIT_SALARY_ACCESS');
    DBMS_FGA.DROP_POLICY('HR', 'EMPLOYEES', 'AUDIT_SALARY_ACCESS');
END;
/

VPD（Virtual Private Database）— 虚拟私有数据库

-- 1. 创建策略函数
CREATE OR REPLACE FUNCTION dept_access_policy(
    p_schema VARCHAR2, p_object VARCHAR2
) RETURN VARCHAR2 AS
    v_dept_id NUMBER;
BEGIN
    IF SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE' THEN
        RETURN '1=1';
    END IF;
    v_dept_id := SYS_CONTEXT('USER_CTX', 'DEPARTMENT_ID');
    IF v_dept_id IS NOT NULL THEN
        RETURN 'department_id = ' || v_dept_id;
    ELSE
        RETURN '1=0';
    END IF;
END;
/

-- 2. 应用策略到表
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'DEPT_ACCESS_POLICY',
        function_schema => 'HR',
        policy_function => 'dept_access_policy',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
        update_check    => TRUE,
        enable          => TRUE
    );
END;
/

-- 查看 VPD 策略
SELECT * FROM dba_policies WHERE object_name = 'EMPLOYEES';

-- 移除
EXEC DBMS_RLS.DROP_POLICY('HR', 'EMPLOYEES', 'DEPT_ACCESS_POLICY');

TDE（Transparent Data Encryption）— 透明数据加密

-- 打开钱包
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password";
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wallet_password" WITH BACKUP;

-- 创建加密表空间
CREATE TABLESPACE tbs_encrypted
    DATAFILE '/u01/oradata/orcl/encrypted01.dbf' SIZE 5G
    ENCRYPTION USING 'AES256'
    DEFAULT STORAGE(ENCRYPT);

-- 列级加密
CREATE TABLE credit_cards (
    card_id     NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(10),
    card_number VARCHAR2(16) ENCRYPT USING 'AES256',
    cvv         VARCHAR2(4) ENCRYPT,
    expiry_date DATE
);

数据脱敏（Data Redaction）

-- 创建 Redaction Policy
BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema       => 'HR',
        object_name         => 'CREDIT_CARDS',
        policy_name         => 'REDACT_CC_NUM',
        column_name         => 'CARD_NUMBER',
        function_type       => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVVVVVVVVVVVVVV,VVVV-XXXX-XXXX-VVVV,*,1,4',
        expression          => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''APP_ADMIN'''
    );
END;
/

-- 查看脱敏策略
SELECT * FROM redaction_policies;
SELECT * FROM redaction_columns;

SQL 语法详解（Oracle 特有语法）

数据类型

DDL

-- CREATE TABLE 约束
CREATE TABLE users (
    user_id  NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50) NOT NULL,
    email    VARCHAR2(100) NOT NULL UNIQUE,
    status   VARCHAR2(10) DEFAULT 'ACTIVE',
    CONSTRAINT ck_users_status CHECK (status IN ('ACTIVE','INACTIVE','LOCKED'))
);

-- 虚拟列
CREATE TABLE products (
    product_id  NUMBER(10) PRIMARY KEY,
    unit_price  NUMBER(10,2),
    quantity    NUMBER(10),
    total_value NUMBER(10,2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
);

-- 序列
CREATE SEQUENCE seq_orders START WITH 10000 INCREMENT BY 1 CACHE 100 NOORDER;

-- 同义词
CREATE SYNONYM emp FOR hr.employees;
CREATE PUBLIC SYNONYM dept FOR hr.departments;

DML

-- MERGE（UPSERT）
MERGE INTO products p USING staging_products s ON (p.product_id = s.product_id)
WHEN MATCHED THEN UPDATE SET p.price = s.price, p.updated_at = SYSDATE
WHEN NOT MATCHED THEN INSERT (product_id, product_name, price) VALUES (s.product_id, s.product_name, s.price);

-- INSERT ALL（多表插入）
INSERT ALL
    WHEN salary > 10000 THEN INTO emp_active (employee_id, salary)
    WHEN salary <= 10000 THEN INTO emp_archive (employee_id, salary)
SELECT employee_id, salary FROM employees;

-- CONNECT BY（层次查询）
SELECT employee_id, last_name, LEVEL,
       SYS_CONNECT_BY_PATH(last_name, ' -> ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

-- 生成日期序列
SELECT DATE '2024-01-01' + LEVEL - 1 AS day FROM DUAL
CONNECT BY LEVEL <= 31;

-- FLASHBACK QUERY
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);
SELECT * FROM employees AS OF SCN 1234567;

SELECT 特有语法

-- WITH（CTE）
WITH dept_salary AS (
    SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id
)
SELECT * FROM dept_salary ORDER BY total_salary DESC;

-- 递归 CTE
WITH org_tree(employee_id, manager_id, last_name, lvl) AS (
    SELECT employee_id, manager_id, last_name, 1 FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.last_name, t.lvl + 1
    FROM employees e JOIN org_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM org_tree;

-- PIVOT（行转列）
SELECT * FROM (
    SELECT department_id, EXTRACT(MONTH FROM hire_date) AS hire_month FROM employees
) PIVOT (
    COUNT(*) FOR hire_month IN (1 AS JAN, 2 AS FEB, 3 AS MAR)
);

-- LISTAGG（列转字符串）
SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY hire_date) AS emp_list
FROM employees GROUP BY department_id;
-- 19c+ 支持超长处理
LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)

-- MODEL 子句
SELECT region, product, year, sales FROM sales_data
MODEL PARTITION BY (region) DIMENSION BY (product, year) MEASURES (sales)
RULES ( sales['TOTAL',2024] = sales['TOTAL',2023] * 1.1 );

-- MATCH_RECOGNIZE（12c+ 模式匹配）
SELECT * FROM stock_prices
MATCH_RECOGNIZE (
    PARTITION BY symbol ORDER BY trade_date
    MEASURES FIRST(price) AS start_price, LAST(price) AS end_price
    ONE ROW PER MATCH PATTERN (up{3,})
    DEFINE up AS price > PREV(price)
);

伪列

-- ROWNUM 正确用法
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 10;
-- 12c+ 推荐
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

-- ROWID 最快行定位
SELECT * FROM employees WHERE ROWID = 'AAAR3qAAEAAAACvAAA';

集合操作

-- UNION / UNION ALL / INTERSECT / MINUS
SELECT department_id FROM departments
MINUS
SELECT DISTINCT department_id FROM employees;  -- 没有员工的部门

Oracle 特有特性 — 分区 / 索引 / 物化视图 / Flashback / AQ

表空间与数据文件

-- 创建表空间
CREATE TABLESPACE tbs_app_data
    DATAFILE '/u01/oradata/orcl/app_data01.dbf' SIZE 10G
    AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

-- 创建临时/撤销表空间
CREATE TEMPORARY TABLESPACE tbs_app_temp TEMPFILE '/u01/oradata/orcl/app_temp01.dbf' SIZE 5G;
CREATE UNDO TABLESPACE tbs_app_undo DATAFILE '/u01/oradata/orcl/app_undo01.dbf' SIZE 10G;

-- 表空间管理
ALTER TABLESPACE tbs_app_data ADD DATAFILE '/u01/oradata/orcl/app_data02.dbf' SIZE 10G;
ALTER TABLESPACE tbs_app_data READ ONLY;
ALTER TABLESPACE tbs_app_data OFFLINE NORMAL;

-- 表空间使用率
SELECT df.tablespace_name,
       ROUND(df.bytes/1024/1024/1024,2) AS total_gb,
       ROUND((1-fs.bytes/df.bytes)*100,2) AS used_pct
FROM (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) df
JOIN (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name;

分区表

-- RANGE 分区
CREATE TABLE sales (
    sale_id NUMBER(10), sale_date DATE, amount NUMBER(10,2)
) PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (DATE '2023-04-01'),
    PARTITION p_2023_q2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- LIST 分区
CREATE TABLE customers PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('北京','天津','河北'),
    PARTITION p_east VALUES ('上海','江苏','浙江'),
    PARTITION p_other VALUES (DEFAULT)
);

-- HASH 分区
CREATE TABLE logs PARTITION BY HASH (log_id) PARTITIONS 8;

-- 复合分区（RANGE-HASH）
CREATE TABLE sales_comp PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (region) SUBPARTITIONS 4 (
    PARTITION p_2023_q1 VALUES LESS THAN (DATE '2023-04-01')
);

-- 间隔分区（11g+ 自动建分区）
CREATE TABLE sales_interval PARTITION BY RANGE (sale_date)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) (
    PARTITION p_first VALUES LESS THAN (DATE '2023-01-01')
);

-- 分区操作
ALTER TABLE sales ADD PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01');
ALTER TABLE sales TRUNCATE PARTITION p_future;
ALTER TABLE sales EXCHANGE PARTITION p_2023_q1 WITH TABLE sales_2023_q1;
ALTER TABLE sales MERGE PARTITIONS p_2023_q1, p_2023_q2 INTO PARTITION p_2023_h1;
ALTER TABLE sales SPLIT PARTITION p_future AT (DATE '2024-04-01')
    INTO (PARTITION p_2024_q1, PARTITION p_future);

索引

-- B-Tree 索引（默认）
CREATE INDEX idx_emp_dept_id ON employees(department_id);
CREATE INDEX idx_emp_dept_name ON employees(department_id, last_name);  -- 复合
CREATE UNIQUE INDEX idx_emp_email ON employees(email);                 -- 唯一

-- 位图索引（适合低基数、数据仓库）
CREATE BITMAP INDEX idx_sales_region ON sales(region);

-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- 域索引（Oracle Text 全文）
CREATE INDEX idx_docs_content ON documents(content) INDEXTYPE IS CTXSYS.CONTEXT;

-- 索引监控
ALTER INDEX idx_emp_dept_id MONITORING USAGE;
SELECT * FROM v$object_usage;  -- 查看未被使用的索引

物化视图

-- 基本物化视图
CREATE MATERIALIZED VIEW mv_dept_salary_summary
    BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE
AS SELECT d.department_id, d.department_name,
          COUNT(e.employee_id) AS emp_count, SUM(e.salary) AS total_salary
FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 快速刷新物化视图（需物化视图日志）
CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY, ROWID (salary, department_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_emp_fast
    BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT department_id, COUNT(*) AS cnt, SUM(salary) AS total_sal
FROM employees GROUP BY department_id;

-- 刷新
EXEC DBMS_MVIEW.REFRESH('mv_dept_salary_summary', 'C');  -- C=完全, F=快速
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Flashback 技术

-- Flashback Query — 见 references/09-sql-syntax.md

-- Flashback Table（需启用行移动）
ALTER TABLE employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);
FLASHBACK TABLE employees TO SCN 1234567;
FLASHBACK TABLE employees TO RESTORE POINT before_batch;

-- Flashback Drop（回收站）
DROP TABLE employees;
SELECT object_name, original_name, droptime FROM recyclebin;
FLASHBACK TABLE employees TO BEFORE DROP;

-- Flashback Database（需启用闪回日志）
-- FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

AQ（Advanced Queuing）— 高级队列

-- 创建类型和队列表
CREATE OR REPLACE TYPE order_msg AS OBJECT (order_id NUMBER, customer_id NUMBER, amount NUMBER);
/
BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'order_queue_table', queue_payload_type => 'order_msg');
    DBMS_AQADM.CREATE_QUEUE(queue_name => 'order_queue', queue_table => 'order_queue_table');
    DBMS_AQADM.START_QUEUE(queue_name => 'order_queue');
END;
/

-- 发送消息
DECLARE
    enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
    message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_handle     RAW(16);
    msg                order_msg := order_msg(1001, 500, 1500.00);
BEGIN
    DBMS_AQ.ENQUEUE('order_queue', enqueue_options, message_properties, msg, message_handle);
    COMMIT;
END;
/

-- 接收消息
DECLARE
    dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
    message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_handle     RAW(16);
    msg                order_msg;
BEGIN
    dequeue_options.wait := DBMS_AQ.FOREVER;
    DBMS_AQ.DEQUEUE('order_queue', dequeue_options, message_properties, msg, message_handle);
    COMMIT;
END;
/