Article

关系型数据库Oracle

更新于：2026-07-16

第一章：Oracle 基础入门

1.1 Oracle 数据库简介

概念名称	说明	注意事项
实例（Instance）	Oracle 实例由内存结构（SGA）和后台进程组成，用于管理数据库访问	一个实例通常对应一个数据库，RAC 环境下多个实例可共享一个数据库
数据库（Database）	物理存储的数据集合，包括数据文件、控制文件、重做日志文件等	数据库文件必须与实例配合才能被访问
SGA（System Global Area）	共享内存区，包含缓冲区缓存、共享池、重做日志缓冲区等	大小影响性能，需合理配置
PGA（Program Global Area）	每个服务器进程私有的内存区域，用于排序、哈希等操作	由参数 PGA_AGGREGATE_TARGET 控制总量
表空间（Tablespace）	逻辑存储单元，由一个或多个数据文件组成	用户对象（如表）必须创建在某个表空间中
方案（Schema）	与数据库用户关联的对象集合（如表、视图、过程等）	用户名即方案名，但二者概念不同

1.2 安装与配置 Oracle 数据库

步骤名称	操作细节	注意事项
系统环境检查	检查操作系统版本、内核参数（如 shmmax、sem）、磁盘空间、用户组（oinstall / dba）	Oracle 对 Linux 内核参数有严格要求，需提前调整
创建 oracle 用户	使用 root 执行：`useradd -g oinstall -G dba oracle`	必须属于 dba 组才能管理数据库
设置环境变量	在 `~/.bash_profile` 中设置 ORACLE_HOME、ORACLE_SID、PATH	ORACLE_SID 是实例标识，区分大小写
运行安装程序	启动图形化安装：`./runInstaller`（需 X11 转发）或静默安装	静默安装需提前准备 response 文件
执行 root.sh	安装完成后以 root 身份运行 `$ORACLE_HOME/root.sh`	必须执行，否则监听器和实例无法正常启动
创建监听器	使用 Net Configuration Assistant（netca）或手动配置 listener.ora	监听器默认端口 1521，需确保防火墙开放

1.3 启动与关闭数据库实例

方法名称	语法	用途	代码示例	注意事项
STARTUP NOMOUNT	`STARTUP NOMOUNT;`	启动实例，不加载控制文件	`STARTUP NOMOUNT;`	用于创建新数据库或恢复控制文件
STARTUP MOUNT	`STARTUP MOUNT;`	启动实例并加载控制文件，不打开数据库	`STARTUP MOUNT;`	用于备份、恢复、重命名数据文件等维护操作
STARTUP OPEN	`STARTUP;` 或 `STARTUP OPEN;`	完整启动数据库，允许用户连接	`STARTUP;`	默认行为，等价于 STARTUP OPEN
SHUTDOWN NORMAL	`SHUTDOWN;` 或 `SHUTDOWN NORMAL;`	等待所有会话主动断开后关闭	`SHUTDOWN;`	可能长时间等待，生产环境慎用
SHUTDOWN IMMEDIATE	`SHUTDOWN IMMEDIATE;`	回滚未提交事务，强制断开会话	`SHUTDOWN IMMEDIATE;`	最常用的安全关闭方式
SHUTDOWN TRANSACTIONAL	`SHUTDOWN TRANSACTIONAL;`	等待当前事务结束，不再接受新连接	`SHUTDOWN TRANSACTIONAL;`	较少使用
SHUTDOWN ABORT	`SHUTDOWN ABORT;`	立即终止实例，类似”断电”	`SHUTDOWN ABORT;`	下次启动需进行实例恢复，仅用于紧急情况

注：以上命令需在 SQL*Plus 中以具有 SYSDBA 权限的用户（如 sys）执行。

1.4 连接数据库（SQL*Plus / SQLcl / tnsping）

SQL*Plus 连接方式

方法名称	语法	用途	代码示例	注意事项
本地操作系统认证	`sqlplus / as sysdba`	无需密码，通过 OS 用户组认证	`sqlplus / as sysdba`	仅限本地 oracle 用户，需配置 sqlnet.ora（SQLNET.AUTHENTICATION_SERVICES=NTS 或 NONE）
TNS 别名连接	`sqlplus username/password@tns_alias`	通过 tnsnames.ora 中定义的别名连接	`sqlplus scott/tiger@orcl`	需确保 tnsnames.ora 配置正确且监听器运行
EZConnect 连接	`sqlplus username/password@//host:port/service_name`	无需 tnsnames.ora 的简易连接	`sqlplus scott/tiger@//localhost:1521/ORCLCDB`	service_name 区分大小写，通常为 CDB 或 PDB 名称
连接普通用户	`sqlplus username/password`	连接本地默认实例（依赖 ORACLE_SID）	`sqlplus hr/hr`	仅在单实例且 ORACLE_SID 已设时有效

SQLcl 连接（现代替代工具）

方法名称	语法	用途	代码示例	注意事项
基本连接	`sql hr/hr@localhost:1521/ORCLPDB1`	支持彩色输出、自动补全	`sql scott/tiger@//dbhost:1521/XEPDB1`	需 Java 环境，从 Oracle 官网下载
使用钱包连接	`sql /@mywallet`	通过 Oracle Wallet 安全连接	`sql /@prod_db`	需提前配置 wallet

tnsping 工具（网络连通性测试）

方法名称	语法	用途	代码示例	注意事项
测试 TNS 解析	`tnsping tns_alias`	验证 tnsnames.ora 配置是否可达	`tnsping orcl`	仅测试网络层和监听器响应，不验证用户名/密码
测试服务名	`tnsping //host:port/service`	测试 EZConnect 格式	`tnsping //192.168.1.10:1521/ORCLCDB`	需 Oracle Client 12c+ 支持

注意：

所有连接工具均依赖 Oracle Client 或 Instant Client。

若连接失败，依次排查：监听器状态（lsnrctl status）、防火墙、tnsnames.ora、服务名是否正确。

在多租户架构（CDB/PDB）中，连接 PDB 需使用 PDB 的服务名（如 ORCLPDB1），而非 CDB 名。

第二章：SQL 语言基础

2.1 DDL（数据定义语言）

方法名称	语法	用途	代码示例	注意事项
CREATE TABLE	`CREATE TABLE table_name (col1 datatype [CONSTRAINT], ...);`	创建新表	`CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(50));`	表名和列名需符合命名规则；默认在用户默认表空间创建
ALTER TABLE ADD	`ALTER TABLE table_name ADD (col datatype);`	添加新列	`ALTER TABLE employees ADD (email VARCHAR2(100));`	新列对已有行默认为 NULL（除非指定 DEFAULT NOT NULL）
ALTER TABLE MODIFY	`ALTER TABLE table_name MODIFY (col new_datatype);`	修改列定义	`ALTER TABLE employees MODIFY (name VARCHAR2(100));`	缩小长度或更改类型可能失败（如有数据不兼容）
ALTER TABLE DROP COLUMN	`ALTER TABLE table_name DROP COLUMN col_name;`	删除列	`ALTER TABLE employees DROP COLUMN email;`	不可逆操作；Oracle 12c+ 支持 SET UNUSED 后异步删除
DROP TABLE	`DROP TABLE table_name [CASCADE CONSTRAINTS];`	删除整张表	`DROP TABLE employees CASCADE CONSTRAINTS;`	默认放入回收站（RECYCLEBIN），加 PURGE 可彻底删除
TRUNCATE TABLE	`TRUNCATE TABLE table_name;`	快速清空表数据	`TRUNCATE TABLE employees;`	不可回滚，不触发触发器，重置高水位线
RENAME	`RENAME old_name TO new_name;`	重命名表	`RENAME employees TO staff;`	仅改名，不影响数据或权限
COMMENT ON	`COMMENT ON COLUMN table.col IS 'comment';`	为列添加注释	`COMMENT ON COLUMN employees.name IS 'Full name of employee';`	注释存储在 USER_COL_COMMENTS 视图中

注： DDL 语句自动提交事务，无法回滚。

2.2 DML（数据操作语言）

方法名称	语法	用途	代码示例	注意事项
INSERT INTO	`INSERT INTO table VALUES (vals);`	插入单行数据	`INSERT INTO employees (id, name) VALUES (1, 'Alice');`	列数与值数必须匹配；可省略列名（按表定义顺序）
INSERT ALL	`INSERT ALL INTO t1 VALUES (...) INTO t2 VALUES (...) SELECT * FROM dual;`	一次插入多表	`INSERT ALL INTO emp_log VALUES (1, 'A') INTO audit_log VALUES (SYSDATE) SELECT * FROM dual;`	需以 SELECT 结尾（通常用 dual）
UPDATE	`UPDATE table SET col = val WHERE condition;`	更新符合条件的行	`UPDATE employees SET name = 'Bob' WHERE id = 1;`	无 WHERE 子句将更新全表，慎用
DELETE	`DELETE FROM table WHERE condition;`	删除符合条件的行	`DELETE FROM employees WHERE id = 1;`	无 WHERE 子句将删除全表数据，但可回滚
MERGE	`MERGE INTO target USING source ON (cond) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...;`	合并插入/更新（upsert）	`MERGE INTO emp_target t USING emp_source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);`	常用于 ETL 场景；条件需确保唯一匹配

注： DML 操作不会自动提交，需显式 COMMIT 或 ROLLBACK。

2.3 DCL（数据控制语言）

方法名称	语法	用途	代码示例	注意事项
GRANT	`GRANT privilege [, ...] ON object TO user/role [WITH GRANT OPTION];`	授予权限	`GRANT SELECT, INSERT ON employees TO hr_user;`	对象权限需指定对象；系统权限（如 CREATE SESSION）无需 ON
REVOKE	`REVOKE privilege [, ...] ON object FROM user/role;`	回收权限	`REVOKE INSERT ON employees FROM hr_user;`	回收后用户立即失去该权限
GRANT ROLE	`GRANT role TO user;`	授予角色	`GRANT CONNECT, RESOURCE TO app_user;`	CONNECT 和 RESOURCE 是传统角色，12c+ 建议使用最小权限原则
CREATE ROLE	`CREATE ROLE role_name;`	创建自定义角色	`CREATE ROLE data_reader;`	可集中管理权限，便于分配

常见权限：

系统权限：CREATE SESSION, CREATE TABLE, CREATE VIEW
对象权限：SELECT, INSERT, UPDATE, DELETE, EXECUTE

注： WITH GRANT OPTION 允许被授权者再授权，存在安全风险。

2.4 TCL（事务控制语言）

方法名称	语法	用途	代码示例	注意事项
COMMIT	`COMMIT [WORK];`	提交当前事务	`COMMIT;`	永久保存 DML 更改；释放行锁
ROLLBACK	`ROLLBACK [WORK];`	回滚整个事务	`ROLLBACK;`	撤销所有未提交的 DML 操作
SAVEPOINT	`SAVEPOINT sp_name;`	设置保存点	`SAVEPOINT before_update;`	可配合 ROLLBACK TO 使用
ROLLBACK TO	`ROLLBACK TO SAVEPOINT sp_name;`	回滚到指定保存点	`ROLLBACK TO before_update;`	保存点之后的操作被撤销，之前的操作仍保留
SET TRANSACTION	`SET TRANSACTION [READ ONLY \| READ WRITE] [NAME 'name'];`	设置事务属性	`SET TRANSACTION READ ONLY NAME 'report_tx';`	必须在事务第一条语句前执行

注： DDL 语句（如 CREATE、DROP）会隐式 COMMIT 当前事务。

2.5 查询语句（SELECT 与高级查询）

方法名称	语法	用途	代码示例	注意事项
基本 SELECT	`SELECT cols FROM table [WHERE cond] [ORDER BY cols];`	查询数据	`SELECT id, name FROM employees WHERE id > 10 ORDER BY name;`	WHERE 过滤行，ORDER BY 排序结果
DISTINCT	`SELECT DISTINCT col FROM table;`	去重查询	`SELECT DISTINCT department_id FROM employees;`	对所有选定列组合去重
聚合函数	`SELECT COUNT(*), SUM(sal), AVG(sal) FROM table;`	统计计算	`SELECT AVG(salary) FROM employees;`	忽略 NULL 值；COUNT(*) 计所有行
GROUP BY	`SELECT dept, AVG(sal) FROM emp GROUP BY dept;`	分组聚合	`SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;`	SELECT 中非聚合列必须出现在 GROUP BY 中
HAVING	`SELECT dept, AVG(sal) FROM emp GROUP BY dept HAVING AVG(sal) > 5000;`	过滤分组结果	`SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;`	不能用 WHERE 过滤聚合结果
JOIN	`SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id = d.id;`	多表连接	`SELECT e.name, j.title FROM employees e INNER JOIN jobs j ON e.job_id = j.id;`	支持 INNER、LEFT OUTER、RIGHT OUTER、FULL OUTER
子查询	`SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = 'IT');`	嵌套查询	`SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);`	可用于 WHERE、FROM、SELECT 子句
分页查询（12c+）	`SELECT * FROM emp ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;`	限制返回行数	`SELECT * FROM employees ORDER BY hire_date FETCH FIRST 10 ROWS ONLY;`	12c 前需用 ROWNUM 伪列实现
CASE 表达式	`SELECT name, CASE WHEN sal > 10000 THEN 'High' ELSE 'Low' END AS level FROM emp;`	条件逻辑	`SELECT name, CASE department_id WHEN 10 THEN 'Admin' WHEN 20 THEN 'Dev' ELSE 'Other' END FROM employees;`	支持简单 CASE 和搜索 CASE 两种形式

注意：

Oracle 中字符串用单引号 '，双引号用于标识符（如列别名）。

日期字面量推荐使用 DATE '2025-01-01' 或 TO_DATE('2025-01-01', 'YYYY-MM-DD')。

NULL 与任何值比较结果均为 UNKNOWN，需用 IS NULL 判断。

第三章：数据库对象管理

3.1 表（Table）

方法名称	语法	用途	代码示例	注意事项
创建普通表	`CREATE TABLE table_name (col datatype [DEFAULT expr] [CONSTRAINT], ...);`	定义结构化数据存储	`CREATE TABLE products (id NUMBER, name VARCHAR2(100) NOT NULL, price NUMBER(10,2));`	表名在方案内必须唯一；默认在用户默认表空间创建
创建临时表	`CREATE GLOBAL TEMPORARY TABLE table_name (...) ON COMMIT [PRESERVE \| DELETE] ROWS;`	存储会话或事务级临时数据	`CREATE GLOBAL TEMPORARY TABLE temp_log (msg VARCHAR2(200)) ON COMMIT DELETE ROWS;`	数据仅对当前会话可见；ON COMMIT DELETE 表示事务结束清空
指定表空间	`CREATE TABLE ... TABLESPACE ts_name;`	将表创建在指定表空间	`CREATE TABLE logs (id NUMBER) TABLESPACE users;`	需确保用户有该表空间配额（QUOTA）
复制表结构	`CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=0;`	仅复制结构（无数据）	`CREATE TABLE emp_copy AS SELECT * FROM employees WHERE 1=0;`	不复制约束、索引、触发器等
复制表+数据	`CREATE TABLE new_table AS SELECT * FROM old_table;`	结构+数据一起复制	`CREATE TABLE emp_backup AS SELECT * FROM employees;`	主键、外键等不会被复制
查看表结构	`DESCRIBE table_name;` 或查询 USER_TAB_COLUMNS	获取列定义	`DESCRIBE employees;`	SQL*Plus/SQLcl 支持 DESC 命令
删除表	`DROP TABLE table_name [PURGE];`	移除表及其数据	`DROP TABLE temp_data PURGE;`	不加 PURGE 会进入回收站（可通过 FLASHBACK 恢复）

注意：

表名最大长度 30 字节（12c 之前），12.2+ 支持 128 字节。

修改表结构（如增加主键）需单独使用 ALTER TABLE ADD CONSTRAINT。

3.2 索引（Index）

方法名称	语法	用途	代码示例	注意事项
创建 B-Tree 索引	`CREATE INDEX idx_name ON table(col);`	加速等值或范围查询	`CREATE INDEX idx_emp_name ON employees(last_name);`	最常用索引类型；对高选择性列有效
创建唯一索引	`CREATE UNIQUE INDEX idx_name ON table(col);`	强制列值唯一	`CREATE UNIQUE INDEX idx_emp_email ON employees(email);`	可替代唯一约束；NULL 值不参与唯一性检查
创建复合索引	`CREATE INDEX idx_name ON table(col1, col2);`	支持多列查询优化	`CREATE INDEX idx_emp_dept_name ON employees(department_id, last_name);`	列顺序影响查询效率（最左前缀原则）
创建位图索引	`CREATE BITMAP INDEX idx_name ON table(low_card_col);`	适用于低基数列（如性别、状态）	`CREATE BITMAP INDEX idx_emp_status ON employees(emp_status);`	仅适用于数据仓库；OLTP 中并发 DML 性能差
创建函数索引	`CREATE INDEX idx_name ON table(function(col));`	对表达式或函数结果建索引	`CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));`	查询时必须使用相同函数才能命中索引
重建索引	`ALTER INDEX idx_name REBUILD [TABLESPACE ts];`	优化索引碎片	`ALTER INDEX idx_emp_name REBUILD;`	可在线重建（加 ONLINE 关键字）
删除索引	`DROP INDEX idx_name;`	移除索引	`DROP INDEX idx_emp_name;`	索引删除不影响表数据

注意：

索引会降低 INSERT/UPDATE/DELETE 性能（需维护索引结构）。

查询是否使用索引可通过 EXPLAIN PLAN 查看执行计划。

3.3 视图（View）

方法名称	语法	用途	代码示例	注意事项
创建简单视图	`CREATE VIEW view_name AS SELECT cols FROM table;`	封装查询逻辑，简化访问	`CREATE VIEW emp_view AS SELECT id, name, dept_id FROM employees;`	默认不可更新（若满足条件可更新）
创建只读视图	`CREATE VIEW view_name AS SELECT ... WITH READ ONLY;`	禁止通过视图修改数据	`CREATE VIEW emp_ro AS SELECT * FROM employees WITH READ ONLY;`	显式防止 DML 操作
创建带检查选项视图	`CREATE VIEW view_name AS SELECT ... WHERE cond WITH CHECK OPTION;`	插入/更新时强制满足 WHERE 条件	`CREATE VIEW high_salary_emp AS SELECT * FROM employees WHERE salary > 10000 WITH CHECK OPTION;`	若插入 salary=5000 会报错
替换视图	`CREATE OR REPLACE VIEW view_name AS ...;`	修改现有视图定义	`CREATE OR REPLACE VIEW emp_view AS SELECT id, UPPER(name) AS name FROM employees;`	无需先 DROP，保留权限
删除视图	`DROP VIEW view_name;`	移除视图定义	`DROP VIEW emp_view;`	不影响基表数据
查看视图定义	`SELECT text FROM USER_VIEWS WHERE view_name = 'EMP_VIEW';`	获取视图 SQL	`SELECT text FROM USER_VIEWS WHERE view_name = 'EMP_VIEW';`	TEXT 字段为 LONG 类型，部分工具可能截断

注意：

视图不存储数据（物化视图除外），每次查询都执行底层 SQL。

聚合、DISTINCT、GROUP BY 等视图通常不可更新。

3.4 序列（Sequence）

方法名称	语法	用途	代码示例	注意事项
创建序列	`CREATE SEQUENCE seq_name [START WITH n] [INCREMENT BY n] [MAXVALUE n \| NOMAXVALUE] [MINVALUE n \| NOMINVALUE] [CYCLE \| NOCYCLE] [CACHE n \| NOCACHE];`	生成唯一数字	`CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;`	常用于主键自增
获取下一个值	`seq_name.NEXTVAL`	返回并递增序列值	`INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'John');`	每次调用递增一次，即使事务回滚也不回退
获取当前值	`seq_name.CURRVAL`	返回当前序列值（需先调用 NEXTVAL）	`SELECT emp_seq.CURRVAL FROM dual;`	会话中未调用 NEXTVAL 时使用 CURRVAL 报错
修改序列	`ALTER SEQUENCE seq_name [INCREMENT BY n] [MAXVALUE ...];`	调整序列参数	`ALTER SEQUENCE emp_seq INCREMENT BY 2;`	不能修改 START WITH
删除序列	`DROP SEQUENCE seq_name;`	移除序列	`DROP SEQUENCE emp_seq;`	不影响已使用该序列的表数据
查看序列信息	`SELECT * FROM USER_SEQUENCES WHERE sequence_name = 'EMP_SEQ';`	查询序列属性	`SELECT last_number, cache_size FROM USER_SEQUENCES WHERE sequence_name = 'EMP_SEQ';`	LAST_NUMBER 是已分配的最大值（非当前值）

注意：

CACHE 提升性能但可能产生”跳号”（实例崩溃时丢失缓存值）。

Oracle 12c+ 支持表级 IDENTITY 列，可替代序列。

3.5 同义词（Synonym）

方法名称	语法	用途	代码示例	注意事项
创建私有同义词	`CREATE SYNONYM syn_name FOR [schema.]object;`	为对象创建别名（仅当前用户可用）	`CREATE SYNONYM emp FOR hr.employees;`	用户可直接 `SELECT * FROM emp;`
创建公共同义词	`CREATE PUBLIC SYNONYM syn_name FOR [schema.]object;`	全局可用的别名（需 DBA 权限）	`CREATE PUBLIC SYNONYM global_log FOR app.logs;`	所有用户均可访问（需有对象权限）
删除同义词	`DROP SYNONYM syn_name;` 或 `DROP PUBLIC SYNONYM syn_name;`	移除同义词	`DROP SYNONYM emp;`	不影响基对象
查看同义词	`SELECT * FROM USER_SYNONYMS;` 或 `ALL_SYNONYMS;`	查询同义词定义	`SELECT synonym_name, table_owner, table_name FROM USER_SYNONYMS;`	PUBLIC 同义词在 ALL_SYNONYMS 中可见
使用同义词	`SELECT * FROM syn_name;`	透明访问目标对象	`SELECT COUNT(*) FROM emp;`	同义词可指向表、视图、序列、过程等

注意：

同义词不提供安全控制，仅简化命名。

若基对象被删除，同义词变为”失效”，查询时报错。

3.6 约束（Constraint）

方法名称	语法	用途	代码示例	注意事项
主键约束	`CONSTRAINT pk_name PRIMARY KEY (col)`	唯一标识行，隐式 NOT NULL + 唯一索引	`CREATE TABLE t (id NUMBER CONSTRAINT t_pk PRIMARY KEY);`	一张表只能有一个主键
唯一约束	`CONSTRAINT uk_name UNIQUE (col)`	列值唯一（允许多个 NULL）	`CREATE TABLE t (email VARCHAR2(100) CONSTRAINT t_uk UNIQUE);`	自动创建唯一索引
非空约束	`col datatype NOT NULL`	禁止列为空	`CREATE TABLE t (name VARCHAR2(50) NOT NULL);`	无命名（系统自动生成）
检查约束	`CONSTRAINT ck_name CHECK (condition)`	限制列值范围	`CREATE TABLE t (age NUMBER CONSTRAINT t_ck CHECK (age BETWEEN 0 AND 150));`	条件中不能含子查询或 SYSDATE
外键约束	`CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(pk_col) [ON DELETE CASCADE \| SET NULL]`	维护引用完整性	`CREATE TABLE orders (cust_id NUMBER, CONSTRAINT o_fk FOREIGN KEY (cust_id) REFERENCES customers(id) ON DELETE CASCADE);`	父表必须有主键/唯一约束
添加约束	`ALTER TABLE table ADD CONSTRAINT name type (col);`	表创建后增加约束	`ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (id);`	需确保现有数据满足约束条件
禁用约束	`ALTER TABLE table DISABLE CONSTRAINT name [CASCADE];`	临时关闭约束检查	`ALTER TABLE orders DISABLE CONSTRAINT o_fk;`	CASCADE 用于外键依赖
启用约束	`ALTER TABLE table ENABLE CONSTRAINT name;`	重新启用约束	`ALTER TABLE orders ENABLE CONSTRAINT o_fk;`	启用时验证所有数据
删除约束	`ALTER TABLE table DROP CONSTRAINT name;`	移除约束	`ALTER TABLE employees DROP CONSTRAINT emp_pk;`	主键/唯一约束的索引默认一并删除（加 KEEP INDEX 保留）

注意：

约束状态可通过 USER_CONSTRAINTS 视图查询。

外键列建议创建索引，避免父表 DML 锁等待。

第四章：用户与权限管理

4.1 用户（User）创建与管理

方法名称	语法	用途	代码示例	注意事项
创建用户	`CREATE USER username IDENTIFIED BY password;`	新建数据库用户	`CREATE USER app_user IDENTIFIED BY SecurePass123;`	用户名必须唯一；密码需符合复杂度策略（若启用）
指定默认表空间	`CREATE USER ... DEFAULT TABLESPACE ts_name;`	设置用户对象默认存储位置	`CREATE USER app_user IDENTIFIED BY pass DEFAULT TABLESPACE users;`	若未指定，默认为 DATABASE_DEFAULT（通常为 USERS）
指定临时表空间	`CREATE USER ... TEMPORARY TABLESPACE temp_ts;`	设置排序/哈希操作临时空间	`CREATE USER app_user IDENTIFIED BY pass TEMPORARY TABLESPACE temp;`	必须是临时表空间类型
修改用户密码	`ALTER USER username IDENTIFIED BY new_password;`	更改用户密码	`ALTER USER app_user IDENTIFIED BY NewPass456;`	需具有 ALTER USER 权限
锁定用户	`ALTER USER username ACCOUNT LOCK;`	禁止用户登录	`ALTER USER app_user ACCOUNT LOCK;`	常用于禁用离职员工账号
解锁用户	`ALTER USER username ACCOUNT UNLOCK;`	恢复用户登录	`ALTER USER app_user ACCOUNT UNLOCK;`	默认新建用户为解锁状态
删除用户	`DROP USER username [CASCADE];`	移除用户及其所有对象	`DROP USER app_user CASCADE;`	不加 CASCADE 无法删除非空用户；操作不可逆
查看用户信息	`SELECT * FROM DBA_USERS WHERE username = 'APP_USER';`	查询用户属性	`SELECT username, account_status, default_tablespace FROM DBA_USERS;`	需 DBA 权限；普通用户可查 USER_USERS

注意：

新建用户默认无任何权限（包括 CREATE SESSION），必须显式授权才能连接。

用户名在 Oracle 中不区分大小写（除非用双引号创建）。

4.2 角色（Role）管理

方法名称	语法	用途	代码示例	注意事项
创建角色	`CREATE ROLE role_name;`	定义权限集合	`CREATE ROLE data_reader;`	角色名全局唯一
创建带密码角色	`CREATE ROLE role_name IDENTIFIED BY password;`	需密码激活的角色	`CREATE ROLE secure_role IDENTIFIED BY RolePass789;`	使用 `SET ROLE role_name IDENTIFIED BY ...` 激活
授予角色给用户	`GRANT role_name TO user_name;`	批量分配权限	`GRANT data_reader TO app_user;`	用户自动获得角色内所有权限
撤销角色	`REVOKE role_name FROM user_name;`	移除角色权限	`REVOKE data_reader FROM app_user;`	用户立即失去相关权限
删除角色	`DROP ROLE role_name;`	移除角色定义	`DROP ROLE data_reader;`	不影响已授权用户的历史操作，但新会话不再拥有该角色
设置默认角色	`ALTER USER user_name DEFAULT ROLE role1, role2;`	控制用户登录时自动启用的角色	`ALTER USER app_user DEFAULT ROLE data_reader, CONNECT;`	非默认角色需手动 SET ROLE 启用
查看角色信息	`SELECT * FROM DBA_ROLES; SELECT * FROM DBA_ROLE_PRIVS;`	查询角色及分配情况	`SELECT granted_role FROM DBA_ROLE_PRIVS WHERE grantee = 'APP_USER';`	DBA_ROLES 列出所有角色；DBA_ROLE_PRIVS 列出用户/角色拥有的角色

注意：

预定义角色如 CONNECT、RESOURCE 在早期版本包含较多权限，12c+ 已精简。

应用中建议使用自定义角色实现最小权限原则。

4.3 权限（Privilege）授予与回收

方法名称	语法	用途	代码示例	注意事项
授予系统权限	`GRANT system_priv TO user/role;`	赋予全局能力	`GRANT CREATE SESSION, CREATE TABLE TO app_user;`	系统权限不依赖具体对象（如 CREATE VIEW）
授予对象权限	`GRANT object_priv ON [schema.]object TO user/role;`	赋予对特定对象的操作权	`GRANT SELECT, INSERT ON hr.employees TO app_user;`	对象权限包括 SELECT、INSERT、UPDATE、DELETE、EXECUTE、INDEX 等
带传递授权	`GRANT ... TO ... WITH GRANT OPTION;`	允许被授权者再授权（对象权限）	`GRANT SELECT ON emp TO user1 WITH GRANT OPTION;`	仅适用于对象权限；慎用以防权限扩散
带管理授权	`GRANT ... TO ... WITH ADMIN OPTION;`	允许被授权者再授权（系统权限/角色）	`GRANT CREATE TABLE TO user1 WITH ADMIN OPTION;`	可转授系统权限或角色
回收系统权限	`REVOKE system_priv FROM user/role;`	撤销全局能力	`REVOKE CREATE TABLE FROM app_user;`	不级联回收（WITH ADMIN OPTION 授出的权限不受影响）
回收对象权限	`REVOKE object_priv ON object FROM user/role;`	撤销对象操作权	`REVOKE INSERT ON hr.employees FROM app_user;`	级联回收：若 A→B→C，回收 A→B 则 C 也失去权限
查看权限	`SELECT * FROM DBA_SYS_PRIVS; SELECT * FROM DBA_TAB_PRIVS;`	查询权限分配	`SELECT privilege FROM DBA_TAB_PRIVS WHERE table_name = 'EMPLOYEES';`	DBA_SYS_PRIVS：系统权限；DBA_TAB_PRIVS：对象权限

注意：

回收权限可能导致依赖对象失效（如视图、过程）。

PUBLIC 是特殊”用户”，GRANT TO PUBLIC 表示所有用户可用（高风险）。

4.4 默认表空间与临时表空间配置

方法名称	语法	用途	代码示例	注意事项
创建永久表空间	`CREATE TABLESPACE ts_name DATAFILE 'path' SIZE size;`	存储用户表、索引等	`CREATE TABLESPACE app_data DATAFILE '/u01/oradata/app_data01.dbf' SIZE 1G;`	数据文件路径需 Oracle 有读写权限
创建临时表空间	`CREATE TEMPORARY TABLESPACE temp_ts TEMPFILE 'path' SIZE size;`	存储排序、哈希等临时数据	`CREATE TEMPORARY TABLESPACE app_temp TEMPFILE '/u01/oradata/app_temp01.dbf' SIZE 500M;`	必须指定 TEMPFILE
设置数据库默认表空间	`ALTER DATABASE DEFAULT TABLESPACE ts_name;`	新建用户默认使用此表空间	`ALTER DATABASE DEFAULT TABLESPACE app_data;`	影响后续 CREATE USER 语句
设置数据库默认临时表空间	`ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts;`	新建用户默认临时表空间	`ALTER DATABASE DEFAULT TEMPORARY TABLESPACE app_temp;`	强烈建议设置专用临时表空间
修改用户默认表空间	`ALTER USER username DEFAULT TABLESPACE ts_name;`	更改用户对象存储位置	`ALTER USER app_user DEFAULT TABLESPACE app_data;`	不移动已有对象，仅影响新创建对象
修改用户临时表空间	`ALTER USER username TEMPORARY TABLESPACE temp_ts;`	更改用户临时操作空间	`ALTER USER app_user TEMPORARY TABLESPACE app_temp;`	立即生效
设置表空间配额	`ALTER USER username QUOTA size ON ts_name;`	限制用户在表空间的使用量	`ALTER USER app_user QUOTA 500M ON app_data;`	可设 UNLIMITED；无配额则无法创建对象
查看表空间信息	`SELECT * FROM DBA_TABLESPACES; SELECT * FROM DBA_DATA_FILES;`	查询表空间配置	`SELECT tablespace_name, contents FROM DBA_TABLESPACES;`	CONTENTS 列标识 PERMANENT 或 TEMPORARY

注意：

USERS 和 TEMP 是安装时默认的永久/临时表空间，生产环境应避免直接使用。

用户必须在其默认表空间有配额（QUOTA），否则 CREATE TABLE 会失败（ORA-01536）。

第五章：PL/SQL 编程基础

5.1 PL/SQL 结构与语法

概念名称	说明	注意事项
块结构（Block）	PL/SQL 程序基本单元，由 DECLARE、BEGIN、EXCEPTION、END 组成	DECLARE 和 EXCEPTION 可选；匿名块无名称，命名块可存储在数据库中
匿名块	无名称的 PL/SQL 块，通常用于脚本或测试	不能被其他程序直接调用；每次执行需重新编译
命名块	存储过程、函数、包、触发器等，持久化在数据库中	可被应用程序或其他 PL/SQL 调用；支持重载（仅限包内）
编译与执行	PL/SQL 块在服务器端编译为字节码后执行	语法错误在编译时报出（如 PLS-00103），运行时错误在执行时报出（如 ORA-01403）
分号与换行	每条语句以分号 `;` 结尾；SQL 语句在 PL/SQL 中可直接嵌入	不支持 GO；多行字符串可用 `q'{...}'` 语法

示例匿名块：

DECLARE
  v_msg VARCHAR2(100) := 'Hello PL/SQL';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_msg);
END;

5.2 变量与数据类型

方法名称	语法	用途	代码示例	注意事项
声明变量	`var_name datatype [NOT NULL] [:= default_value];`	定义局部变量	`v_count NUMBER := 0;`	必须在 DECLARE 部分声明
声明常量	`const_name CONSTANT datatype := value;`	定义不可变值	`c_pi CONSTANT NUMBER := 3.14159;`	必须初始化，且不能修改
%TYPE 属性	`var_name table.col%TYPE;`	声明与表列同类型的变量	`v_name employees.last_name%TYPE;`	自动匹配列的数据类型和长度
%ROWTYPE 属性	`rec_name table%ROWTYPE;`	声明与整行结构匹配的记录	`v_emp employees%ROWTYPE;`	可通过 v_emp.last_name 访问字段
内置数据类型	NUMBER, VARCHAR2, DATE, BOOLEAN, CLOB, BLOB 等	存储不同类别数据	`v_flag BOOLEAN := TRUE;`	BOOLEAN 仅 PL/SQL 支持，不能用于表列
赋值	`var := expression;`	给变量赋值	`v_total := v_price * v_qty;`	使用 `:=`，非 `=`
打印输出	`DBMS_OUTPUT.PUT_LINE(msg);`	调试输出	`DBMS_OUTPUT.PUT_LINE('Count: ' \|\| v_count);`	需先在 SQL*Plus 中执行 `SET SERVEROUTPUT ON`

注意：

VARCHAR2 最大 32767 字节（PL/SQL 中），表列最大 4000（12c 前）或 32767（启用扩展）。

未初始化的变量值为 NULL，参与运算结果也为 NULL。

5.3 控制结构（IF、LOOP、CASE）

方法名称	语法	用途	代码示例	注意事项
IF-THEN	`IF condition THEN statements END IF;`	单分支条件	`IF v_score >= 60 THEN pass := TRUE; END IF;`	条件为布尔表达式
IF-THEN-ELSE	`IF cond THEN s1 ELSE s2 END IF;`	双分支	`IF v_age < 18 THEN msg := 'Minor'; ELSE msg := 'Adult'; END IF;`	—
IF-ELSIF-ELSE	`IF c1 THEN s1 ELSIF c2 THEN s2 ELSE s3 END IF;`	多分支	`IF grade = 'A' THEN gpa := 4.0; ELSIF grade = 'B' THEN gpa := 3.0; END IF;`	注意拼写是 ELSIF，非 ELSEIF
简单 LOOP	`LOOP statements EXIT WHEN condition; END LOOP;`	无限循环，手动退出	`LOOP v_i := v_i + 1; EXIT WHEN v_i > 10; END LOOP;`	必须有 EXIT，否则死循环
WHILE LOOP	`WHILE condition LOOP statements END LOOP;`	条件为真时循环	`WHILE v_i <= 10 LOOP v_sum := v_sum + v_i; v_i := v_i + 1; END LOOP;`	条件在每次循环前检查
FOR LOOP	`FOR i IN [REVERSE] low..high LOOP statements END LOOP;`	固定次数循环	`FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;`	i 自动声明，无需提前定义；范围 inclusive
CASE 表达式	`CASE selector WHEN val THEN res [ELSE def] END;`	多值选择（表达式）	`v_level := CASE v_score WHEN 100 THEN 'Perfect' WHEN 90 THEN 'Great' ELSE 'OK' END;`	用于赋值或 SELECT
CASE 语句	`CASE selector WHEN val THEN stmts [ELSE stmts] END CASE;`	多值选择（语句块）	`CASE v_dept WHEN 10 THEN v_loc := 'NY'; WHEN 20 THEN v_loc := 'LA'; END CASE;`	必须以 END CASE; 结尾

注意：

所有控制结构必须完整配对（如 IF-END IF）。

FOR 循环变量为只读，不可在循环体内修改。

5.4 游标（Cursor）

方法名称	语法	用途	代码示例	注意事项
显式游标声明	`CURSOR cur_name IS select_stmt;`	定义查询结果集	`CURSOR emp_cur IS SELECT id, name FROM employees WHERE dept_id = 10;`	在 DECLARE 部分定义
打开游标	`OPEN cur_name;`	执行查询并定位结果集	`OPEN emp_cur;`	仅对显式游标需要
获取行	`FETCH cur_name INTO var_list;`	从结果集取一行	`FETCH emp_cur INTO v_id, v_name;`	需预先声明接收变量
关闭游标	`CLOSE cur_name;`	释放游标资源	`CLOSE emp_cur;`	必须显式关闭（否则可能内存泄漏）
游标属性	`cur%FOUND, cur%NOTFOUND, cur%ISOPEN, cur%ROWCOUNT`	获取游标状态	`IF emp_cur%NOTFOUND THEN EXIT; END IF;`	%ROWCOUNT 返回已取行数
FOR 循环游标	`FOR rec IN (SELECT ...) LOOP ... END LOOP;`	自动打开/取/关闭游标	`FOR r IN (SELECT name FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(r.name); END LOOP;`	最简洁方式，推荐使用
带参数游标	`CURSOR cur(p_dept NUMBER) IS SELECT ... WHERE dept_id = p_dept;`	动态过滤结果	`CURSOR emp_cur(p_d NUMBER) IS SELECT name FROM employees WHERE dept_id = p_d;`	调用时传参：`OPEN emp_cur(10);`
隐式游标	直接在 DML 后使用 SQL% 属性	获取最近 DML 影响行数	`UPDATE emp SET sal = sal * 1.1 WHERE dept = 10; IF SQL%ROWCOUNT > 0 THEN ...`	SQL%FOUND / SQL%NOTFOUND / SQL%ROWCOUNT / SQL%ISOPEN

注意：

显式游标适用于复杂逻辑；简单遍历优先用 FOR 循环游标。

游标不支持”回滚”到前一行，只能顺序读取。

5.5 异常处理（Exception）

方法名称	语法	用途	代码示例	注意事项
预定义异常	`WHEN exception_name THEN handler;`	捕获常见错误	`WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Not found');`	如 TOO_MANY_ROWS, DUP_VAL_ON_INDEX, ZERO_DIVIDE
自定义异常	`DECLARE my_ex EXCEPTION; RAISE my_ex;`	定义业务异常	`DECLARE invalid_age EXCEPTION; BEGIN IF age < 0 THEN RAISE invalid_age; END IF; EXCEPTION WHEN invalid_age THEN ...`	需在 DECLARE 中声明
RAISE_APPLICATION_ERROR	`RAISE_APPLICATION_ERROR(-20001, 'msg');`	抛出自定义错误码（-20000 到 -20999）	`IF v_id IS NULL THEN RAISE_APPLICATION_ERROR(-20001, 'ID required'); END IF;`	可在过程/函数中终止并返回错误
异常传播	未捕获的异常向上传递	子块未处理则抛给父块	`BEGIN ... BEGIN ... RAISE; END; -- 若未处理，外层可捕获`	最外层未捕获则报错回滚
获取错误信息	`SQLCODE, SQLERRM`	获取当前错误码和消息	`DBMS_OUTPUT.PUT_LINE('Err: ' \|\| SQLCODE \|\| ' - ' \|\| SQLERRM);`	在 EXCEPTION 块中使用
OTHERS 通配	`WHEN OTHERS THEN handler;`	捕获所有未指定异常	`WHEN OTHERS THEN log_error(SQLERRM); RAISE;`	建议记录后重新 RAISE，避免静默失败

常见预定义异常：

NO_DATA_FOUND（SELECT INTO 无结果）
TOO_MANY_ROWS（SELECT INTO 多行）
DUP_VAL_ON_INDEX（唯一约束冲突）
INVALID_NUMBER（转换失败）

5.6 存储过程与函数

方法名称	语法	用途	代码示例	注意事项
创建过程	`CREATE [OR REPLACE] PROCEDURE proc_name (param mode type) IS ... BEGIN ... END;`	执行操作，无返回值	`CREATE PROCEDURE raise_salary(p_emp_id NUMBER, p_pct NUMBER) IS BEGIN UPDATE emp SET sal = sal * (1 + p_pct/100) WHERE id = p_emp_id; END;`	参数模式：IN（默认）、OUT、IN OUT
创建函数	`CREATE [OR REPLACE] FUNCTION func_name (param type) RETURN type IS ... BEGIN ... RETURN expr; END;`	计算并返回单值	`CREATE FUNCTION get_emp_name(p_id NUMBER) RETURN VARCHAR2 IS v_name VARCHAR2(100); BEGIN SELECT name INTO v_name FROM emp WHERE id = p_id; RETURN v_name; END;`	函数必须有 RETURN 语句；不能执行事务控制（如 COMMIT）
调用过程	`EXEC proc_name(args);` 或 `CALL proc_name(args);`	执行过程	`EXEC raise_salary(101, 10);`	在 SQL*Plus/SQLcl 中可用 EXEC；PL/SQL 中直接写 proc_name(…)
调用函数	`SELECT func(args) FROM dual;` 或 `var := func(args);`	获取函数结果	`SELECT get_emp_name(101) FROM dual;`	函数可在 SQL 语句中使用（需满足 purity rules）
删除过程/函数	`DROP PROCEDURE proc_name; DROP FUNCTION func_name;`	移除子程序	`DROP PROCEDURE raise_salary;`	依赖对象（如包）会失效
查看源码	`SELECT text FROM USER_SOURCE WHERE name = 'PROC_NAME' ORDER BY line;`	调试或审计	`SELECT text FROM USER_SOURCE WHERE name = 'GET_EMP_NAME' ORDER BY line;`	TEXT 为 LONG 类型，部分工具需特殊处理

注意：

过程可执行 DML 并 COMMIT/ROLLBACK；函数在 SQL 中调用时不能修改数据库状态。

推荐将相关过程/函数组织到 PACKAGE 中。

5.7 触发器（Trigger）

方法名称	语法	用途	代码示例	注意事项
行级触发器	`CREATE [OR REPLACE] TRIGGER trig_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table FOR EACH ROW [WHEN (cond)] DECLARE ... BEGIN ... END;`	对每行操作触发	`CREATE TRIGGER emp_audit BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO emp_log VALUES (:OLD.id, :NEW.salary, SYSDATE); END;`	可用 `:OLD`（更新前）和 `:NEW`（更新后）引用列值
语句级触发器	`CREATE TRIGGER ... BEFORE/AFTER ... ON table DECLARE ... BEGIN ... END;`	对整个 DML 语句触发一次	`CREATE TRIGGER log_trunc AFTER TRUNCATE ON SCHEMA BEGIN INSERT INTO ddl_log VALUES ('TRUNCATE', SYSDATE); END;`	无 :OLD/:NEW；常用于审计
INSTEAD OF 触发器	`CREATE TRIGGER ... INSTEAD OF INSERT/UPDATE/DELETE ON view ...`	替代对视图的 DML 操作	`CREATE TRIGGER io_emp INSTEAD OF INSERT ON emp_view FOR EACH ROW BEGIN INSERT INTO emp_base VALUES (:NEW.id, :NEW.name); END;`	仅用于视图，实现可更新视图
系统触发器	`CREATE TRIGGER ... AFTER LOGON ON DATABASE \| SCHEMA`	响应数据库事件	`CREATE TRIGGER set_client_info AFTER LOGON ON SCHEMA BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO(USER); END;`	事件包括 LOGON、LOGOFF、STARTUP、SHUTDOWN、DDL 等
禁用触发器	`ALTER TRIGGER trig_name DISABLE;`	临时停用	`ALTER TRIGGER emp_audit DISABLE;`	可单独禁用，不影响表
启用触发器	`ALTER TRIGGER trig_name ENABLE;`	重新启用	`ALTER TRIGGER emp_audit ENABLE;`	—
删除触发器	`DROP TRIGGER trig_name;`	移除触发器	`DROP TRIGGER emp_audit;`	不影响基表

注意：

触发器中不能 COMMIT/ROLLBACK（自治事务除外）。

避免在触发器中编写复杂逻辑，以免影响 DML 性能。

递归触发需谨慎（如触发器内更新自身表）。

第六章：事务与并发控制

6.1 事务概念与 ACID 特性

概念名称	说明	注意事项
事务（Transaction）	一组逻辑上不可分割的 DML 操作，作为一个整体提交或回滚	由第一条 DML 开始，到 COMMIT/ROLLBACK 或会话结束终止
原子性（Atomicity）	事务内所有操作要么全部成功，要么全部失败	Oracle 通过 UNDO 日志实现回滚
一致性（Consistency）	事务使数据库从一个有效状态转移到另一个有效状态（满足约束）	由应用逻辑和数据库约束共同保证
隔离性（Isolation）	并发事务之间互不干扰	Oracle 默认提供”读一致性”，非 ANSI 标准的 READ COMMITTED 级别
持久性（Durability）	提交后的数据永久保存，即使系统崩溃	通过 REDO 日志确保，由 LGWR 进程写入磁盘
自动提交（Autocommit）	某些工具（如 SQL Developer）默认每条 DML 后自动 COMMIT	在脚本或程序中应显式控制事务边界
事务标识	每个事务有唯一 XID（事务 ID），可通过 `V$TRANSACTION` 查看	应用可通过 DBMS_TRANSACTION.LOCAL_TRANSACTION_ID 获取

注意：

DDL 语句（如 CREATE、DROP）会隐式 COMMIT 当前事务。

事务不跨会话；每个会话独立维护其事务上下文。

6.2 锁机制（Lock）

锁类型	说明	兼容性	获取时机	注意事项
行级锁（Row-Level Lock）	锁定被修改的单行（TX 锁）	多个事务可同时持有不同行的锁	执行 UPDATE/DELETE/SELECT FOR UPDATE 时自动获取	由事务持有，COMMIT/ROLLBACK 释放；不阻塞 SELECT
表级锁（TM 锁）	保护表结构，防止 DDL 干扰 DML	多种模式（如 RS、RX、S、X）	DML 操作时自动申请（如 RX 模式）	通常短暂持有；长时间持有可能因外键未索引导致
共享锁（Share Lock, S）	允许多个会话读取，禁止修改	与其他 S 锁兼容，与 X 锁冲突	手动执行 `LOCK TABLE t IN SHARE MODE`	很少使用，可能引发死锁
排他锁（Exclusive Lock, X）	禁止其他任何会话读写	仅与 NULL 锁兼容	手动执行 `LOCK TABLE t IN EXCLUSIVE MODE`	严重影响并发，慎用
DDL 锁（DDL Lock）	保护对象结构（如防止 ALTER TABLE 时被查询）	分为共享（保护对象被引用）、排他（正在修改）	执行 DDL 时自动获取	通常短暂；长时间持有表示有活跃会话依赖该对象
手动加锁	`SELECT ... FOR UPDATE [OF cols] [NOWAIT \| WAIT n]`	显式锁定查询结果行	`SELECT id FROM emp WHERE dept = 10 FOR UPDATE NOWAIT;`	NOWAIT 立即报错（ORA-00054）；WAIT n 最多等待 n 秒

查看锁信息：

SELECT sid, type, id1, id2, lmode, request FROM v$lock WHERE block > 0;
SELECT * FROM dba_blockers;  -- 阻塞者
SELECT * FROM dba_waiters;   -- 等待者

注意：

Oracle 不支持”脏读”，即使未提交的数据也无法被其他会话读取。

外键列若无索引，子表 DML 可能导致父表全表 TM 锁，严重降低并发。

6.3 隔离级别与一致性读

概念名称	说明	语法/设置	注意事项
默认隔离级别	READ COMMITTED（读已提交）	无需设置，默认行为	每条查询看到的是”语句开始时刻”已提交的数据快照
串行化隔离	SERIALIZABLE	`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`	事务内所有查询看到的是”事务开始时刻”的一致快照；可能报 ORA-08177（无法序列化访问）
一致性读（Consistent Read）	利用 UNDO 构造过去时间点的数据镜像	自动启用，无需干预	保证查询结果不被并发修改干扰；不阻塞写操作
Flashback Query	查询历史时刻的数据	`SELECT * FROM table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);`	依赖 UNDO_RETENTION 参数；需足够 UNDO 空间
读写冲突处理	写操作阻塞其他写，但不阻塞读	自动处理	读操作永不等待写锁，始终返回一致快照
设置事务只读	`SET TRANSACTION READ ONLY;`	`BEGIN; SET TRANSACTION READ ONLY; SELECT ...; COMMIT;`	适用于报表类长查询，避免 ORA-1555（快照太旧）

注意：

Oracle 不支持 READ UNCOMMITTED 和 REPEATABLE READ（ANSI 标准中的中间级别）。

SERIALIZABLE 模式下，若检测到写冲突（如更新已被其他事务修改的行），会抛出 ORA-08177。

ORA-01555 “snapshot too old” 通常因 UNDO 空间不足或查询过长导致。

6.4 死锁检测与处理

操作名称	说明	检测/处理方式	注意事项
死锁定义	两个或多个事务互相等待对方释放锁，形成循环等待	Oracle 自动检测	例如：T1 锁 A 等 B，T2 锁 B 等 A
自动检测机制	后台进程定期检查锁等待图（wait-for graph）	一旦发现死锁，立即终止其中一个事务	被终止的事务收到 ORA-00060 错误
报错信息	ORA-00060: deadlock detected while waiting for resource	包含 trace 文件路径（如 alert log 中）	trace 文件记录死锁涉及的 SQL、会话、锁类型
回滚策略	Oracle 选择”代价最小”的事务回滚（通常是修改较少者）	自动 ROLLBACK 被选中事务	应用需捕获 ORA-00060 并重试事务
预防措施	按固定顺序访问表和行；减少事务粒度；避免用户交互在事务中	应用层设计规范	例如：总是先更新 EMP 表再更新 DEPT 表
手动诊断	查询 `V$LOCK`、`V$SESSION_WAIT`、`DBA_BLOCKERS`	`SELECT s1.username \|\| ' blocks ' \|\| s2.username FROM v$lock l1, v$lock l2, v$session s1, v$session s2 WHERE ...`	trace 文件路径在 alert log 中

注意：

死锁是应用程序逻辑问题，非数据库缺陷。

即使使用 SELECT FOR UPDATE，若访问顺序不一致，仍可能死锁。

生产环境中应记录 ORA-00060 并告警，便于分析根因。

第七章：备份与恢复

7.1 逻辑备份（expdp / impdp）

操作名称	语法	用途	代码示例	注意事项
创建目录对象	`CREATE DIRECTORY dir_name AS '/path';`	定义操作系统路径映射	`CREATE DIRECTORY dp_dir AS '/u01/backup';`	需 DBA 权限；路径必须存在且 Oracle 有读写权限
授权目录访问	`GRANT READ, WRITE ON DIRECTORY dir_name TO user;`	允许用户使用目录	`GRANT READ, WRITE ON DIRECTORY dp_dir TO system;`	expdp/impdp 运行用户需此权限
全库导出	`expdp user/password FULL=Y DIRECTORY=dir DUMPFILE=file.dmp LOGFILE=log.log`	备份整个数据库	`expdp system/manager FULL=Y DIRECTORY=dp_dir DUMPFILE=full_2025.dmp LOGFILE=full.log`	仅 SYSDBA 或具有 EXP_FULL_DATABASE 角色可执行
按用户导出	`expdp ... SCHEMAS=scott,hr ...`	导出指定用户所有对象	`expdp system/manager SCHEMAS=hr DIRECTORY=dp_dir DUMPFILE=hr.dmp`	默认包含数据+元数据
按表导出	`expdp ... TABLES=emp,dept ...`	导出指定表	`expdp scott/tiger TABLES=employees DIRECTORY=dp_dir DUMPFILE=emp.dmp`	可加 QUERY 参数过滤数据
导入全库	`impdp ... FULL=Y ...`	恢复整个数据库	`impdp system/manager FULL=Y DIRECTORY=dp_dir DUMPFILE=full_2025.dmp`	目标库需兼容版本；可能覆盖现有对象
按用户导入	`impdp ... SCHEMAS=hr REMAP_SCHEMA=hr:hr_new ...`	导入到同名或新用户	`impdp system/manager SCHEMAS=hr DIRECTORY=dp_dir DUMPFILE=hr.dmp REMAP_SCHEMA=hr:hr_test`	REMAP_SCHEMA 可重定向方案
表级导入	`impdp ... TABLES=emp ... TABLE_EXISTS_ACTION=REPLACE`	控制同名表处理方式	`impdp scott/tiger TABLES=employees DIRECTORY=dp_dir DUMPFILE=emp.dmp TABLE_EXISTS_ACTION=TRUNCATE`	可选 SKIP, APPEND, TRUNCATE, REPLACE
并行导出/导入	`PARALLEL=n`	提升 I/O 性能	`expdp ... PARALLEL=4 DUMPFILE=part_%U.dmp`	%U 生成多个文件（如 part_01.dmp）；需 Enterprise Edition
查看作业状态	`expdp/impdp ... ATTACH=job_name`	连接运行中的作业	`expdp system ATTACH=SYS_EXPORT_FULL_01`	可暂停（STOP_JOB）、继续（START_JOB）、终止（KILL_JOB）

注意：

expdp/impdp 是服务器端工具，DUMPFILE 路径在数据库服务器上。

不支持跨大版本直接导入（如 19c → 11g），需用低版本客户端导出。

逻辑备份不能替代物理备份，无法用于块级损坏恢复。

7.2 物理备份（RMAN）

操作名称	语法	用途	代码示例	注意事项
启动 RMAN	`rman TARGET /`	以操作系统认证连接	`rman TARGET sys/oracle@orcl`	需配置 ORACLE_SID 或使用 TNS
全量备份	`BACKUP DATABASE;`	备份所有数据文件+控制文件+SPFILE	`RMAN> BACKUP DATABASE PLUS ARCHIVELOG;`	PLUS ARCHIVELOG 自动切换并备份归档日志
增量备份（0 级）	`BACKUP INCREMENTAL LEVEL 0 DATABASE;`	基准全量备份	`RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;`	后续 1 级增量基于此
增量备份（1 级）	`BACKUP INCREMENTAL LEVEL 1 DATABASE;`	备份自上次 0/1 级后变更块	`RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;`	CUMULATIVE 表示基于最近 0 级
归档日志备份	`BACKUP ARCHIVELOG ALL;`	备份所有归档日志	`RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1';`	可配合 DELETE INPUT 自动删除已备日志
配置保留策略	`CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;`	定义备份保留时间	`RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;`	REDUNDANCY 表示保留最近 2 份全备
交叉检查	`CROSSCHECK BACKUP;`	验证备份集是否存在	`RMAN> CROSSCHECK ARCHIVELOG ALL;`	标记 EXPIRED 状态的备份需 DELETE EXPIRED 清除
删除过期备份	`DELETE OBSOLETE;`	自动清理超出保留策略的备份	`RMAN> DELETE NOPROMPT EXPIRED BACKUP;`	NOPROMPT 跳过确认
恢复数据库	`RESTORE DATABASE; RECOVER DATABASE;`	从备份还原并应用日志	`RMAN> RUN { SET UNTIL TIME "TO_DATE('2025-01-01','YYYY-MM-DD')"; RESTORE DATABASE; RECOVER DATABASE; }`	需先 STARTUP MOUNT
验证备份	`VALIDATE BACKUPSET n;`	检查备份集完整性	`RMAN> RESTORE DATABASE VALIDATE;`	不实际还原，仅校验

注意：

RMAN 备份必须启用归档模式（ARCHIVELOG）。

控制文件自动备份默认开启（CONFIGURE CONTROLFILE AUTOBACKUP ON）。

RMAN 备份存储在 Flash Recovery Area（FRA）或指定路径。

7.3 闪回技术（Flashback）

技术名称	语法/操作	用途	代码示例	注意事项
闪回查询	`SELECT * FROM table AS OF TIMESTAMP ...`	查询历史时刻数据	`SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);`	依赖 UNDO_RETENTION；可能报 ORA-01555
闪回版本查询	`SELECT versions_starttime, versions_endtime, ... FROM table VERSIONS BETWEEN ...`	查看行的历史变更记录	`SELECT versions_starttime, salary FROM employees VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE id = 101;`	显示每行的多个版本
闪回事务查询	`SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE xid = '...';`	分析事务修改细节	`SELECT operation, undo_sql FROM FLASHBACK_TRANSACTION_QUERY WHERE table_name = 'EMPLOYEES';`	需开启 supplemental logging
闪回表	`FLASHBACK TABLE table TO TIMESTAMP ...;`	将表恢复到过去状态	`FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2025-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');`	表需启用行移动（ALTER TABLE t ENABLE ROW MOVEMENT）
闪回删除	`FLASHBACK TABLE table TO BEFORE DROP [RENAME TO new_name];`	恢复被 DROP 的表	`FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_old;`	从回收站（RECYCLEBIN）恢复；仅限非 PURGE 删除
闪回数据库	`FLASHBACK DATABASE TO TIMESTAMP ...;`	将整个数据库回退到过去	`SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 1/24); ALTER DATABASE OPEN RESETLOGS;`	需提前启用闪回（ALTER DATABASE FLASHBACK ON）；FRA 必须足够大

注意：

闪回数据库要求数据库处于 MOUNT 状态，且必须 RESETLOGS 打开。

闪回表/删除不适用于 SYS 用户对象。

闪回功能依赖 UNDO（查询类）或 FRA（数据库级）。

7.4 归档模式与非归档模式

模式名称	说明	切换命令	用途	注意事项
非归档模式（NOARCHIVELOG）	重做日志循环覆盖，不保存历史变更	默认安装模式	仅支持全库冷备；实例崩溃可恢复，但介质损坏导致数据丢失	无法进行时间点恢复；生产环境禁用
归档模式（ARCHIVELOG）	重做日志填满后归档，保留所有变更	`SHUTDOWN IMMEDIATE;` `STARTUP MOUNT;` `ALTER DATABASE ARCHIVELOG;` `ALTER DATABASE OPEN;`	支持热备、时间点恢复、Data Guard、GoldenGate	必须为生产系统启用；需监控归档空间
查看当前模式	`SELECT log_mode FROM v$database;`	—	确认数据库运行模式	NOARCHIVELOG 或 ARCHIVELOG
手动切换日志	`ALTER SYSTEM SWITCH LOGFILE;`	强制归档当前日志	用于测试或立即归档	仅在 ARCHIVELOG 模式有效
设置归档路径	`ALTER SYSTEM SET log_archive_dest_1='LOCATION=/arch';`	指定归档存储位置	可配置多个目的地（dest_1, dest_2…）	路径需有足够空间；建议使用 FRA（db_recovery_file_dest）
归档日志视图	`SELECT name, first_time, next_time FROM v$archived_log;`	查询已生成归档日志	可结合 RMAN 备份管理	归档日志是 PITR（时间点恢复）的关键

注意：

切换 ARCHIVELOG 模式需重启数据库到 MOUNT 状态。

归档空间不足会导致数据库挂起（HANG），需设置告警或自动清理策略。

即使启用 ARCHIVELOG，若未定期备份归档日志，仍可能因磁盘满导致故障。

第八章：性能调优基础

8.1 执行计划（EXPLAIN PLAN）

方法名称	语法	用途	代码示例	注意事项
生成执行计划	`EXPLAIN PLAN FOR sql_statement;`	将 SQL 的执行计划存入 PLAN_TABLE	`EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;`	不实际执行 SQL，仅解析计划
查看执行计划	`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);`	格式化显示最近 EXPLAIN PLAN 结果	`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);`	默认读取 PLAN_TABLE 中最新记录
指定语句 ID	`EXPLAIN PLAN SET STATEMENT_ID = 'id' FOR ...;`	为计划指定唯一标识	`EXPLAIN PLAN SET STATEMENT_ID = 'emp_q1' FOR SELECT * FROM emp WHERE id = 100;`	查询时需指定：DBMS_XPLAN.DISPLAY(NULL, ‘emp_q1’)
实时执行计划	`SELECT /+ GATHER_PLAN_STATISTICS / ... FROM ...;` 然后 `DBMS_XPLAN.DISPLAY_CURSOR(..., NULL, 'ALLSTATS LAST');`	获取实际执行统计（含逻辑/物理读）	`SELECT /+ GATHER_PLAN_STATISTICS / name FROM employees WHERE salary > 10000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));`	需在 SQL 中加提示；显示 A-Rows（实际行数） vs E-Rows（估算行数）
计划表初始化	`@?/rdbms/admin/utlxplan.sql`	创建 PLAN_TABLE（若不存在）	在 SQL*Plus 中运行脚本	通常已存在；若缺失需手动创建
清除计划表	`DELETE FROM plan_table WHERE statement_id = '...';`	清理旧计划记录	`DELETE FROM plan_table;`	避免 PLAN_TABLE 过大影响查询性能

关键列说明（DBMS_XPLAN 输出）：

Id: 操作步骤编号
Operation: 访问方法（如 TABLE ACCESS FULL、INDEX RANGE SCAN）
Cost: 优化器估算的资源消耗（相对值）
Cardinality (E-Rows): 估算返回行数
Bytes: 估算数据量
A-Rows（实际行）: 仅在 GATHER_PLAN_STATISTICS 下可见

8.2 SQL 跟踪与 TKPROF

操作名称	语法/步骤	用途	代码示例	注意事项
开启会话级跟踪	`ALTER SESSION SET sql_trace = TRUE;` 或 `EXEC DBMS_SESSION.SESSION_TRACE_ENABLE;`	记录当前会话所有 SQL 的执行细节	`ALTER SESSION SET sql_trace = TRUE; SELECT * FROM emp; ALTER SESSION SET sql_trace = FALSE;`	生成 .trc 文件于 USER_DUMP_DEST 目录
开启带绑定变量跟踪	`EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);`	同时记录等待事件和绑定变量值	`EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(binds=>TRUE);`	绑定变量值对性能分析至关重要
查找 trace 文件路径	`SELECT value FROM v$diag_info WHERE name = 'Default Trace File';`	定位当前会话的 trace 文件	—	11g+ 使用 Automatic Diagnostic Repository（ADR）
转换 trace 为可读报告	`tkprof input.trc output.txt [sort=option]`	将原始 trace 文件格式化为易读报告	`tkprof ora_12345.trc report.txt sort=exeela`	sort 参数可按执行时间（exeela）、CPU（execpu）等排序
TKPROF 报告关键指标	—	分析 SQL 性能瓶颈	—	关注：count（执行次数）、cpu、elapsed（总耗时）、disk（物理读）、query（逻辑读）
关闭跟踪	`ALTER SESSION SET sql_trace = FALSE;` 或 `EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;`	停止生成 trace	—	避免长时间开启导致磁盘占满

注意：

生产环境慎用全会话跟踪，建议用 ORADEBUG 或 DBMS_MONITOR 针对特定会话。

TKPROF 不显示 PL/SQL 行级性能，需结合 PL/SQL hierarchical profiler。

8.3 AWR 与 ADDM 报告

报告类型	生成方式	用途	代码示例	注意事项
AWR 快照	自动每小时采集（默认保留 8 天）	记录数据库性能历史（等待事件、SQL、资源使用）	手动创建：`EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;`	需 Diagnostics Pack 许可（企业版功能）
生成 AWR 报告	`@?/rdbms/admin/awrrpt.sql`	分析指定时间段性能趋势	在 SQL*Plus 中运行脚本，输入 begin_snap / end_snap	输出 HTML 或文本格式；关注 Top 5 Timed Events
生成 ADDM 报告	`@?/rdbms/admin/addmrpt.sql`	自动诊断性能问题并提供建议	输入 begin_snap / end_snap	基于 AWR 快照；直接给出”Finding”和”Recommendation”
查看快照列表	`SELECT snap_id, begin_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC;`	确定分析时间段	—	快照 ID 用于生成报告
修改快照设置	`EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>60247, interval=>30);`	调整保留天数和采集间隔	retention 单位为分钟；interval 最小 10 分钟	过短间隔增加 overhead
清除旧快照	`EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id, high_snap_id);`	释放 SYSAUX 表空间	—	避免 SYSAUX 过度膨胀

AWR 报告关键部分：

DB Time: 用户请求消耗的总数据库时间
Top 5 Timed Events: 主要等待事件（如 db file sequential read、log file sync）
SQL ordered by Elapsed Time: 耗时最长的 SQL
Instance Efficiency Percentages: 缓冲区命中率等

ADDM 典型建议：

“Undersized SGA” → 增大 SGA
“Top SQL using excessive CPU” → 优化 SQL 或加索引
“Hard Parse” → 使用绑定变量

8.4 索引优化与统计信息收集

操作名称	语法	用途	代码示例	注意事项
收集表统计信息	`EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE');`	更新优化器所需元数据（行数、列分布等）	`EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');`	默认 ESTIMATE_PERCENT=AUTO_SAMPLE_SIZE
收集 Schema 统计	`EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA');`	批量收集用户下所有对象统计	`EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');`	可设 degree 并行加速
收集数据库统计	`EXEC DBMS_STATS.GATHER_DATABASE_STATS;`	全库统计收集（通常夜间作业）	—	影响较大，避免业务高峰
锁定统计信息	`EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA', 'TABLE');`	防止自动任务覆盖手工统计	—	适用于静态表或测试环境
删除统计信息	`EXEC DBMS_STATS.DELETE_TABLE_STATS('SCHEMA', 'TABLE');`	移除现有统计（回退到动态采样）	—	谨慎操作，可能导致执行计划恶化
查看统计信息	`SELECT num_rows, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';`	验证统计是否最新	—	LAST_ANALYZED 应接近当前时间
创建缺失索引	`CREATE INDEX idx ON table(col) [TABLESPACE ts];`	优化高成本查询	`CREATE INDEX idx_emp_dept ON employees(department_id);`	优先为 WHERE、JOIN、ORDER BY 列建索引
重建碎片化索引	`ALTER INDEX idx_name REBUILD ONLINE;`	降低索引高度，提升扫描效率	`ALTER INDEX idx_emp_name REBUILD ONLINE;`	ONLINE 允许 DML 并发；非 ONLINE 会锁表
监控未使用索引	`ALTER INDEX idx_name MONITORING USAGE;` 后查 `V$OBJECT_USAGE`	识别可删除的冗余索引	`ALTER INDEX idx_old MONITORING USAGE;` — `SELECT used FROM v$object_usage WHERE index_name = 'IDX_OLD';`	MONITORING 需持续一段时间（如一周）

优化建议：

高选择性列（如主键、唯一值多）适合 B-Tree 索引
低基数列（如状态、性别）考虑位图索引（仅数据仓库）
函数/表达式查询需建函数索引（如 UPPER(name)）
组合索引遵循最左前缀原则，高频过滤列放前
统计信息过期是执行计划错误的最常见原因

第九章：命令行工具详解

9.1 SQL*Plus 命令速查

命令名称	语法	用途	代码示例	注意事项
连接数据库	`sqlplus [username[/password][@tns]]`	启动并连接会话	`sqlplus scott/tiger@orcl` / `sqlplus / as sysdba`	本地 OS 认证需配置 sqlnet.ora；sysdba 需 oracle 用户组
断开连接	`DISCONNECT`	关闭当前数据库连接，保留 SQL*Plus 会话	`DISCONNECT`	可重新 CONNECT
退出	`EXIT` 或 `QUIT`	退出 SQL*Plus	`EXIT`	自动 COMMIT 或 ROLLBACK 取决于设置
执行脚本	`@script.sql` 或 `START script.sql`	运行 SQL 脚本	`@/home/oracle/init.sql`	路径可为绝对或相对
设置输出格式	`SET LINESIZE n` / `SET PAGESIZE n` / `SET FEEDBACK OFF`	控制查询显示	`SET LINESIZE 200` `SET PAGESIZE 100`	FEEDBACK OFF 隐藏”X rows selected”
启用输出	`SET SERVEROUTPUT ON`	显示 PL/SQL 的 DBMS_OUTPUT	`SET SERVEROUTPUT ON` `BEGIN DBMS_OUTPUT.PUT_LINE('OK'); END;`	默认关闭；缓冲区默认 20000 字节
描述表结构	`DESC[ribe] table_name`	查看列定义	`DESC employees`	等价于查询 USER_TAB_COLUMNS
编辑命令	`EDIT`	调用系统编辑器修改最后一条命令	`EDIT`	默认调用 vi（Linux）或 notepad（Windows）
存储变量	`DEFINE var = value`	定义替换变量	`DEFINE dept_id = 10` `SELECT * FROM emp WHERE dept = &dept_id;`	&var 在运行时替换；&&var 可复用
清屏	`CLEAR SCREEN`	清除终端屏幕	`CLEAR SCREEN`	仅部分终端支持
查看环境	`SHOW USER` / `SHOW CON_NAME`	显示当前用户或容器名	`SHOW USER` → USER is “SCOTT” / `SHOW CON_NAME` → ORCLPDB1	多租户环境下重要

注意：

SQL 语句以分号 ; 或斜杠 / 结尾执行。

命令不区分大小写，但对象名区分（除非用双引号）。

9.2 SQLcl 命令速查

命令名称	语法	用途	代码示例	注意事项
启动连接	`sql [user/pass@connect_string]`	现代化 SQL*Plus 替代工具	`sql hr/hr@localhost:1521/ORCLPDB1`	需 Java 8+；从 Oracle 官网下载
自动补全	输入部分命令后按 Tab	智能提示对象名、关键字	`SEL` → 自动补全 SELECT	支持列名、表名、函数等
格式化 SQL	`FORMAT`	美化 SQL 语句	`SELECT * FROM emp WHERE id=1;` `FORMAT` → 自动缩进换行	内置 SQL Developer 引擎
保存历史	`HISTORY`	查看已执行命令	`HISTORY` / `HISTORY 5` → 显示第 5 条	历史持久化到 `~/.sqlcl/history`
导出结果	`SPOOL file.txt`	将输出写入文件	`SPOOL report.txt` `SELECT * FROM dual;` `SPOOL OFF`	支持 CSV、JSON（需额外命令）
切换连接	`CONNECT user/pass@tns`	在会话中切换数据库	`CONNECT sys/sys@orcl AS SYSDBA`	无需退出
快捷别名	`ALIAS name = sql`	创建自定义命令	`ALIAS emp10 = SELECT * FROM emp WHERE dept=10;` `emp10;`	类似 shell alias
查看对象	`INFO table_name`	显示表结构、索引、约束	`INFO employees`	比 DESC 更丰富
支持 ANSI SQL	—	兼容标准 SQL 语法	使用 LIMIT、OFFSET（12c+）	向后兼容 SQL*Plus 命令
彩色输出	—	语法高亮	`SELECT * FROM emp;` → 关键字高亮	提升可读性

注意：

SQLcl 是免费工具，包含在 Oracle Instant Client 或独立下载包中。

支持 JavaScript 扩展（通过 SCRIPT 命令）。

9.3 RMAN 命令速查

命令名称	语法	用途	代码示例	注意事项
启动 RMAN	`rman TARGET /`	以本地 OS 认证连接	`rman TARGET sys/oracle@orcl`	需配置 ORACLE_HOME 和 PATH
连接目录库	`rman TARGET / CATALOG cat_user/cat_pass@cat_db`	使用恢复目录（可选）	—	目录库存储备份元数据，支持长期保留
备份数据库	`BACKUP DATABASE;`	全量物理备份	`BACKUP DATABASE PLUS ARCHIVELOG;`	默认备份到 FRA（Flash Recovery Area）
备份归档日志	`BACKUP ARCHIVELOG ALL DELETE INPUT;`	备份并删除已备日志	—	防止归档空间耗尽
列出备份	`LIST BACKUP SUMMARY;`	查看现有备份集	`LIST BACKUP OF DATABASE;`	可按时间、对象过滤
验证备份	`RESTORE DATABASE VALIDATE;`	检查备份完整性	—	不实际还原，仅校验块
恢复数据库	`RUN { SET UNTIL TIME "..."; RESTORE DATABASE; RECOVER DATABASE; }`	时间点恢复	`SET UNTIL SCN 123456;`	需先 STARTUP MOUNT
删除过期备份	`DELETE OBSOLETE;`	清理超出保留策略的备份	`CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;`	自动标记 obsolete
交叉检查	`CROSSCHECK BACKUP;`	同步控制文件与物理文件状态	`DELETE EXPIRED BACKUP;`	EXPIRED 表示控制文件有记录但文件不存在
配置自动备份	`CONFIGURE CONTROLFILE AUTOBACKUP ON;`	自动备份控制文件和 SPFILE	—	强烈建议开启
查看配置	`SHOW ALL;`	显示当前 RMAN 设置	—	包括通道、压缩、加密等

注意：

RMAN 备份必须启用 ARCHIVELOG 模式。

所有操作记录在控制文件或恢复目录中。

9.4 Data Pump（expdp / impdp）命令速查

参数名称	语法	用途	代码示例	注意事项
DIRECTORY	`DIRECTORY=dir_name`	指定 dump 文件存储目录	`expdp system DIRECTORY=dp_dir DUMPFILE=full.dmp`	必须预先创建 Oracle 目录对象
DUMPFILE	`DUMPFILE=file.dmp`	指定备份文件名	`DUMPFILE=exp_%U.dmp` → 生成 exp_01.dmp, exp_02.dmp…	%U 支持并行多文件
LOGFILE	`LOGFILE=log.log`	指定日志文件	`LOGFILE=exp.log`	日志记录详细过程和错误
SCHEMAS	`SCHEMAS=hr,scott`	按用户导出/导入	`expdp system SCHEMAS=hr`	默认包含数据+元数据
TABLES	`TABLES=emp,dept`	按表导出	`expdp scott TABLES=employees`	可加 `QUERY="WHERE id>100"`
FULL	`FULL=Y`	全库导出	`expdp system FULL=Y`	需 EXP_FULL_DATABASE 角色
REMAP_SCHEMA	`REMAP_SCHEMA=old:new`	导入时重定向方案	`impdp system REMAP_SCHEMA=hr:hr_test`	常用于测试环境
TABLE_EXISTS_ACTION	`TABLE_EXISTS_ACTION=REPLACE`	处理同名表	`impdp ... TABLE_EXISTS_ACTION=TRUNCATE`	选项：SKIP（默认）、APPEND、TRUNCATE、REPLACE
PARALLEL	`PARALLEL=4`	并行作业提升性能	`expdp ... PARALLEL=4 DUMPFILE=part_%U.dmp`	需 Enterprise Edition；worker 进程数
CONTENT	`CONTENT=METADATA_ONLY`	仅导出结构	`expdp ... CONTENT=DATA_ONLY`	可分离结构与数据
NETWORK_LINK	`NETWORK_LINK=dblink`	直接跨库导入（无需中间文件）	`impdp system NETWORK_LINK=remote_db SCHEMAS=hr`	源端需可访问目标端 dblink

注意：

expdp/impdp 是服务器端工具，DIRECTORY 路径在数据库服务器上。

不支持 SYS 用户对象导出（除数据字典外）。

9.5 Listener 与 TNS 配置命令（lsnrctl / tnsping）

命令名称	语法	用途	代码示例	注意事项
启动监听器	`lsnrctl start [listener_name]`	启动监听进程	`lsnrctl start`	默认监听器名为 LISTENER
停止监听器	`lsnrctl stop`	停止监听	`lsnrctl stop`	不影响已连接会话
查看状态	`lsnrctl status`	显示监听器注册的服务和处理程序	`lsnrctl status`	关注 “Services Summary” 是否包含目标数据库
重载配置	`lsnrctl reload`	重新读取 listener.ora	`lsnrctl reload`	修改配置后无需重启
查看服务	`lsnrctl services`	显示每个服务的连接负载	`lsnrctl services`	用于诊断连接问题
测试 TNS 解析	`tnsping tns_alias`	验证 tnsnames.ora 配置是否可达	`tnsping orcl`	仅测试网络层和监听响应，不验证用户名/密码
测试 EZConnect	`tnsping //host:port/service`	测试简易连接字符串	`tnsping //192.168.1.10:1521/ORCLCDB`	12c+ 支持
动态注册	—	PMON 自动向监听器注册实例	—	需 LOCAL_LISTENER 或默认端口 1521；静态注册需在 listener.ora 中配置 SID_LIST
配置文件位置	`$ORACLE_HOME/network/admin/listener.ora` / `$ORACLE_HOME/network/admin/tnsnames.ora`	监听器和客户端连接配置	—	客户端只需 tnsnames.ora；服务器需两者

注意：

若 status 中无数据库服务，检查：

数据库是否启动

LOCAL_LISTENER 参数是否正确

防火墙是否开放 1521 端口

tnsping 成功 ≠ 能连接数据库（可能密码错或服务名不对）。

第十章：高可用与分布式架构（选学）

10.1 RAC（Real Application Clusters）

概念名称	说明	注意事项
共享存储	所有节点访问相同的数据库文件	必须使用支持集群的存储设备，如 SAN 或 NAS
负载均衡	自动分配连接至负载最低的实例	需在客户端 TNS 连接字符串中指定 LOAD_BALANCE=YES
故障切换	当一个节点失败时，会话自动转移至其他活动节点	使用 Transparent Application Failover（TAF）提升用户体验
Cache Fusion	节点间缓存数据块共享机制	通过私有互联网络实现低延迟通信

步骤名称	操作细节	注意事项
安装 Grid Infrastructure	设置 ASM 和 Clusterware 环境	必须首先安装 GI，再安装 RDBMS
创建 RAC 数据库	使用 DBCA 工具选择 RAC 模板创建 DB	需预先设置好共享存储和 GI
添加节点	通过 OUI 增加新节点到现有集群	新节点需满足软件版本一致性和 OS 兼容性要求

10.2 Data Guard

概念名称	说明	注意事项
物理备用数据库	数据文件与主库完全一致	只能执行 Redo Apply；适合灾难恢复
逻辑备用数据库	主库事务转换为 SQL 在备库重做	支持查询和报表，但某些操作不可行
最大保护模式	强制所有事务同步提交到备库	影响性能；至少需要两个备库
最高性能模式	异步传输日志，最小化对主库影响	默认模式；适用于大多数场景

步骤名称	操作细节	注意事项
创建物理备库	复制主库数据文件，应用最新归档日志	初始同步可通过 RMAN DUPLICATE 实现
配置 DG Broker	使用 DGMGRL 工具简化管理	建议启用以提高故障切换效率
角色切换	从主库切换到备库或反之	规划好停机窗口；测试切换流程

10.3 GoldenGate

概念名称	说明	注意事项
Extract	从源数据库抽取变更数据	可配置抽取级别（全量/增量）；支持异构环境
Pump	将 Extract 输出发送到目标端	推荐用于跨网段或远程复制
Replicat	在目标端应用变更	支持冲突检测与解决策略
初始化加载	在开始实时复制前同步源目标数据	可用 Data Pump 或 Export/Import

步骤名称	操作细节	注意事项
部署 Extract	配置 Manager、Extract、Pump 进程	需确保源数据库开启归档模式
配置 Replicat	在目标端定义 Replicat 参数	注意字符集兼容性和数据类型映射
监控状态	使用 GGSCI 命令查看进程健康状况	定期检查 Lag 时间和错误日志
解决冲突	根据业务规则处理重复或不一致记录	设计合理的冲突策略至关重要

10.4 分区表与物化视图

分区表

方法名称	语法	用途	示例	注意事项
RANGE 分区	`PARTITION BY RANGE(column) (...)`	按数值范围划分数据	`CREATE TABLE sales (...) PARTITION BY RANGE(sale_date) (...);`	适合日期等连续值
LIST 分区	`PARTITION BY LIST(column) (...)`	按离散值划分数据	`CREATE TABLE customers (...) PARTITION BY LIST(region_id) (...);`	适合地区代码等固定集合
HASH 分区	`PARTITION BY HASH(column) PARTITIONS n`	通过哈希算法均匀分布数据	`CREATE TABLE orders (...) PARTITION BY HASH(order_id) PARTITIONS 4;`	提高 I/O 并发度

物化视图

方法名称	语法	用途	示例	注意事项
创建 MV	`CREATE MATERIALIZED VIEW mv_name AS SELECT ... FROM ...`	存储查询结果快照	`CREATE MATERIALIZED VIEW sales_summary AS SELECT region_id, SUM(amount) FROM sales GROUP BY region_id;`	可选刷新频率（ON COMMIT / ON DEMAND）
刷新 MV	`EXEC DBMS_MVIEW.REFRESH('mv_name');`	更新 MV 数据	`EXEC DBMS_MVIEW.REFRESH('sales_summary', 'C');`	’C’ 表示完整刷新；‘F’ 增量刷新（需支持）

注意：

分区设计应考虑查询模式和数据分布特点。

物化视图适合频繁查询且更新较少的数据集。