万字书籍速通之《Oracle数据库实用教程(19C)》

万字书籍速通之《Oracle数据库实用教程(19C)》

📖 导读：本文是对《Oracle数据库实用教程(19C)》全书的精华浓缩与速通指南。从Oracle数据库的基础安装到多租户架构，从SQL操作到PL/SQL开发，从备份恢复到闪回技术，涵盖Oracle DBA和开发者必须掌握的核心知识体系。适合在校学生、初级DBA及需要快速回顾Oracle知识体系的开发者阅读收藏。

第1章 Oracle数据库基础

1.1 Oracle简介

Oracle数据库是由Oracle公司开发的关系型数据库管理系统（RDBMS），自1979年发布第一个商业版本以来，已经发展成为全球最广泛使用的企业级数据库之一。Oracle 19c（即19.3版本）是Oracle数据库的长期支持版本（Long-Term Support Release），其支持周期延续到2027年，是目前企业生产环境中部署最广泛的版本之一。

Oracle数据库的核心特点：

Oracle版本演进简述：

• Oracle 12c（2013）：引入多租户架构
• Oracle 18c（2018）：引入自动化特性
• Oracle 19c（2019）：长期支持版，整合12c/18c所有特性
• Oracle 21c（2021）：创新版本
• Oracle 23c（2023）：AI向量搜索等新特性

1.2 Oracle数据库安装与卸载

Oracle 19c在Linux（以Oracle Linux 7/8为例）上的安装流程：

一、安装前准备：

# 1. 检查系统要求
uname -r # 内核版本
free -g # 内存（建议至少2GB）
df -h /tmp # 临时空间（建议至少1GB）

# 2. 创建用户和组
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

# 3. 创建安装目录
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

# 4. 配置内核参数 /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 4294967295
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 9000 65500

# 5. 配置用户资源限制 /etc/security/limits.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

二、环境变量配置（~oracle/.bash_profile）：

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

三、静默安装（推荐生产环境）：

# 解压安装包
cd /u01/app/oracle/product/19.0.0/dbhome_1
unzip LINUX.X64_193000_db_home.zip -d .

# 执行静默安装
./runInstaller -silent -responseFile /home/oracle/db_install.rsp \\
oracle.install.option=INSTALL_DB_SWONLY \\
ORACLE_HOSTNAME=oracledb \\
UNIX_GROUP_NAME=oinstall \\
INVENTORY_LOCATION=/u01/app/oraInventory \\
ORACLE_HOME=$ORACLE_HOME \\
ORACLE_BASE=$ORACLE_BASE \\
oracle.install.db.InstallEdition=EE \\
oracle.install.db.OSDBA_GROUP=dba

# 运行root脚本
sudo /u01/app/oraInventory/orainstRoot.sh
sudo $ORACLE_HOME/root.sh

四、卸载Oracle：

# 使用deinstall工具
$ORACLE_HOME/deinstall/deinstall

# 清理残留
rm -rf /u01/app/oracle
rm -rf /u01/app/oraInventory
# 删除相关用户、组、环境变量配置

1.3 SQL*Plus工具

SQL*Plus是Oracle自带的命令行交互工具，是DBA日常运维的基础工具。

基本操作：

— 连接数据库
sqlplus / as sysdba
sqlplus scott/tiger@orcl
sqlplus sys/password@orcl as sysdba

— 常用SET命令
SET LINESIZE 200 — 行宽
SET PAGESIZE 50 — 每页行数
SET TIMING ON — 显示执行时间
SET AUTOTRACE ON — 显示执行计划
SET SERVEROUTPUT ON — 显示DBMS_OUTPUT输出
COLUMN ename FORMAT A20 — 格式化列

— 执行脚本
@/home/oracle/scripts/create_tables.sql
START /home/oracle/scripts/init.sql

— 导出结果
SPOOL /tmp/output.txt
SELECT * FROM employees;
SPOOL OFF

1.4 Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager Database Express（EM Express）是19c内置的轻量级Web管理界面：

— 查看EM Express端口（默认5500）
SELECT dbms_xdb_config.gethttpsport FROM dual;

— 修改端口
EXEC dbms_xdb_config.sethttpsport(5501);

— 访问地址：https://hostname:5500/em

对于企业级大规模管理，Oracle还提供OEM Cloud Control（独立部署的集中管理平台），支持多数据库、中间件、主机的统一监控和管理。

1.5 Oracle网络体系

Oracle网络通信基于Oracle Net Services，核心组件包括：

┌─────────────┐ TCP/IP ┌─────────────┐
│ Client │ ◄────────────────────► │ Server │
│ (SQL*Plus) │ │ (Listener) │
└─────────────┘ └─────────────┘
│ │
tnsnames.ora listener.ora

三大配置文件：

listener.ora 示例：

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
)

tnsnames.ora 示例：

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

1.6 Net Configuration Assistant (NetCA)

NetCA是图形化网络配置工具，用于：

• 配置/删除监听器
• 配置本地Net服务名（tnsnames.ora条目）
• 配置目录服务访问
• 配置命名方法优先级

# 命令行启动
netca

# 静默模式
netca /silent /responseFile /path/to/netca.rsp

1.7 Net Manager

Net Manager是更底层的网络管理GUI工具，可以精确编辑listener.ora和tnsnames.ora中的每一个参数，适合高级网络配置场景。

netmgr

1.8 Database Configuration Assistant (DBCA)

DBCA用于创建、配置、删除Oracle数据库实例：

# 图形化创建
dbca

# 静默创建数据库
dbca -silent -createDatabase \\
-templateName General_Purpose.dbc \\
-gdbName orcl \\
-sid orcl \\
-sysPassword Oracle_2024 \\
-systemPassword Oracle_2024 \\
-emConfiguration NONE \\
-datafileDestination /u01/app/oracle/oradata \\
-storageType FS \\
-characterSet AL32UTF8 \\
-nationalCharacterSet AL16UTF16 \\
-totalMemory 2048

第2章 Oracle体系结构

2.1 Oracle体系概述

Oracle体系结构是理解整个数据库运行机制的基石。一个Oracle数据库系统由 实例（Instance） 和 数据库（Database） 两部分组成：

┌──────────────── Oracle Instance ────────────────┐
│ │
│ ┌──────────── SGA ────────────┐ │
│ │ Database Buffer Cache │ │
│ │ Shared Pool │ │
│ │ Redo Log Buffer │ │
│ │ Large Pool / Java Pool │ │
│ └─────────────────────────────┘ │
│ │
│ ┌──── Background Processes ────┐ │
│ │ DBWR LGWR SMON PMON │ │
│ │ CKPT ARCn MMON … │ │
│ └──────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────┘
│
▼
┌──────────────── Oracle Database ─────────────────┐
│ Data Files │ Redo Log Files │ Control Files │
│ Parameter File │ Archive Logs │ … │
└──────────────────────────────────────────────────┘

2.2 内存结构

Oracle内存分为 SGA（System Global Area） 和 PGA（Program Global Area） ：

SGA组件：

— 查看SGA组成
SHOW SGA;
SELECT * FROM V$SGA;

— 查看各组件大小
SELECT component, current_size/1024/1024 AS "Size(MB)"
FROM V$SGA_DYNAMIC_COMPONENTS;

PGA： 每个服务器进程私有的内存区域，用于排序（SORT_AREA）、哈希连接、位图合并等操作。

— 查看PGA统计
SELECT * FROM V$PGASTAT;

— 设置自动内存管理
ALTER SYSTEM SET MEMORY_TARGET = 4G SCOPE=SPFILE; — AMM
ALTER SYSTEM SET SGA_TARGET = 2G SCOPE=SPFILE; — ASMM
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 512M SCOPE=SPFILE;

2.3 进程结构

核心后台进程：

— 查看后台进程
SELECT pname, description FROM V$BGPROCESS WHERE paddr <> '00';

2.4 物理存储结构

Oracle数据库的物理文件包括：

— 查看所有数据文件
SELECT file_name, bytes/1024/1024 AS "Size(MB)", status
FROM DBA_DATA_FILES;

— 查看控制文件
SELECT name, status FROM V$CONTROLFILE;

— 查看重做日志文件
SELECT group#, member FROM V$LOGFILE;

— 查看临时文件
SELECT file_name, bytes/1024/1024 FROM DBA_TEMP_FILES;

2.5 逻辑存储结构

逻辑存储层次：数据库 → 表空间 → 段(Segment) → 区(Extent) → 数据块(Block)

— 查看表空间
SELECT tablespace_name, status, contents
FROM DBA_TABLESPACES;

— 查看段信息
SELECT segment_name, segment_type, bytes/1024/1024
FROM DBA_SEGMENTS
WHERE owner = 'SCOTT';

— 查看数据块大小
SHOW PARAMETER db_block_size; — 默认8KB

区（Extent）管理：

• 本地管理表空间（LMT）：使用位图管理区分配，推荐
• 字典管理表空间（DMT）：使用数据字典表管理，已不推荐

2.6 Oracle数据库启动与关闭

启动阶段：

— 完整启动流程
STARTUP NOMOUNT; — 读取参数文件，分配内存，启动后台进程
STARTUP MOUNT; — 打开控制文件，可执行恢复操作
STARTUP OPEN; — 打开数据文件和重做日志，数据库可用

— 一步到位
STARTUP;
— 等价于
STARTUP OPEN;

— 受限模式启动（仅允许有RESTRICTED SESSION权限的用户）
STARTUP RESTRICT;

— 强制启动（实例异常时使用）
STARTUP FORCE;

关闭阶段：

SHUTDOWN NORMAL; — 等待所有连接断开（可能等很久）
SHUTDOWN IMMEDIATE; — 回滚未提交事务，断开连接（推荐）
SHUTDOWN TRANSACTIONAL; — 等待活动事务完成
SHUTDOWN ABORT; — 强制终止（相当于kill，需要实例恢复）

2.7 数据字典

数据字典是Oracle的元数据存储，以SYS用户拥有的基表为基础：

— 数据字典视图分类
— USER_* : 当前用户拥有的对象
— ALL_* : 当前用户有权访问的对象
— DBA_* : 数据库中所有对象（需DBA权限）
— V$* : 动态性能视图（实例运行时信息）

— 常用查询
SELECT * FROM USER_TABLES; — 当前用户的表
SELECT * FROM DBA_USERS; — 所有用户
SELECT * FROM V$SESSION; — 当前会话
SELECT * FROM V$SQL WHERE sql_text LIKE '%employees%';

— 查看数据字典视图定义
SELECT comments FROM DICTIONARY WHERE table_name = 'DBA_TABLES';

2.8 SQL语句的处理流程

一条SQL语句在Oracle中的处理流程：

SQL语句 → 语法解析 → 语义解析 → 权限检查 → 查询优化器生成执行计划
→ 行源生成 → 执行 → 返回结果

— 查看SQL执行计划
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 查看共享池中的SQL
SELECT sql_id, sql_text, executions, elapsed_time/1000000 AS "Sec"
FROM V$SQL
WHERE sql_text LIKE '%employees%'
ORDER BY elapsed_time DESC;

第3章 Oracle存储结构管理

3.1 用户表空间管理

— 创建表空间
CREATE TABLESPACE tbs_data
DATAFILE '/u01/app/oracle/oradata/orcl/tbs_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

— 修改表空间
ALTER TABLESPACE tbs_data ADD DATAFILE '/u01/app/oracle/oradata/orcl/tbs_data02.dbf' SIZE 500M;
ALTER TABLESPACE tbs_data READ ONLY;
ALTER TABLESPACE tbs_data OFFLINE;

— 删除表空间
DROP TABLESPACE tbs_data INCLUDING CONTENTS AND DATAFILES;

— 查看表空间使用情况
SELECT tablespace_name,
ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
ROUND(used_percent, 2) AS used_pct
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY used_percent DESC;

3.2 临时表空间管理

临时表空间用于排序、哈希连接等操作的临时数据存储：

— 创建临时表空间
CREATE TEMPORARY TABLESPACE temp_ts
TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 2G
AUTOEXTEND ON NEXT 500M MAXSIZE 8G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

— 设为默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts;

— 创建临时表空间组
CREATE TEMPORARY TABLESPACE temp_ts2 TEMPFILE '/u01/oradata/temp02.dbf' SIZE 2G;
ALTER TABLESPACE temp_ts2 TABLESPACE GROUP temp_group;
ALTER TABLESPACE temp_ts TABLESPACE GROUP temp_group;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;

— 查看临时表空间使用
SELECT tablespace_name, tablespace_size/1024/1024 AS total_mb,
allocated_space/1024/1024 AS alloc_mb,
free_space/1024/1024 AS free_mb
FROM DBA_TEMP_FREE_SPACE;

3.3 还原表空间管理

还原（UNDO）表空间存储事务修改前的旧数据，用于回滚、读一致性和闪回：

— 创建还原表空间
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 2G
AUTOEXTEND ON NEXT 500M MAXSIZE 8G;

— 切换还原表空间
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

— 查看还原段使用
SELECT segment_name, status, bytes/1024/1024
FROM DBA_UNDO_EXTENTS;

— 设置还原保留时间（秒）
ALTER SYSTEM SET UNDO_RETENTION = 3600;

3.4 控制文件管理

控制文件记录数据库的物理结构元数据，是数据库最关键的二进制文件：

— 查看控制文件位置
SELECT name, status, block_size FROM V$CONTROLFILE;

— 查看控制文件记录的内容
SELECT type, record_size, records_total, records_used
FROM V$CONTROLFILE_RECORD_SECTION;

— 备份控制文件
ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/controlfile_backup.ctl';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE; — 生成重建脚本

— 多路复用控制文件（修改参数后重启）
ALTER SYSTEM SET control_files =
'/u01/oradata/orcl/control01.ctl',
'/u01/oradata/orcl/control02.ctl',
'/u02/oradata/orcl/control03.ctl'
SCOPE = SPFILE;

3.5 联机重做日志文件管理

— 查看日志组
SELECT group#, members, bytes/1024/1024 AS size_mb, status
FROM V$LOG;

— 添加日志组
ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/oradata/orcl/redo04a.log', '/u02/oradata/orcl/redo04b.log')
SIZE 500M;

— 添加成员（多路复用）
ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/orcl/redo01b.log' TO GROUP 1;

— 删除日志组（不能删除CURRENT状态的组）
ALTER DATABASE DROP LOGFILE GROUP 4;

— 强制日志切换
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

3.6 重做日志文件归档管理

— 查看归档模式
ARCHIVE LOG LIST;
SELECT log_mode FROM V$DATABASE;

— 开启归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

— 设置归档目的地
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/archive MANDATORY';
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 'arch_%t_%s_%r.arc';

— 手动归档
ALTER SYSTEM ARCHIVE LOG CURRENT;

— 查看归档日志
SELECT sequence#, name, applied FROM V$ARCHIVED_LOG ORDER BY sequence# DESC;

3.7 初始化参数文件管理

— SPFILE vs PFILE
— SPFILE: 二进制，可通过ALTER SYSTEM修改，优先级高
— PFILE: 文本，需重启生效

— 查看参数
SHOW PARAMETER db_name;
SELECT name, value, issys_modifiable FROM V$PARAMETER WHERE name LIKE '%sga%';

— 修改参数
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE; — 重启生效
ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH; — 立即+持久化
ALTER SYSTEM SET "_hidden_param" = value SCOPE=MEMORY; — 仅当前实例

— SPFILE与PFILE互转
CREATE PFILE='/tmp/initorcl.ora' FROM SPFILE;
CREATE SPFILE FROM PFILE='/tmp/initorcl.ora';

第4章 模式对象管理

4.1 模式含义

模式（Schema） 是数据库对象的逻辑容器，与用户一一对应。用户SCOTT创建的所有对象都属于SCOTT模式。模式对象包括：表、视图、索引、序列、同义词、存储过程等。

— 查看当前用户（模式）
SHOW USER;

— 查看当前模式下的所有对象
SELECT object_name, object_type, status FROM USER_OBJECTS;

4.2 数据表

— 创建表
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(10,2) CHECK (salary > 0),
department_id NUMBER(4),
CONSTRAINT fk_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
) TABLESPACE tbs_data;

— 修改表结构
ALTER TABLE employees ADD (phone VARCHAR2(20));
ALTER TABLE employees MODIFY (salary NUMBER(12,2));
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN first_name TO given_name;

— 表重命名
ALTER TABLE employees RENAME TO emp;

— 截断表（不可回滚，释放空间）
TRUNCATE TABLE employees;

— 删除表
DROP TABLE employees PURGE; — 不进入回收站

— 查看表结构
DESC employees;
SELECT column_name, data_type, nullable FROM USER_TAB_COLUMNS WHERE table_name = 'EMPLOYEES';

4.3 索引

— B树索引（默认）
CREATE INDEX idx_emp_name ON employees(last_name, first_name)
TABLESPACE tbs_index;

— 唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

— 位图索引（适合低基数列，DML少）
CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

— 函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(last_name));

— 复合索引
CREATE INDEX idx_emp_dept_date ON employees(department_id, hire_date DESC);

— 查看索引
SELECT index_name, index_type, uniqueness, status
FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';

— 索引有效性分析
ANALYZE INDEX idx_emp_name VALIDATE STRUCTURE;
SELECT name, height, blocks, del_lf_rows FROM INDEX_STATS;

— 重建索引
ALTER INDEX idx_emp_name REBUILD ONLINE TABLESPACE tbs_index;

— 删除索引
DROP INDEX idx_emp_name;

4.4 分区

分区表将大表按规则拆分为多个物理段，提升查询性能和可管理性：

— 范围分区（按时间）
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE NOT NULL,
amount NUMBER(12,2),
region VARCHAR2(50)
)
PARTITION BY RANGE (sale_date) (
PARTITION p_2024q1 VALUES LESS THAN (DATE '2024-04-01'),
PARTITION p_2024q2 VALUES LESS THAN (DATE '2024-07-01'),
PARTITION p_2024q3 VALUES LESS THAN (DATE '2024-10-01'),
PARTITION p_2024q4 VALUES LESS THAN (DATE '2025-01-01'),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

— 列表分区
CREATE TABLE customers (
cust_id NUMBER PRIMARY KEY,
region VARCHAR2(20),
name VARCHAR2(100)
)
PARTITION BY LIST (region) (
PARTITION p_east VALUES ('Beijing', 'Shanghai', 'Tianjin'),
PARTITION p_west VALUES ('Chengdu', 'Chongqing', 'Kunming'),
PARTITION p_other VALUES (DEFAULT)
);

— 哈希分区
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
cust_id NUMBER,
amount NUMBER
)
PARTITION BY HASH (order_id) PARTITIONS 8;

— 分区维护
ALTER TABLE sales ADD PARTITION p_2025q1 VALUES LESS THAN (DATE '2025-04-01');
ALTER TABLE sales DROP PARTITION p_2024q1;
ALTER TABLE sales TRUNCATE PARTITION p_2024q2;
ALTER TABLE sales SPLIT PARTITION p_max AT (DATE '2025-07-01')
INTO (PARTITION p_2025q2, PARTITION p_max);

4.5 索引化表

索引组织表（IOT）将数据按主键顺序存储在B树索引结构中，适合主键访问为主的场景：

CREATE TABLE order_items (
order_id NUMBER,
item_id NUMBER,
quantity NUMBER,
unit_price NUMBER(8,2),
CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id)
)
ORGANIZATION INDEX
INCLUDING unit_price
OVERFLOW TABLESPACE tbs_overflow;

4.6 其他对象

— 序列
CREATE SEQUENCE seq_emp_id START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
SELECT seq_emp_id.NEXTVAL FROM DUAL;

— 视图
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

— 同义词
CREATE SYNONYM emp FOR hr.employees;
CREATE PUBLIC SYNONYM emp FOR hr.employees;

— 物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE REFRESH FAST ON COMMIT AS
SELECT region, SUM(amount) total_amount FROM sales GROUP BY region;

第5章 Oracle数据操作

5.1 SQL概述

Oracle SQL遵循ANSI SQL标准，并扩展了大量Oracle特有语法。SQL语句分类：

• DDL（数据定义）：CREATE, ALTER, DROP, TRUNCATE
• DML（数据操纵）：SELECT, INSERT, UPDATE, DELETE, MERGE
• DCL（数据控制）：GRANT, REVOKE
• TCL（事务控制）：COMMIT, ROLLBACK, SAVEPOINT

5.2 数据查询

— 基础查询
SELECT employee_id, last_name, salary * 12 AS annual_salary
FROM employees
WHERE department_id = 80
ORDER BY salary DESC;

— 多表连接
SELECT e.last_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

— 子查询
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

— 集合操作
SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE salary > 10000;

— 分析函数
SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

— 层次查询
SELECT LPAD(' ', LEVEL*2) || last_name AS org_chart
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

— PIVOT行转列
SELECT * FROM (SELECT department_id, salary FROM employees)
PIVOT (SUM(salary) FOR department_id IN (10 AS "Dept10", 20 AS "Dept20", 30 AS "Dept30"));

5.3 数据操纵

— INSERT
INSERT INTO employees (employee_id, last_name, email, hire_date, salary)
VALUES (seq_emp_id.NEXTVAL, 'Zhang', 'zhang@example.com', SYSDATE, 8000);

— 多表插入
INSERT ALL
WHEN salary > 10000 THEN INTO high_salary VALUES (*)
WHEN salary <= 10000 THEN INTO low_salary VALUES (*)
SELECT * FROM employees;

— MERGE（UPSERT）
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.salary = s.salary
WHEN NOT MATCHED THEN INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

— UPDATE
UPDATE employees SET salary = salary * 1.1
WHERE department_id = 20 AND hire_date < DATE '2020-01-01';

— DELETE
DELETE FROM employees WHERE employee_id = 999;

— 事务控制
SAVEPOINT sp1;
UPDATE employees SET salary = 0 WHERE employee_id = 100;
ROLLBACK TO sp1;
COMMIT;

5.4 Oracle常用函数

— 字符串函数
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('oracle db') FROM DUAL;
SELECT SUBSTR('Oracle Database', 1, 6) FROM DUAL; — Oracle
SELECT INSTR('hello world', 'world') FROM DUAL; — 7
SELECT LENGTH('Oracle') FROM DUAL; — 6
SELECT REPLACE('Oracle', 'Ora', 'My') FROM DUAL; — Mycle
SELECT LPAD('7', 5, '0') FROM DUAL; — 00007
SELECT TRIM(' hello ') FROM DUAL; — hello
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+') FROM DUAL; — 123

— 数值函数
SELECT ROUND(3.14159, 2) FROM DUAL; — 3.14
SELECT TRUNC(3.99) FROM DUAL; — 3
SELECT MOD(17, 5) FROM DUAL; — 2
SELECT CEIL(4.1), FLOOR(4.9) FROM DUAL; — 5, 4
SELECT POWER(2, 10) FROM DUAL; — 1024

— 日期函数
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(DATE '2025-01-01', DATE '2024-01-01') FROM DUAL; — 12
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

— 转换函数
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_NUMBER('12345') FROM DUAL;
SELECT TO_DATE('2025-06-15', 'YYYY-MM-DD') FROM DUAL;
SELECT CAST('2025-01-01' AS DATE) FROM DUAL;

— 聚合与条件
SELECT department_id, COUNT(*), AVG(salary), MAX(salary)
FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

SELECT CASE WHEN salary > 10000 THEN 'High'
WHEN salary > 5000 THEN 'Mid'
ELSE 'Low' END AS level
FROM employees;

SELECT NVL(commission_pct, 0), COALESCE(phone, email, 'N/A') FROM employees;
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM accounts;

第6章 用户安全管理

6.1 用户安全概述

Oracle安全体系包含：认证 → 授权 → 审计 三层机制。通过用户、权限、角色、概要文件实现精细化访问控制。

6.2 概要文件管理

概要文件（Profile）用于限制用户资源使用和密码策略：

— 创建概要文件
CREATE PROFILE profile_app LIMIT
SESSIONS_PER_USER 10
CPU_PER_SESSION 360000 — 厘秒
CPU_PER_CALL 6000
CONNECT_TIME 480 — 分钟
IDLE_TIME 60
LOGICAL_READS_PER_SESSION 500000
PRIVATE_SGA 100M
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 365
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;

— 应用概要文件
ALTER USER app_user PROFILE profile_app;

— 查看概要文件
SELECT * FROM DBA_PROFILES WHERE profile = 'PROFILE_APP';

— 修改/删除
ALTER PROFILE profile_app LIMIT FAILED_LOGIN_ATTEMPTS 3;
DROP PROFILE profile_app CASCADE;

6.3 用户管理

— 创建用户
CREATE USER app_user IDENTIFIED BY "Secure#Pass2025"
DEFAULT TABLESPACE tbs_data
TEMPORARY TABLESPACE temp
QUOTA 500M ON tbs_data
PROFILE profile_app;

— 修改用户
ALTER USER app_user IDENTIFIED BY "NewPass#2025";
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user QUOTA UNLIMITED ON tbs_data;

— 删除用户
DROP USER app_user CASCADE; — CASCADE删除所有对象

— 查看用户信息
SELECT username, account_status, default_tablespace, created
FROM DBA_USERS ORDER BY created DESC;

6.4 权限管理

— 系统权限（200+种）
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;
GRANT CREATE ANY TABLE TO app_user WITH ADMIN OPTION;
GRANT DBA TO app_user; — 谨慎！

— 对象权限
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;
GRANT EXECUTE ON hr.pkg_salary TO app_user;
GRANT ALL ON hr.employees TO app_user WITH GRANT OPTION;

— 撤销权限
REVOKE CREATE TABLE FROM app_user;
REVOKE SELECT ON hr.employees FROM app_user;

— 查看权限
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'APP_USER';
SELECT * FROM DBA_TAB_PRIVS WHERE grantee = 'APP_USER';
SELECT * FROM SESSION_PRIVS; — 当前会话权限

6.5 角色管理

— 创建角色
CREATE ROLE role_developer;
CREATE ROLE role_dba IDENTIFIED BY "RolePass123";

— 授予权限给角色
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO role_developer;
GRANT SELECT ANY TABLE, INSERT ANY TABLE TO role_developer;

— 将角色授予用户
GRANT role_developer TO app_user;
GRANT role_developer TO dev_user WITH ADMIN OPTION;

— 角色层级
GRANT role_developer TO role_dba;

— 查看角色
SELECT * FROM DBA_ROLES;
SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'APP_USER';
SELECT * FROM ROLE_ROLE_PRIVS WHERE role = 'ROLE_DBA';

— 启用/禁用角色
SET ROLE role_developer;
SET ROLE NONE;
SET ROLE ALL;

6.6 审计

— 统一审计（19c推荐）
CREATE AUDIT POLICY pol_dml_actions
ACTIONS SELECT ON hr.employees, INSERT ON hr.employees,
UPDATE ON hr.employees, DELETE ON hr.employees
WHEN 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'') != ''batch_job'''
EVALUATE PER SESSION;

AUDIT POLICY pol_dml_actions;

— 审计特权操作
CREATE AUDIT POLICY pol_priv_ops
ACTIONS CREATE USER, DROP USER, ALTER USER, GRANT, REVOKE;
AUDIT POLICY pol_priv_ops BY ALL;

— 查看审计结果
SELECT event_timestamp, dbusername, action_name, object_name, return_code
FROM UNIFIED_AUDIT_TRAIL
WHERE event_timestamp > SYSDATE – 1
ORDER BY event_timestamp DESC;

— 清理审计数据
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED);

第7章 PL/SQL程序开发

7.1 PL/SQL开发基础

PL/SQL是Oracle的过程化SQL扩展语言，支持变量、控制流、异常处理：

— 匿名块
DECLARE
v_name VARCHAR2(100);
v_salary NUMBER(10,2);
v_bonus NUMBER(10,2);
c_rate CONSTANT NUMBER := 0.15;
BEGIN
SELECT last_name, salary INTO v_name, v_salary
FROM employees WHERE employee_id = 100;

IF v_salary > 10000 THEN
v_bonus := v_salary * c_rate;
ELSE
v_bonus := v_salary * 0.10;
END IF;

DBMS_OUTPUT.PUT_LINE(v_name || ' bonus: ' || v_bonus);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows returned');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

— 控制流
BEGIN
FOR i IN 1..10 LOOP
CONTINUE WHEN MOD(i, 2) = 0;
DBMS_OUTPUT.PUT_LINE('Odd: ' || i);
END LOOP;

FOR rec IN (SELECT employee_id, last_name FROM employees WHERE ROWNUM <= 5) LOOP
DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.last_name);
END LOOP;
END;
/

7.2 游标

— 显式游标
DECLARE
CURSOR c_emp IS
SELECT employee_id, last_name, salary
FROM employees WHERE department_id = 80;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ': ' || v_emp.salary);
END LOOP;
CLOSE c_emp;
END;
/

— 参数化游标
DECLARE
CURSOR c_dept_emp(p_dept_id NUMBER) IS
SELECT last_name, salary FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR rec IN c_dept_emp(20) LOOP
DBMS_OUTPUT.PUT_LINE(rec.last_name);
END LOOP;
END;
/

— 游标FOR UPDATE（用于更新）
DECLARE
CURSOR c_update IS
SELECT salary FROM employees WHERE department_id = 30
FOR UPDATE NOWAIT;
BEGIN
FOR rec IN c_update LOOP
UPDATE employees SET salary = rec.salary * 1.05
WHERE CURRENT OF c_update;
END LOOP;
COMMIT;
END;
/

— REF CURSOR（动态游标）
DECLARE
TYPE t_ref_cur IS REF CURSOR;
v_cur t_ref_cur;
v_name VARCHAR2(100);
BEGIN
OPEN v_cur FOR SELECT last_name FROM employees WHERE ROWNUM <= 3;
LOOP
FETCH v_cur INTO v_name;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE v_cur;
END;
/

7.3 存储过程

CREATE OR REPLACE PROCEDURE sp_adjust_salary (
p_dept_id IN NUMBER,
p_percent IN NUMBER DEFAULT 5,
p_count OUT NUMBER
)
IS
v_count NUMBER := 0;
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percent / 100)
WHERE department_id = p_dept_id;

v_count := SQL%ROWCOUNT;
p_count := v_count;

DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' employees.');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(–20001, 'Salary adjustment failed: ' || SQLERRM);
END sp_adjust_salary;
/

— 调用
DECLARE
v_cnt NUMBER;
BEGIN
sp_adjust_salary(80, 10, v_cnt);
DBMS_OUTPUT.PUT_LINE('Affected: ' || v_cnt);
END;
/

7.4 函数

CREATE OR REPLACE FUNCTION fn_get_annual_salary (
p_emp_id IN NUMBER
) RETURN NUMBER
IS
v_annual NUMBER(12,2);
BEGIN
SELECT (salary + NVL(commission_pct, 0) * salary) * 12
INTO v_annual
FROM employees
WHERE employee_id = p_emp_id;

RETURN v_annual;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN –1;
END fn_get_annual_salary;
/

— 调用
SELECT last_name, fn_get_annual_salary(employee_id) AS annual
FROM employees WHERE department_id = 10;

7.5 程序包

程序包（Package）将相关的类型、变量、游标、过程和函数封装在一起：

— 包规范（接口）
CREATE OR REPLACE PACKAGE pkg_employee AS
c_max_salary CONSTANT NUMBER := 999999;
TYPE t_emp_rec IS RECORD (
emp_id NUMBER,
name VARCHAR2(100),
salary NUMBER
);
TYPE t_emp_tab IS TABLE OF t_emp_rec INDEX BY PLS_INTEGER;

PROCEDURE hire_employee(p_name VARCHAR2, p_dept NUMBER, p_salary NUMBER);
PROCEDURE fire_employee(p_emp_id NUMBER);
FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
FUNCTION get_dept_employees(p_dept_id NUMBER) RETURN t_emp_tab;
END pkg_employee;
/

— 包体（实现）
CREATE OR REPLACE PACKAGE BODY pkg_employee AS

PROCEDURE hire_employee(p_name VARCHAR2, p_dept NUMBER, p_salary NUMBER) IS
BEGIN
IF p_salary > c_max_salary THEN
RAISE_APPLICATION_ERROR(–20002, 'Salary exceeds maximum');
END IF;
INSERT INTO employees(employee_id, last_name, department_id, salary, hire_date)
VALUES(seq_emp_id.NEXTVAL, p_name, p_dept, p_salary, SYSDATE);
COMMIT;
END;

PROCEDURE fire_employee(p_emp_id NUMBER) IS
BEGIN
DELETE FROM employees WHERE employee_id = p_emp_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(–20003, 'Employee not found');
END IF;
COMMIT;
END;

FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
RETURN v_sal;
END;

FUNCTION get_dept_employees(p_dept_id NUMBER) RETURN t_emp_tab IS
v_tab t_emp_tab;
i PLS_INTEGER := 0;
BEGIN
FOR rec IN (SELECT employee_id, last_name, salary
FROM employees WHERE department_id = p_dept_id) LOOP
i := i + 1;
v_tab(i).emp_id := rec.employee_id;
v_tab(i).name := rec.last_name;
v_tab(i).salary := rec.salary;
END LOOP;
RETURN v_tab;
END;

END pkg_employee;
/

— 调用
EXEC pkg_employee.hire_employee('Li Ming', 20, 8000);
SELECT pkg_employee.get_salary(100) FROM DUAL;

7.6 触发器

— 行级触发器（审计日志）
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary <> NEW.salary)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
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, SYSTIMESTAMP
);
COMMIT;
END;
/

— 语句级触发器
CREATE OR REPLACE TRIGGER trg_emp_insert_check
BEFORE INSERT ON employees
BEGIN
IF TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' THEN
RAISE_APPLICATION_ERROR(–20010, 'Insert only allowed during work hours');
END IF;
END;
/

— INSTEAD OF触发器（用于视图）
CREATE OR REPLACE TRIGGER trg_v_emp_dept_insert
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
INSERT INTO employees(last_name, department_id) VALUES(:NEW.last_name, :NEW.department_id);
END;
/

— 查看触发器
SELECT trigger_name, trigger_type, triggering_event, status
FROM USER_TRIGGERS WHERE table_name = 'EMPLOYEES';

— 禁用/启用
ALTER TRIGGER trg_emp_salary_audit DISABLE;
ALTER TRIGGER trg_emp_salary_audit ENABLE;

第8章 数据库备份与恢复

8.1 备份与恢复概述

恢复场景：

• 数据文件损坏 → 还原+恢复
• 误删表 → 闪回/时间点恢复
• 实例崩溃 → 自动实例恢复（SMON）
• 介质故障 → RMAN恢复

8.2 手动备份与恢复

— 热备份（需归档模式）
ALTER TABLESPACE tbs_data BEGIN BACKUP;
— OS层面复制数据文件
— cp /u01/oradata/orcl/tbs_data01.dbf /backup/
ALTER TABLESPACE tbs_data END BACKUP;

— 用户管理的完全恢复
— 1. 还原数据文件
— 2. 恢复
RECOVER DATAFILE '/u01/oradata/orcl/tbs_data01.dbf';
— 或
RECOVER DATABASE;
— 3. 打开
ALTER DATABASE OPEN;

8.3 RMAN备份与恢复

RMAN（Recovery Manager）是Oracle官方推荐的物理备份恢复工具：

# 连接RMAN
rman target /

# 配置
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/%U';

# 全库备份
BACKUP DATABASE PLUS ARCHIVELOG;

# 增量备份
BACKUP INCREMENTAL LEVEL 0 DATABASE; — 0级（基线）
BACKUP INCREMENTAL LEVEL 1 DATABASE; — 1级增量

# 表空间备份
BACKUP TABLESPACE tbs_data;

# 归档日志备份
BACKUP ARCHIVELOG ALL DELETE INPUT;

# 控制文件备份
BACKUP CURRENT CONTROLFILE;

# 查看备份
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;

# 恢复整个数据库
RUN {
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

# 恢复到指定时间点（PITR）
RUN {
STARTUP MOUNT;
SET UNTIL TIME "TO_DATE('2025-06-15 14:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

# 恢复单个数据文件
RUN {
SQL 'ALTER TABLESPACE tbs_data OFFLINE';
RESTORE DATAFILE '/u01/oradata/orcl/tbs_data01.dbf';
RECOVER DATAFILE '/u01/oradata/orcl/tbs_data01.dbf';
SQL 'ALTER TABLESPACE tbs_data ONLINE';
}

# 交叉检查与清理
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;

8.4 逻辑备份与恢复

Data Pump是Oracle的逻辑备份工具（替代旧版exp/imp）：

# 全库导出
expdp system/password DIRECTORY=dpump_dir DUMPFILE=full_db.dmp \\
FULL=Y LOGFILE=full_db.log COMPRESSION=ALL PARALLEL=4

# 模式导出
expdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp \\
SCHEMAS=HR LOGFILE=hr_schema.log

# 表导出
expdp hr/password DIRECTORY=dpump_dir DUMPFILE=employees.dmp \\
TABLES=employees,departments

# 导入
impdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp \\
SCHEMAS=HR REMAP_SCHEMA=HR:HR_NEW LOGFILE=import.log

# 表空间传输
expdp system/password DIRECTORY=dpump_dir DUMPFILE=ts_transport.dmp \\
TRANSPORT_TABLESPACES=tbs_data

# 创建目录对象
CREATE DIRECTORY dpump_dir AS '/u01/backup/datapump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO hr;

第9章 闪回技术

9.1 闪回技术概述

闪回（Flashback）技术是Oracle提供的一组数据恢复手段，可在不做完整恢复的情况下快速回退错误操作：

9.2 闪回查询

— 查询5分钟前的数据
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '5' MINUTE)
WHERE employee_id = 100;

— 查询指定SCN的数据
SELECT * FROM employees
AS OF SCN 123456789
WHERE department_id = 20;

— 查询指定时间点
SELECT * FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2025-06-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

— 基于闪回查询恢复数据
INSERT INTO employees
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '30' MINUTE)
WHERE employee_id NOT IN (SELECT employee_id FROM employees);

9.3 闪回版本查询

— 查看某行的所有历史版本
SELECT versions_starttime, versions_endtime, versions_operation,
employee_id, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE employee_id = 100;

— VERSIONS_OPERATION: I=Insert, U=Update, D=Delete

— 查看SCN范围
SELECT versions_startscn, versions_endscn, versions_xid, salary
FROM employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 100;

9.4 闪回事务查询

— 查询特定事务的变更及撤销SQL
SELECT xid, operation, table_name, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE table_name = 'EMPLOYEES'
AND operation = 'UPDATE'
AND commit_timestamp > SYSDATE – 1;

— 结合闪回版本查询获取事务ID
SELECT versions_xid FROM employees
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE employee_id = 100 AND versions_operation = 'U';

9.5 闪回表

— 启用行移动（闪回表前提）
ALTER TABLE employees ENABLE ROW MOVEMENT;

— 闪回表到指定时间
FLASHBACK TABLE employees TO TIMESTAMP
TO_TIMESTAMP('2025-06-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS');

— 闪回表到指定SCN
FLASHBACK TABLE employees TO SCN 987654321;

— 闪回多个表（保持引用完整性）
FLASHBACK TABLE employees, departments TO TIMESTAMP (SYSTIMESTAMP – INTERVAL '1' HOUR);

9.6 闪回删除

— 删除表（进入回收站）
DROP TABLE employees;

— 查看回收站
SELECT object_name, original_name, droptime FROM RECYCLEBIN;
SHOW RECYCLEBIN;

— 恢复表
FLASHBACK TABLE employees TO BEFORE DROP;

— 恢复并重命名
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

— 清空回收站
PURGE RECYCLEBIN; — 当前用户
PURGE DBA_RECYCLEBIN; — DBA
PURGE TABLE employees; — 指定表

— 绕过回收站直接删除
DROP TABLE employees PURGE;

9.7 闪回数据库

— 前提：开启闪回日志（需MOUNT状态配置）
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 4320; — 保留3天（分钟）
ALTER DATABASE FLASHBACK ON;

— 执行闪回数据库
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP
TO_TIMESTAMP('2025-06-14 08:00:00', 'YYYY-MM-DD HH24:MI:SS');
— 或
FLASHBACK DATABASE TO SCN 567890123;
ALTER DATABASE OPEN RESETLOGS;

— 创建还原点（命名快照）
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
— 闪回到还原点
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
DROP RESTORE POINT before_upgrade;

— 查看闪回日志
SELECT oldest_flashback_scn, retention_target, flashback_size/1024/1024
FROM V$FLASHBACK_DATABASE_LOG;

9.8 闪回数据归档（FDA）

— 创建闪回归档表空间
CREATE TABLESPACE fda_ts DATAFILE '/u01/oradata/orcl/fda01.dbf' SIZE 2G;

— 创建闪回归档
CREATE FLASHBACK ARCHIVE fda_1year
TABLESPACE fda_ts
RETENTION 1 YEAR;

— 设为默认
ALTER FLASHBACK ARCHIVE fda_1year SET DEFAULT;

— 为表启用闪回归档
ALTER TABLE hr.employees FLASHBACK ARCHIVE fda_1year;

— 查询任意历史时间点
SELECT * FROM hr.employees
AS OF TIMESTAMP TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

— 移除闪回归档
ALTER TABLE hr.employees NO FLASHBACK ARCHIVE;
DROP FLASHBACK ARCHIVE fda_1year;

第10章 多租户数据库体系

10.1 多租户数据库系统结构

Oracle 12c引入的多租户（Multitenant）架构是Oracle数据库的重大变革，19c中已成为默认和推荐架构：

┌─────────────────── CDB (Container Database) ────────────────────┐
│ │
│ ┌───────────┐ ┌──────────┐ ┌─────────┐ ┌─────────┐ │
│ │ ROOT │ │ PDB$SEED│ │ PDB1 │ │ PDB2 │ … │
│ │ (CDB$ROOT)│ │ (模板库) │ │(业务库1)│ │(业务库2)│ │
│ └───────────┘ └──────────┘ └─────────┘ └─────────┘ │
│ │
│ 共享: SGA、后台进程、参数文件、控制文件、重做日志 │
│ 隔离: 数据文件、用户、表空间、应用对象 │
└─────────────────────────────────────────────────────────────────┘

核心概念：

• CDB（Container Database）：容器数据库，最外层容器
• CDB$ROOT：根容器，存储Oracle元数据
• PDB$SEED：种子PDB，创建新PDB的模板（只读）
• PDB（Pluggable Database）：可插拔数据库，承载实际业务

10.2 创建CDB与PDB

— 创建CDB（DBCA或手动）
CREATE DATABASE cdb1
USER SYS IDENTIFIED BY sys_pass
USER SYSTEM IDENTIFIED BY system_pass
ENABLE PLUGGABLE DATABASE
SEED FILE_NAME_CONVERT = ('/u01/oradata/', '/u01/oradata/cdb1/pdbseed/')
DATAFILE '/u01/oradata/cdb1/system01.dbf' SIZE 500M
SYSAUX DATAFILE '/u01/oradata/cdb1/sysaux01.dbf' SIZE 500M
UNDO TABLESPACE undotbs1 DATAFILE '/u01/oradata/cdb1/undotbs01.dbf' SIZE 200M;

— 创建PDB
CREATE PLUGGABLE DATABASE pdb_sales
ADMIN USER pdb_admin IDENTIFIED BY "Admin#2025"
FILE_NAME_CONVERT = ('/u01/oradata/cdb1/pdbseed/', '/u01/oradata/cdb1/pdb_sales/');

— 从现有PDB克隆
CREATE PLUGGABLE DATABASE pdb_sales_copy FROM pdb_sales
FILE_NAME_CONVERT = ('/u01/oradata/cdb1/pdb_sales/', '/u01/oradata/cdb1/pdb_sales_copy/');

— 打开PDB
ALTER PLUGGABLE DATABASE pdb_sales OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

— 保存PDB状态（重启后自动打开）
ALTER PLUGGABLE DATABASE pdb_sales SAVE STATE;

10.3 管理CDB与PDB

— 查看容器信息
SELECT con_id, name, open_mode, restricted FROM V$CONTAINERS;
SHOW PDBS;

— 切换容器
ALTER SESSION SET CONTAINER = pdb_sales;
ALTER SESSION SET CONTAINER = CDB$ROOT;

— 关闭PDB
ALTER PLUGGABLE DATABASE pdb_sales CLOSE;
ALTER PLUGGABLE DATABASE ALL CLOSE;

— 拔出PDB（Unplug）
ALTER PLUGGABLE DATABASE pdb_sales CLOSE;
ALTER PLUGGABLE DATABASE pdb_sales UNPLUG INTO '/tmp/pdb_sales.xml';

— 插入PDB（Plug）
CREATE PLUGGABLE DATABASE pdb_new
USING '/tmp/pdb_sales.xml'
FILE_NAME_CONVERT = ('/old_path/', '/new_path/');

— 删除PDB
DROP PLUGGABLE DATABASE pdb_sales INCLUDING DATAFILES;

— 资源管理
ALTER SYSTEM SET CPU_COUNT = 4 CONTAINER = pdb_sales;
ALTER PLUGGABLE DATABASE pdb_sales SET DEFAULT_TABLESPACE = tbs_pdb;

— 查看PDB资源使用
SELECT con_id, name, value FROM V$SYSTEM_PARAMETER WHERE name = 'cpu_count';

10.4 多租户数据库安全管理

— CDB级别用户（公共用户，以C##或c##开头）
CREATE USER c##global_admin IDENTIFIED BY "Global#2025" CONTAINER = ALL;
GRANT CREATE SESSION TO c##global_admin CONTAINER = ALL;

— PDB级别用户（本地用户）
ALTER SESSION SET CONTAINER = pdb_sales;
CREATE USER local_app IDENTIFIED BY "Local#2025";
GRANT CREATE SESSION, CREATE TABLE TO local_app;

— 公共角色
CREATE ROLE c##role_dba CONTAINER = ALL;
GRANT DBA TO c##role_dba CONTAINER = ALL;

— 权限继承
GRANT SELECT ANY TABLE TO c##global_admin CONTAINER = ALL;

— PDB中查看权限
ALTER SESSION SET CONTAINER = pdb_sales;
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'LOCAL_APP';

10.5 多租户数据库备份与恢复

# RMAN连接CDB
rman target /

# 备份整个CDB（包含所有PDB）
BACKUP DATABASE PLUS ARCHIVELOG;

# 备份单个PDB
BACKUP PLUGGABLE DATABASE pdb_sales;

# 备份PDB的表空间
BACKUP TABLESPACE pdb_sales:users;

# 恢复单个PDB
RUN {
ALTER PLUGGABLE DATABASE pdb_sales CLOSE;
RESTORE PLUGGABLE DATABASE pdb_sales;
RECOVER PLUGGABLE DATABASE pdb_sales;
ALTER PLUGGABLE DATABASE pdb_sales OPEN;
}

# PDB时间点恢复
RUN {
ALTER PLUGGABLE DATABASE pdb_sales CLOSE;
SET UNTIL TIME "TO_DATE('2025-06-15 12:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE PLUGGABLE DATABASE pdb_sales;
RECOVER PLUGGABLE DATABASE pdb_sales;
ALTER PLUGGABLE DATABASE pdb_sales OPEN RESETLOGS;
}

— PDB级别闪回
ALTER SESSION SET CONTAINER = pdb_sales;
FLASHBACK PLUGGABLE DATABASE pdb_sales TO TIMESTAMP
TO_TIMESTAMP('2025-06-15 08:00:00', 'YYYY-MM-DD HH24:MI:SS');

— Data Pump在PDB中操作
— 需连接PDB的服务名
expdp system/password@pdb_sales DIRECTORY=dp_dir DUMPFILE=pdb_export.dmp FULL=Y

总结与学习建议

📋 全书知识图谱

Oracle 19c 知识体系
├── 基础层
│ ├── 安装部署（第1章）
│ ├── 体系结构（第2章）
│ └── 网络配置（第1章）
├── 管理层
│ ├── 存储管理（第3章）
│ ├── 模式对象（第4章）
│ ├── 用户安全（第6章）
│ └── 多租户管理（第10章）
├── 开发层
│ ├── SQL操作（第5章）
│ └── PL/SQL开发（第7章）
└── 运维层
├── 备份恢复（第8章）
└── 闪回技术（第9章）

🎯 学习路径建议

⚡ 关键记忆点

📝 写在最后：Oracle 19c作为长期支持版本，其知识体系庞大而精深。本文作为速通指南，旨在帮助读者快速建立完整的知识框架。实际工作中，每个主题都值得深入研究。建议结合Oracle官方文档（docs.oracle.com）和实际环境反复练习，方能真正掌握。

如果本文对你有帮助，欢迎 点赞、收藏、关注 三连支持！有问题欢迎评论区交流讨论。 🚀

本文基于《Oracle数据库实用教程(19C)》整理，仅供学习参考。

附录：Oracle 19c 核心知识可视化图表集

📎 附录说明：本附录为《万字书籍速通之〈Oracle数据库实用教程(19C)〉》的配套可视化资料。通过Mermaid流程图、架构图、时序图及汇总表格，将正文中抽象的概念具象化，帮助读者建立直观的认知模型。建议与正文对照阅读。

A.1 Oracle 19c 体系结构全景图

A.1.1 实例与数据库关系总览

#mermaid-svg-t0xhU9JaL4xm4Vpq{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-t0xhU9JaL4xm4Vpq .error-icon{fill:#552222;}#mermaid-svg-t0xhU9JaL4xm4Vpq .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-t0xhU9JaL4xm4Vpq .marker{fill:#333333;stroke:#333333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .marker.cross{stroke:#333333;}#mermaid-svg-t0xhU9JaL4xm4Vpq svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-t0xhU9JaL4xm4Vpq p{margin:0;}#mermaid-svg-t0xhU9JaL4xm4Vpq .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster-label text{fill:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster-label span{color:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster-label span p{background-color:transparent;}#mermaid-svg-t0xhU9JaL4xm4Vpq .label text,#mermaid-svg-t0xhU9JaL4xm4Vpq span{fill:#333;color:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .node rect,#mermaid-svg-t0xhU9JaL4xm4Vpq .node circle,#mermaid-svg-t0xhU9JaL4xm4Vpq .node ellipse,#mermaid-svg-t0xhU9JaL4xm4Vpq .node polygon,#mermaid-svg-t0xhU9JaL4xm4Vpq .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .rough-node .label text,#mermaid-svg-t0xhU9JaL4xm4Vpq .node .label text,#mermaid-svg-t0xhU9JaL4xm4Vpq .image-shape .label,#mermaid-svg-t0xhU9JaL4xm4Vpq .icon-shape .label{text-anchor:middle;}#mermaid-svg-t0xhU9JaL4xm4Vpq .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .rough-node .label,#mermaid-svg-t0xhU9JaL4xm4Vpq .node .label,#mermaid-svg-t0xhU9JaL4xm4Vpq .image-shape .label,#mermaid-svg-t0xhU9JaL4xm4Vpq .icon-shape .label{text-align:center;}#mermaid-svg-t0xhU9JaL4xm4Vpq .node.clickable{cursor:pointer;}#mermaid-svg-t0xhU9JaL4xm4Vpq .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .arrowheadPath{fill:#333333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-t0xhU9JaL4xm4Vpq .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-t0xhU9JaL4xm4Vpq .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-t0xhU9JaL4xm4Vpq .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster text{fill:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq .cluster span{color:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-t0xhU9JaL4xm4Vpq .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-t0xhU9JaL4xm4Vpq rect.text{fill:none;stroke-width:0;}#mermaid-svg-t0xhU9JaL4xm4Vpq .icon-shape,#mermaid-svg-t0xhU9JaL4xm4Vpq .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-t0xhU9JaL4xm4Vpq .icon-shape p,#mermaid-svg-t0xhU9JaL4xm4Vpq .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-t0xhU9JaL4xm4Vpq .icon-shape .label rect,#mermaid-svg-t0xhU9JaL4xm4Vpq .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-t0xhU9JaL4xm4Vpq .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-t0xhU9JaL4xm4Vpq .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-t0xhU9JaL4xm4Vpq :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

Oracle Database（物理文件）

Oracle Instance（内存+进程）

Server Processes

Background Processes

SGA Components

读写数据块

写入重做记录

持久化

刷脏块

更新文件头

更新文件头

归档已满日志

实例恢复

实例恢复

清理失败进程

清理失败进程

清理失败进程

AWR快照

私有内存

私有内存

私有内存

访问

访问

访问

SGA (System Global Area)

PGA (Program Global Area)

Database Buffer Cache

Shared Pool(Library Cache + Data Dict Cache)

Redo Log Buffer

Large Pool

Java Pool

Streams Pool

DBWRDatabase Writer

LGWRLog Writer

SMONSystem Monitor

PMONProcess Monitor

CKPTCheckpoint

ARCnArchiver

MMONManageability Monitor

RECORecoverer

Dedicated Server Process 1

Dedicated Server Process 2

Dedicated Server Process N

Data Files(表空间数据)

Control Files(数据库元数据)

Online Redo Log Files(重做日志)

Archive Log Files(归档日志)

Parameter File(SPFILE/PFILE)

Password File

A.1.2 SGA 内存组件交互流程

#mermaid-svg-jCoKq2ia2T71Ctlo{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-jCoKq2ia2T71Ctlo .error-icon{fill:#552222;}#mermaid-svg-jCoKq2ia2T71Ctlo .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-jCoKq2ia2T71Ctlo .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-jCoKq2ia2T71Ctlo .marker{fill:#333333;stroke:#333333;}#mermaid-svg-jCoKq2ia2T71Ctlo .marker.cross{stroke:#333333;}#mermaid-svg-jCoKq2ia2T71Ctlo svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-jCoKq2ia2T71Ctlo p{margin:0;}#mermaid-svg-jCoKq2ia2T71Ctlo .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster-label text{fill:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster-label span{color:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster-label span p{background-color:transparent;}#mermaid-svg-jCoKq2ia2T71Ctlo .label text,#mermaid-svg-jCoKq2ia2T71Ctlo span{fill:#333;color:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo .node rect,#mermaid-svg-jCoKq2ia2T71Ctlo .node circle,#mermaid-svg-jCoKq2ia2T71Ctlo .node ellipse,#mermaid-svg-jCoKq2ia2T71Ctlo .node polygon,#mermaid-svg-jCoKq2ia2T71Ctlo .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-jCoKq2ia2T71Ctlo .rough-node .label text,#mermaid-svg-jCoKq2ia2T71Ctlo .node .label text,#mermaid-svg-jCoKq2ia2T71Ctlo .image-shape .label,#mermaid-svg-jCoKq2ia2T71Ctlo .icon-shape .label{text-anchor:middle;}#mermaid-svg-jCoKq2ia2T71Ctlo .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-jCoKq2ia2T71Ctlo .rough-node .label,#mermaid-svg-jCoKq2ia2T71Ctlo .node .label,#mermaid-svg-jCoKq2ia2T71Ctlo .image-shape .label,#mermaid-svg-jCoKq2ia2T71Ctlo .icon-shape .label{text-align:center;}#mermaid-svg-jCoKq2ia2T71Ctlo .node.clickable{cursor:pointer;}#mermaid-svg-jCoKq2ia2T71Ctlo .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-jCoKq2ia2T71Ctlo .arrowheadPath{fill:#333333;}#mermaid-svg-jCoKq2ia2T71Ctlo .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-jCoKq2ia2T71Ctlo .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-jCoKq2ia2T71Ctlo .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-jCoKq2ia2T71Ctlo .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-jCoKq2ia2T71Ctlo .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-jCoKq2ia2T71Ctlo .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster text{fill:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo .cluster span{color:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-jCoKq2ia2T71Ctlo .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-jCoKq2ia2T71Ctlo rect.text{fill:none;stroke-width:0;}#mermaid-svg-jCoKq2ia2T71Ctlo .icon-shape,#mermaid-svg-jCoKq2ia2T71Ctlo .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-jCoKq2ia2T71Ctlo .icon-shape p,#mermaid-svg-jCoKq2ia2T71Ctlo .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-jCoKq2ia2T71Ctlo .icon-shape .label rect,#mermaid-svg-jCoKq2ia2T71Ctlo .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-jCoKq2ia2T71Ctlo .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-jCoKq2ia2T71Ctlo .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-jCoKq2ia2T71Ctlo :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

命中缓存

未命中

读取数据

Cache Hit

Cache Miss

加载数据块

产生变更

LGWR写入

DBWR定期刷盘

客户端SQL请求

解析阶段(Shared Pool)

执行阶段

硬解析(生成执行计划)

Database Buffer Cache

返回结果

磁盘I/O(Data Files)

Redo Log Buffer

Online Redo Log

Data Files

A.2 SQL语句处理全流程

A.2.1 SELECT 语句完整生命周期

Redo Log BufferData FilesBuffer CacheShared PoolServer ProcessClientRedo Log BufferData FilesBuffer CacheShared PoolServer ProcessClient#mermaid-svg-AU28f0R6GGeGMN0J{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-AU28f0R6GGeGMN0J .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-AU28f0R6GGeGMN0J .error-icon{fill:#552222;}#mermaid-svg-AU28f0R6GGeGMN0J .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-AU28f0R6GGeGMN0J .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-AU28f0R6GGeGMN0J .marker{fill:#333333;stroke:#333333;}#mermaid-svg-AU28f0R6GGeGMN0J .marker.cross{stroke:#333333;}#mermaid-svg-AU28f0R6GGeGMN0J svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-AU28f0R6GGeGMN0J p{margin:0;}#mermaid-svg-AU28f0R6GGeGMN0J .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-AU28f0R6GGeGMN0J text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-AU28f0R6GGeGMN0J .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-AU28f0R6GGeGMN0J .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-AU28f0R6GGeGMN0J .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-AU28f0R6GGeGMN0J .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-AU28f0R6GGeGMN0J #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-AU28f0R6GGeGMN0J .sequenceNumber{fill:white;}#mermaid-svg-AU28f0R6GGeGMN0J #sequencenumber{fill:#333;}#mermaid-svg-AU28f0R6GGeGMN0J #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-AU28f0R6GGeGMN0J .messageText{fill:#333;stroke:none;}#mermaid-svg-AU28f0R6GGeGMN0J .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-AU28f0R6GGeGMN0J .labelText,#mermaid-svg-AU28f0R6GGeGMN0J .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-AU28f0R6GGeGMN0J .loopText,#mermaid-svg-AU28f0R6GGeGMN0J .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-AU28f0R6GGeGMN0J .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-AU28f0R6GGeGMN0J .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-AU28f0R6GGeGMN0J .noteText,#mermaid-svg-AU28f0R6GGeGMN0J .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-AU28f0R6GGeGMN0J .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-AU28f0R6GGeGMN0J .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-AU28f0R6GGeGMN0J .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-AU28f0R6GGeGMN0J .actorPopupMenu{position:absolute;}#mermaid-svg-AU28f0R6GGeGMN0J .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-AU28f0R6GGeGMN0J .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-AU28f0R6GGeGMN0J .actor-man circle,#mermaid-svg-AU28f0R6GGeGMN0J line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-AU28f0R6GGeGMN0J :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}验证表/列是否存在CBO评估多种执行计划选择最优路径alt[Soft Parse (缓存命中)][Hard Parse (缓存未命中)]alt[Buffer Cache Hit][Buffer Cache Miss]可能多次Fetch直到全部返回发送SQL语句1. 语法检查(Syntax Check)语法正确2. 语义检查(Semantic Check)语义正确3. 权限检查(Permission Check)权限通过4. 查找共享池(Library Cache)找到已有执行计划5. 查询优化器(Optimizer)6. 缓存执行计划7. 行源生成(Row Source Generation)8. 执行(Fetch数据块)直接从内存返回物理读取加载数据块从缓存返回9. 逐行返回结果集(Fetch)

A.2.2 DML (INSERT/UPDATE/DELETE) 处理流程

#mermaid-svg-MP9b6xQIU6cVEASt{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-MP9b6xQIU6cVEASt .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-MP9b6xQIU6cVEASt .error-icon{fill:#552222;}#mermaid-svg-MP9b6xQIU6cVEASt .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-MP9b6xQIU6cVEASt .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-MP9b6xQIU6cVEASt .marker{fill:#333333;stroke:#333333;}#mermaid-svg-MP9b6xQIU6cVEASt .marker.cross{stroke:#333333;}#mermaid-svg-MP9b6xQIU6cVEASt svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-MP9b6xQIU6cVEASt p{margin:0;}#mermaid-svg-MP9b6xQIU6cVEASt .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-MP9b6xQIU6cVEASt .cluster-label text{fill:#333;}#mermaid-svg-MP9b6xQIU6cVEASt .cluster-label span{color:#333;}#mermaid-svg-MP9b6xQIU6cVEASt .cluster-label span p{background-color:transparent;}#mermaid-svg-MP9b6xQIU6cVEASt .label text,#mermaid-svg-MP9b6xQIU6cVEASt span{fill:#333;color:#333;}#mermaid-svg-MP9b6xQIU6cVEASt .node rect,#mermaid-svg-MP9b6xQIU6cVEASt .node circle,#mermaid-svg-MP9b6xQIU6cVEASt .node ellipse,#mermaid-svg-MP9b6xQIU6cVEASt .node polygon,#mermaid-svg-MP9b6xQIU6cVEASt .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-MP9b6xQIU6cVEASt .rough-node .label text,#mermaid-svg-MP9b6xQIU6cVEASt .node .label text,#mermaid-svg-MP9b6xQIU6cVEASt .image-shape .label,#mermaid-svg-MP9b6xQIU6cVEASt .icon-shape .label{text-anchor:middle;}#mermaid-svg-MP9b6xQIU6cVEASt .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-MP9b6xQIU6cVEASt .rough-node .label,#mermaid-svg-MP9b6xQIU6cVEASt .node .label,#mermaid-svg-MP9b6xQIU6cVEASt .image-shape .label,#mermaid-svg-MP9b6xQIU6cVEASt .icon-shape .label{text-align:center;}#mermaid-svg-MP9b6xQIU6cVEASt .node.clickable{cursor:pointer;}#mermaid-svg-MP9b6xQIU6cVEASt .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-MP9b6xQIU6cVEASt .arrowheadPath{fill:#333333;}#mermaid-svg-MP9b6xQIU6cVEASt .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-MP9b6xQIU6cVEASt .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-MP9b6xQIU6cVEASt .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-MP9b6xQIU6cVEASt .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-MP9b6xQIU6cVEASt .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-MP9b6xQIU6cVEASt .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-MP9b6xQIU6cVEASt .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-MP9b6xQIU6cVEASt .cluster text{fill:#333;}#mermaid-svg-MP9b6xQIU6cVEASt .cluster span{color:#333;}#mermaid-svg-MP9b6xQIU6cVEASt div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-MP9b6xQIU6cVEASt .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-MP9b6xQIU6cVEASt rect.text{fill:none;stroke-width:0;}#mermaid-svg-MP9b6xQIU6cVEASt .icon-shape,#mermaid-svg-MP9b6xQIU6cVEASt .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-MP9b6xQIU6cVEASt .icon-shape p,#mermaid-svg-MP9b6xQIU6cVEASt .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-MP9b6xQIU6cVEASt .icon-shape .label rect,#mermaid-svg-MP9b6xQIU6cVEASt .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-MP9b6xQIU6cVEASt .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-MP9b6xQIU6cVEASt .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-MP9b6xQIU6cVEASt :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

用户发出COMMIT

用户发出ROLLBACK

实例崩溃

DML语句提交

解析 & 权限检查

获取数据块到Buffer Cache

生成UNDO记录(修改前的旧值)

修改Buffer Cache中的数据块(标记为Dirty Buffer)

生成REDO记录写入Redo Log Buffer

对修改的行加锁

等待COMMIT?

LGWR将Redo Buffer写入Online Redo Log

事务提交成功(数据已持久化到日志)

DBWR异步将脏块写入Data Files

使用UNDO记录回滚修改

事务回滚完成

SMON实例恢复:前滚(Redo)+回滚(Undo)

结束

A.3 存储结构层次关系图

A.3.1 逻辑存储 vs 物理存储映射

#mermaid-svg-LgM1tUxc1BN1LHn7{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-LgM1tUxc1BN1LHn7 .error-icon{fill:#552222;}#mermaid-svg-LgM1tUxc1BN1LHn7 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-LgM1tUxc1BN1LHn7 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .marker.cross{stroke:#333333;}#mermaid-svg-LgM1tUxc1BN1LHn7 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-LgM1tUxc1BN1LHn7 p{margin:0;}#mermaid-svg-LgM1tUxc1BN1LHn7 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster-label text{fill:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster-label span{color:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster-label span p{background-color:transparent;}#mermaid-svg-LgM1tUxc1BN1LHn7 .label text,#mermaid-svg-LgM1tUxc1BN1LHn7 span{fill:#333;color:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .node rect,#mermaid-svg-LgM1tUxc1BN1LHn7 .node circle,#mermaid-svg-LgM1tUxc1BN1LHn7 .node ellipse,#mermaid-svg-LgM1tUxc1BN1LHn7 .node polygon,#mermaid-svg-LgM1tUxc1BN1LHn7 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .rough-node .label text,#mermaid-svg-LgM1tUxc1BN1LHn7 .node .label text,#mermaid-svg-LgM1tUxc1BN1LHn7 .image-shape .label,#mermaid-svg-LgM1tUxc1BN1LHn7 .icon-shape .label{text-anchor:middle;}#mermaid-svg-LgM1tUxc1BN1LHn7 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .rough-node .label,#mermaid-svg-LgM1tUxc1BN1LHn7 .node .label,#mermaid-svg-LgM1tUxc1BN1LHn7 .image-shape .label,#mermaid-svg-LgM1tUxc1BN1LHn7 .icon-shape .label{text-align:center;}#mermaid-svg-LgM1tUxc1BN1LHn7 .node.clickable{cursor:pointer;}#mermaid-svg-LgM1tUxc1BN1LHn7 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .arrowheadPath{fill:#333333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-LgM1tUxc1BN1LHn7 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-LgM1tUxc1BN1LHn7 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-LgM1tUxc1BN1LHn7 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster text{fill:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 .cluster span{color:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-LgM1tUxc1BN1LHn7 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-LgM1tUxc1BN1LHn7 rect.text{fill:none;stroke-width:0;}#mermaid-svg-LgM1tUxc1BN1LHn7 .icon-shape,#mermaid-svg-LgM1tUxc1BN1LHn7 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-LgM1tUxc1BN1LHn7 .icon-shape p,#mermaid-svg-LgM1tUxc1BN1LHn7 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-LgM1tUxc1BN1LHn7 .icon-shape .label rect,#mermaid-svg-LgM1tUxc1BN1LHn7 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-LgM1tUxc1BN1LHn7 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-LgM1tUxc1BN1LHn7 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-LgM1tUxc1BN1LHn7 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

物理存储结构

逻辑存储结构

包含

包含

包含

Database

Tablespace: USERS

Tablespace: TEMP

Tablespace: UNDOTBS1

Segment: EMPLOYEES Table

Segment: IDX_EMP_NAME Index

Segment: Temporary Segment

Segment: Undo Segment

Extent 1 (64KB)

Extent 2 (64KB)

Extent 3 (1MB)

Block 1 (8KB)

Block 2 (8KB)

Block N (8KB)

users01.dbf (500MB)

temp01.dbf (2GB)

undotbs01.dbf (2GB)

control01.ctl

redo01.log

redo02.log

A.3.2 表空间类型与用途对照

#mermaid-svg-HOFy50rSeqD3pFXB{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-HOFy50rSeqD3pFXB .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-HOFy50rSeqD3pFXB .error-icon{fill:#552222;}#mermaid-svg-HOFy50rSeqD3pFXB .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-HOFy50rSeqD3pFXB .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-HOFy50rSeqD3pFXB .marker{fill:#333333;stroke:#333333;}#mermaid-svg-HOFy50rSeqD3pFXB .marker.cross{stroke:#333333;}#mermaid-svg-HOFy50rSeqD3pFXB svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-HOFy50rSeqD3pFXB p{margin:0;}#mermaid-svg-HOFy50rSeqD3pFXB .edge{stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .section–1 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section–1 path,#mermaid-svg-HOFy50rSeqD3pFXB .section–1 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section–1 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section–1 path{fill:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section–1 text{fill:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon–1{font-size:40px;color:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge–1{stroke:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth–1{stroke-width:17;}#mermaid-svg-HOFy50rSeqD3pFXB .section–1 line{stroke:hsl(60, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-0 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-0 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-0 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-0 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-0 path{fill:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-0 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-0{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-0{stroke:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-0{stroke-width:14;}#mermaid-svg-HOFy50rSeqD3pFXB .section-0 line{stroke:hsl(240, 100%, 83.5294117647%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-1 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-1 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-1 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-1 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-1 path{fill:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-1 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-1{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-1{stroke:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-1{stroke-width:11;}#mermaid-svg-HOFy50rSeqD3pFXB .section-1 line{stroke:hsl(260, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-2 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-2 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-2 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-2 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-2 path{fill:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-2 text{fill:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-2{font-size:40px;color:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-2{stroke:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-2{stroke-width:8;}#mermaid-svg-HOFy50rSeqD3pFXB .section-2 line{stroke:hsl(90, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-3 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-3 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-3 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-3 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-3 path{fill:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-3 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-3{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-3{stroke:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-3{stroke-width:5;}#mermaid-svg-HOFy50rSeqD3pFXB .section-3 line{stroke:hsl(120, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-4 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-4 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-4 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-4 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-4 path{fill:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-4 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-4{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-4{stroke:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-4{stroke-width:2;}#mermaid-svg-HOFy50rSeqD3pFXB .section-4 line{stroke:hsl(150, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-5 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-5 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-5 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-5 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-5 path{fill:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-5 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-5{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-5{stroke:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-5{stroke-width:-1;}#mermaid-svg-HOFy50rSeqD3pFXB .section-5 line{stroke:hsl(180, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-6 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-6 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-6 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-6 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-6 path{fill:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-6 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-6{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-6{stroke:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-6{stroke-width:-4;}#mermaid-svg-HOFy50rSeqD3pFXB .section-6 line{stroke:hsl(210, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-7 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-7 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-7 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-7 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-7 path{fill:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-7 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-7{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-7{stroke:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-7{stroke-width:-7;}#mermaid-svg-HOFy50rSeqD3pFXB .section-7 line{stroke:hsl(270, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-8 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-8 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-8 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-8 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-8 path{fill:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-8 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-8{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-8{stroke:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-8{stroke-width:-10;}#mermaid-svg-HOFy50rSeqD3pFXB .section-8 line{stroke:hsl(330, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-9 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-9 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-9 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-9 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-9 path{fill:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-9 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-9{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-9{stroke:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-9{stroke-width:-13;}#mermaid-svg-HOFy50rSeqD3pFXB .section-9 line{stroke:hsl(0, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-10 rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-10 path,#mermaid-svg-HOFy50rSeqD3pFXB .section-10 circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-10 polygon,#mermaid-svg-HOFy50rSeqD3pFXB .section-10 path{fill:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-10 text{fill:black;}#mermaid-svg-HOFy50rSeqD3pFXB .node-icon-10{font-size:40px;color:black;}#mermaid-svg-HOFy50rSeqD3pFXB .section-edge-10{stroke:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .edge-depth-10{stroke-width:-16;}#mermaid-svg-HOFy50rSeqD3pFXB .section-10 line{stroke:hsl(30, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled,#mermaid-svg-HOFy50rSeqD3pFXB .disabled circle,#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:lightgray;}#mermaid-svg-HOFy50rSeqD3pFXB .disabled text{fill:#efefef;}#mermaid-svg-HOFy50rSeqD3pFXB .section-root rect,#mermaid-svg-HOFy50rSeqD3pFXB .section-root path,#mermaid-svg-HOFy50rSeqD3pFXB .section-root circle,#mermaid-svg-HOFy50rSeqD3pFXB .section-root polygon{fill:hsl(240, 100%, 46.2745098039%);}#mermaid-svg-HOFy50rSeqD3pFXB .section-root text{fill:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .section-root span{color:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .section-2 span{color:#ffffff;}#mermaid-svg-HOFy50rSeqD3pFXB .icon-container{height:100%;display:flex;justify-content:center;align-items:center;}#mermaid-svg-HOFy50rSeqD3pFXB .edge{fill:none;}#mermaid-svg-HOFy50rSeqD3pFXB .mindmap-node-label{dy:1em;alignment-baseline:middle;text-anchor:middle;dominant-baseline:middle;text-align:center;}#mermaid-svg-HOFy50rSeqD3pFXB :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

表空间类型

永久表空间 Permanent

用户数据表

索引

分区数据

LOB数据

临时表空间 Temporary

排序操作 ORDER BY

哈希连接 Hash Join

全局临时表

不能存储永久对象

还原表空间 Undo

事务回滚

读一致性 CR

闪回查询

每个实例一个Active Undo

SYSTEM表空间

数据字典

SYS用户对象

不可删除/离线

SYSAUX表空间

AWR/ADDM数据

OEM Express

辅助组件数据

A.4 数据库启动与关闭状态机

A.4.1 启动阶段状态转换

#mermaid-svg-UidJt7z6Hfc9siYW{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-UidJt7z6Hfc9siYW .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-UidJt7z6Hfc9siYW .error-icon{fill:#552222;}#mermaid-svg-UidJt7z6Hfc9siYW .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-UidJt7z6Hfc9siYW .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-UidJt7z6Hfc9siYW .marker{fill:#333333;stroke:#333333;}#mermaid-svg-UidJt7z6Hfc9siYW .marker.cross{stroke:#333333;}#mermaid-svg-UidJt7z6Hfc9siYW svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-UidJt7z6Hfc9siYW p{margin:0;}#mermaid-svg-UidJt7z6Hfc9siYW .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-UidJt7z6Hfc9siYW .cluster-label text{fill:#333;}#mermaid-svg-UidJt7z6Hfc9siYW .cluster-label span{color:#333;}#mermaid-svg-UidJt7z6Hfc9siYW .cluster-label span p{background-color:transparent;}#mermaid-svg-UidJt7z6Hfc9siYW .label text,#mermaid-svg-UidJt7z6Hfc9siYW span{fill:#333;color:#333;}#mermaid-svg-UidJt7z6Hfc9siYW .node rect,#mermaid-svg-UidJt7z6Hfc9siYW .node circle,#mermaid-svg-UidJt7z6Hfc9siYW .node ellipse,#mermaid-svg-UidJt7z6Hfc9siYW .node polygon,#mermaid-svg-UidJt7z6Hfc9siYW .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-UidJt7z6Hfc9siYW .rough-node .label text,#mermaid-svg-UidJt7z6Hfc9siYW .node .label text,#mermaid-svg-UidJt7z6Hfc9siYW .image-shape .label,#mermaid-svg-UidJt7z6Hfc9siYW .icon-shape .label{text-anchor:middle;}#mermaid-svg-UidJt7z6Hfc9siYW .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-UidJt7z6Hfc9siYW .rough-node .label,#mermaid-svg-UidJt7z6Hfc9siYW .node .label,#mermaid-svg-UidJt7z6Hfc9siYW .image-shape .label,#mermaid-svg-UidJt7z6Hfc9siYW .icon-shape .label{text-align:center;}#mermaid-svg-UidJt7z6Hfc9siYW .node.clickable{cursor:pointer;}#mermaid-svg-UidJt7z6Hfc9siYW .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-UidJt7z6Hfc9siYW .arrowheadPath{fill:#333333;}#mermaid-svg-UidJt7z6Hfc9siYW .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-UidJt7z6Hfc9siYW .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-UidJt7z6Hfc9siYW .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-UidJt7z6Hfc9siYW .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-UidJt7z6Hfc9siYW .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-UidJt7z6Hfc9siYW .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-UidJt7z6Hfc9siYW .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-UidJt7z6Hfc9siYW .cluster text{fill:#333;}#mermaid-svg-UidJt7z6Hfc9siYW .cluster span{color:#333;}#mermaid-svg-UidJt7z6Hfc9siYW div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-UidJt7z6Hfc9siYW .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-UidJt7z6Hfc9siYW rect.text{fill:none;stroke-width:0;}#mermaid-svg-UidJt7z6Hfc9siYW .icon-shape,#mermaid-svg-UidJt7z6Hfc9siYW .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-UidJt7z6Hfc9siYW .icon-shape p,#mermaid-svg-UidJt7z6Hfc9siYW .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-UidJt7z6Hfc9siYW .icon-shape .label rect,#mermaid-svg-UidJt7z6Hfc9siYW .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-UidJt7z6Hfc9siYW .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-UidJt7z6Hfc9siYW .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-UidJt7z6Hfc9siYW :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

STARTUP NOMOUNT

ALTER DATABASE MOUNT

ALTER DATABASE OPEN

STARTUP (一步到位)

STARTUP

STARTUP RESTRICT

STARTUP FORCE

FORCE 说明

= SHUTDOWN ABORT + STARTUP

RESTRICTED 说明

仅RESTRICTED SESSION权限用户可连接

OPEN 说明

✅ 打开数据文件和重做日志

✅ SMON执行实例恢复(如需)

✅ 数据库对外可用

MOUNT 说明

✅ 打开控制文件

✅ 验证数据文件/日志文件

❌ 未打开数据文件

🔧 可执行: 重命名文件、启用归档、完全恢复

NOMOUNT 说明

✅ 读取参数文件

✅ 分配SGA

✅ 启动后台进程

❌ 未打开控制文件

数据库关闭

NOMOUNT

MOUNT

OPEN

RESTRICTED

FORCE

A.4.2 关闭模式对比

#mermaid-svg-dgwPz7X5Xl3UBJ1l{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .error-icon{fill:#552222;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .marker{fill:#333333;stroke:#333333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .marker.cross{stroke:#333333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l p{margin:0;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster-label text{fill:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster-label span{color:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster-label span p{background-color:transparent;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .label text,#mermaid-svg-dgwPz7X5Xl3UBJ1l span{fill:#333;color:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .node rect,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node circle,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node ellipse,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node polygon,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .rough-node .label text,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node .label text,#mermaid-svg-dgwPz7X5Xl3UBJ1l .image-shape .label,#mermaid-svg-dgwPz7X5Xl3UBJ1l .icon-shape .label{text-anchor:middle;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .rough-node .label,#mermaid-svg-dgwPz7X5Xl3UBJ1l .node .label,#mermaid-svg-dgwPz7X5Xl3UBJ1l .image-shape .label,#mermaid-svg-dgwPz7X5Xl3UBJ1l .icon-shape .label{text-align:center;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .node.clickable{cursor:pointer;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .arrowheadPath{fill:#333333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-dgwPz7X5Xl3UBJ1l .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-dgwPz7X5Xl3UBJ1l .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster text{fill:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .cluster span{color:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-dgwPz7X5Xl3UBJ1l rect.text{fill:none;stroke-width:0;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .icon-shape,#mermaid-svg-dgwPz7X5Xl3UBJ1l .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .icon-shape p,#mermaid-svg-dgwPz7X5Xl3UBJ1l .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .icon-shape .label rect,#mermaid-svg-dgwPz7X5Xl3UBJ1l .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-dgwPz7X5Xl3UBJ1l .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-dgwPz7X5Xl3UBJ1l .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-dgwPz7X5Xl3UBJ1l :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

SHUTDOWN ABORT ⚠️紧急

立即终止所有进程

不回滚、不等待

关闭数据库

⚠️下次启动需实例恢复

SHUTDOWN TRANSACTIONAL

禁止新连接

禁止新事务

等待活动事务完成

断开所有会话

关闭数据库

无需实例恢复

SHUTDOWN IMMEDIATE ⭐推荐

禁止新连接

回滚未提交事务

断开所有会话

关闭数据库

无需实例恢复

SHUTDOWN NORMAL

禁止新连接

等待所有用户主动断开

关闭数据库

无需实例恢复

A.5 备份恢复技术决策树

A.5.1 备份策略选择

#mermaid-svg-QIY8pK2SKF1bg3yD{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-QIY8pK2SKF1bg3yD .error-icon{fill:#552222;}#mermaid-svg-QIY8pK2SKF1bg3yD .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-QIY8pK2SKF1bg3yD .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-QIY8pK2SKF1bg3yD .marker{fill:#333333;stroke:#333333;}#mermaid-svg-QIY8pK2SKF1bg3yD .marker.cross{stroke:#333333;}#mermaid-svg-QIY8pK2SKF1bg3yD svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-QIY8pK2SKF1bg3yD p{margin:0;}#mermaid-svg-QIY8pK2SKF1bg3yD .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster-label text{fill:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster-label span{color:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster-label span p{background-color:transparent;}#mermaid-svg-QIY8pK2SKF1bg3yD .label text,#mermaid-svg-QIY8pK2SKF1bg3yD span{fill:#333;color:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD .node rect,#mermaid-svg-QIY8pK2SKF1bg3yD .node circle,#mermaid-svg-QIY8pK2SKF1bg3yD .node ellipse,#mermaid-svg-QIY8pK2SKF1bg3yD .node polygon,#mermaid-svg-QIY8pK2SKF1bg3yD .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-QIY8pK2SKF1bg3yD .rough-node .label text,#mermaid-svg-QIY8pK2SKF1bg3yD .node .label text,#mermaid-svg-QIY8pK2SKF1bg3yD .image-shape .label,#mermaid-svg-QIY8pK2SKF1bg3yD .icon-shape .label{text-anchor:middle;}#mermaid-svg-QIY8pK2SKF1bg3yD .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-QIY8pK2SKF1bg3yD .rough-node .label,#mermaid-svg-QIY8pK2SKF1bg3yD .node .label,#mermaid-svg-QIY8pK2SKF1bg3yD .image-shape .label,#mermaid-svg-QIY8pK2SKF1bg3yD .icon-shape .label{text-align:center;}#mermaid-svg-QIY8pK2SKF1bg3yD .node.clickable{cursor:pointer;}#mermaid-svg-QIY8pK2SKF1bg3yD .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-QIY8pK2SKF1bg3yD .arrowheadPath{fill:#333333;}#mermaid-svg-QIY8pK2SKF1bg3yD .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-QIY8pK2SKF1bg3yD .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-QIY8pK2SKF1bg3yD .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-QIY8pK2SKF1bg3yD .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-QIY8pK2SKF1bg3yD .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-QIY8pK2SKF1bg3yD .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster text{fill:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD .cluster span{color:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-QIY8pK2SKF1bg3yD .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-QIY8pK2SKF1bg3yD rect.text{fill:none;stroke-width:0;}#mermaid-svg-QIY8pK2SKF1bg3yD .icon-shape,#mermaid-svg-QIY8pK2SKF1bg3yD .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-QIY8pK2SKF1bg3yD .icon-shape p,#mermaid-svg-QIY8pK2SKF1bg3yD .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-QIY8pK2SKF1bg3yD .icon-shape .label rect,#mermaid-svg-QIY8pK2SKF1bg3yD .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-QIY8pK2SKF1bg3yD .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-QIY8pK2SKF1bg3yD .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-QIY8pK2SKF1bg3yD :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

物理文件级

逻辑数据导出

数据库OPEN

数据库CLOSED

推荐

手动

首次/基线

日常

归档日志

全库

模式

表

传输表空间

需要备份什么?

热备还是冷备?

Data Pump (expdp)

热备份(必须归档模式)

冷备份(OS文件复制)

使用什么工具?

操作系统 cp/scp/rsync

RMAN✅ 增量备份✅ 压缩加密✅ 自动管理✅ 块级校验

用户管理备份⚠️ BEGIN/END BACKUP⚠️ 无增量⚠️ 易出错

备份级别?

LEVEL 0 全量增量

LEVEL 1 增量

BACKUP ARCHIVELOG

导出范围?

FULL=Y

SCHEMAS=hr,scott

TABLES=emp,dept

TRANSPORT_TABLESPACES

A.5.2 故障恢复决策流程

#mermaid-svg-DTPDulzMmLwJeq4b{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-DTPDulzMmLwJeq4b .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-DTPDulzMmLwJeq4b .error-icon{fill:#552222;}#mermaid-svg-DTPDulzMmLwJeq4b .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-DTPDulzMmLwJeq4b .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-DTPDulzMmLwJeq4b .marker{fill:#333333;stroke:#333333;}#mermaid-svg-DTPDulzMmLwJeq4b .marker.cross{stroke:#333333;}#mermaid-svg-DTPDulzMmLwJeq4b svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-DTPDulzMmLwJeq4b p{margin:0;}#mermaid-svg-DTPDulzMmLwJeq4b .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-DTPDulzMmLwJeq4b .cluster-label text{fill:#333;}#mermaid-svg-DTPDulzMmLwJeq4b .cluster-label span{color:#333;}#mermaid-svg-DTPDulzMmLwJeq4b .cluster-label span p{background-color:transparent;}#mermaid-svg-DTPDulzMmLwJeq4b .label text,#mermaid-svg-DTPDulzMmLwJeq4b span{fill:#333;color:#333;}#mermaid-svg-DTPDulzMmLwJeq4b .node rect,#mermaid-svg-DTPDulzMmLwJeq4b .node circle,#mermaid-svg-DTPDulzMmLwJeq4b .node ellipse,#mermaid-svg-DTPDulzMmLwJeq4b .node polygon,#mermaid-svg-DTPDulzMmLwJeq4b .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-DTPDulzMmLwJeq4b .rough-node .label text,#mermaid-svg-DTPDulzMmLwJeq4b .node .label text,#mermaid-svg-DTPDulzMmLwJeq4b .image-shape .label,#mermaid-svg-DTPDulzMmLwJeq4b .icon-shape .label{text-anchor:middle;}#mermaid-svg-DTPDulzMmLwJeq4b .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-DTPDulzMmLwJeq4b .rough-node .label,#mermaid-svg-DTPDulzMmLwJeq4b .node .label,#mermaid-svg-DTPDulzMmLwJeq4b .image-shape .label,#mermaid-svg-DTPDulzMmLwJeq4b .icon-shape .label{text-align:center;}#mermaid-svg-DTPDulzMmLwJeq4b .node.clickable{cursor:pointer;}#mermaid-svg-DTPDulzMmLwJeq4b .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-DTPDulzMmLwJeq4b .arrowheadPath{fill:#333333;}#mermaid-svg-DTPDulzMmLwJeq4b .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-DTPDulzMmLwJeq4b .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-DTPDulzMmLwJeq4b .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-DTPDulzMmLwJeq4b .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-DTPDulzMmLwJeq4b .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-DTPDulzMmLwJeq4b .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-DTPDulzMmLwJeq4b .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-DTPDulzMmLwJeq4b .cluster text{fill:#333;}#mermaid-svg-DTPDulzMmLwJeq4b .cluster span{color:#333;}#mermaid-svg-DTPDulzMmLwJeq4b div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-DTPDulzMmLwJeq4b .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-DTPDulzMmLwJeq4b rect.text{fill:none;stroke-width:0;}#mermaid-svg-DTPDulzMmLwJeq4b .icon-shape,#mermaid-svg-DTPDulzMmLwJeq4b .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-DTPDulzMmLwJeq4b .icon-shape p,#mermaid-svg-DTPDulzMmLwJeq4b .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-DTPDulzMmLwJeq4b .icon-shape .label rect,#mermaid-svg-DTPDulzMmLwJeq4b .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-DTPDulzMmLwJeq4b .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-DTPDulzMmLwJeq4b .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-DTPDulzMmLwJeq4b :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

实例崩溃/掉电

单个数据文件损坏

多个文件/表空间损坏

误操作(DROP/DELETE)

DROP TABLE

错误UPDATE/DELETE

大批量误操作

无闪回可用

整个数据库灾难

发现故障类型?

实例恢复(自动)

SMON自动执行:1. 前滚(Redo Applied)2. 打开数据库3. 后台回滚(Undo)

介质恢复-文件级

RMAN RESTORE DATAFILE

RMAN RECOVER DATAFILE

ALTER DATABASE DATAFILE ONLINE

介质恢复-表空间级

ALTER TABLESPACE OFFLINE

RMAN RESTORE TABLESPACE

RMAN RECOVER TABLESPACE

ALTER TABLESPACE ONLINE

误操作类型?

闪回删除FLASHBACK TABLE TO BEFORE DROP

闪回查询/闪回表

闪回数据库(需闪回日志)

时间点恢复(PITR)RESTORE + RECOVER UNTIL TIME

完全恢复 / Data Guard切换

A.6 闪回技术体系全景

A.6.1 闪回技术分层架构

#mermaid-svg-TWHlkCcvmgMO44z8{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-TWHlkCcvmgMO44z8 .error-icon{fill:#552222;}#mermaid-svg-TWHlkCcvmgMO44z8 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-TWHlkCcvmgMO44z8 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-TWHlkCcvmgMO44z8 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-TWHlkCcvmgMO44z8 .marker.cross{stroke:#333333;}#mermaid-svg-TWHlkCcvmgMO44z8 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-TWHlkCcvmgMO44z8 p{margin:0;}#mermaid-svg-TWHlkCcvmgMO44z8 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster-label text{fill:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster-label span{color:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster-label span p{background-color:transparent;}#mermaid-svg-TWHlkCcvmgMO44z8 .label text,#mermaid-svg-TWHlkCcvmgMO44z8 span{fill:#333;color:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 .node rect,#mermaid-svg-TWHlkCcvmgMO44z8 .node circle,#mermaid-svg-TWHlkCcvmgMO44z8 .node ellipse,#mermaid-svg-TWHlkCcvmgMO44z8 .node polygon,#mermaid-svg-TWHlkCcvmgMO44z8 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-TWHlkCcvmgMO44z8 .rough-node .label text,#mermaid-svg-TWHlkCcvmgMO44z8 .node .label text,#mermaid-svg-TWHlkCcvmgMO44z8 .image-shape .label,#mermaid-svg-TWHlkCcvmgMO44z8 .icon-shape .label{text-anchor:middle;}#mermaid-svg-TWHlkCcvmgMO44z8 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-TWHlkCcvmgMO44z8 .rough-node .label,#mermaid-svg-TWHlkCcvmgMO44z8 .node .label,#mermaid-svg-TWHlkCcvmgMO44z8 .image-shape .label,#mermaid-svg-TWHlkCcvmgMO44z8 .icon-shape .label{text-align:center;}#mermaid-svg-TWHlkCcvmgMO44z8 .node.clickable{cursor:pointer;}#mermaid-svg-TWHlkCcvmgMO44z8 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-TWHlkCcvmgMO44z8 .arrowheadPath{fill:#333333;}#mermaid-svg-TWHlkCcvmgMO44z8 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-TWHlkCcvmgMO44z8 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-TWHlkCcvmgMO44z8 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-TWHlkCcvmgMO44z8 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-TWHlkCcvmgMO44z8 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-TWHlkCcvmgMO44z8 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster text{fill:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 .cluster span{color:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-TWHlkCcvmgMO44z8 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-TWHlkCcvmgMO44z8 rect.text{fill:none;stroke-width:0;}#mermaid-svg-TWHlkCcvmgMO44z8 .icon-shape,#mermaid-svg-TWHlkCcvmgMO44z8 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-TWHlkCcvmgMO44z8 .icon-shape p,#mermaid-svg-TWHlkCcvmgMO44z8 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-TWHlkCcvmgMO44z8 .icon-shape .label rect,#mermaid-svg-TWHlkCcvmgMO44z8 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-TWHlkCcvmgMO44z8 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-TWHlkCcvmgMO44z8 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-TWHlkCcvmgMO44z8 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

底层依赖

数据库层闪回（DBA操作）

对象层闪回（DBA操作）

应用层闪回（无需DBA介入）

闪回查询AS OF TIMESTAMP/SCN

闪回版本查询VERSIONS BETWEEN

闪回事务查询FLASHBACK_TRANSACTION_QUERY

闪回表FLASHBACK TABLE

闪回删除FLASHBACK TABLE TO BEFORE DROP

闪回数据库FLASHBACK DATABASE

闪回数据归档Flashback Data Archive

UNDO Tablespace(保留时间: UNDO_RETENTION)

Recycle Bin(回收站)

Flashback Logs(Fast Recovery Area)

FDA Tablespace(独立历史存储)

A.6.2 闪回技术选型矩阵

A.7 多租户架构详解

A.7.1 CDB/PDB 架构层次图

#mermaid-svg-ltVew6mExAC5MXsp{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-ltVew6mExAC5MXsp .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-ltVew6mExAC5MXsp .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-ltVew6mExAC5MXsp .error-icon{fill:#552222;}#mermaid-svg-ltVew6mExAC5MXsp .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ltVew6mExAC5MXsp .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-ltVew6mExAC5MXsp .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ltVew6mExAC5MXsp .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ltVew6mExAC5MXsp .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-ltVew6mExAC5MXsp .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ltVew6mExAC5MXsp .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ltVew6mExAC5MXsp .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ltVew6mExAC5MXsp .marker.cross{stroke:#333333;}#mermaid-svg-ltVew6mExAC5MXsp svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ltVew6mExAC5MXsp p{margin:0;}#mermaid-svg-ltVew6mExAC5MXsp .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-ltVew6mExAC5MXsp .cluster-label text{fill:#333;}#mermaid-svg-ltVew6mExAC5MXsp .cluster-label span{color:#333;}#mermaid-svg-ltVew6mExAC5MXsp .cluster-label span p{background-color:transparent;}#mermaid-svg-ltVew6mExAC5MXsp .label text,#mermaid-svg-ltVew6mExAC5MXsp span{fill:#333;color:#333;}#mermaid-svg-ltVew6mExAC5MXsp .node rect,#mermaid-svg-ltVew6mExAC5MXsp .node circle,#mermaid-svg-ltVew6mExAC5MXsp .node ellipse,#mermaid-svg-ltVew6mExAC5MXsp .node polygon,#mermaid-svg-ltVew6mExAC5MXsp .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ltVew6mExAC5MXsp .rough-node .label text,#mermaid-svg-ltVew6mExAC5MXsp .node .label text,#mermaid-svg-ltVew6mExAC5MXsp .image-shape .label,#mermaid-svg-ltVew6mExAC5MXsp .icon-shape .label{text-anchor:middle;}#mermaid-svg-ltVew6mExAC5MXsp .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-ltVew6mExAC5MXsp .rough-node .label,#mermaid-svg-ltVew6mExAC5MXsp .node .label,#mermaid-svg-ltVew6mExAC5MXsp .image-shape .label,#mermaid-svg-ltVew6mExAC5MXsp .icon-shape .label{text-align:center;}#mermaid-svg-ltVew6mExAC5MXsp .node.clickable{cursor:pointer;}#mermaid-svg-ltVew6mExAC5MXsp .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-ltVew6mExAC5MXsp .arrowheadPath{fill:#333333;}#mermaid-svg-ltVew6mExAC5MXsp .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ltVew6mExAC5MXsp .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ltVew6mExAC5MXsp .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ltVew6mExAC5MXsp .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-ltVew6mExAC5MXsp .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ltVew6mExAC5MXsp .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-ltVew6mExAC5MXsp .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ltVew6mExAC5MXsp .cluster text{fill:#333;}#mermaid-svg-ltVew6mExAC5MXsp .cluster span{color:#333;}#mermaid-svg-ltVew6mExAC5MXsp div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ltVew6mExAC5MXsp .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-ltVew6mExAC5MXsp rect.text{fill:none;stroke-width:0;}#mermaid-svg-ltVew6mExAC5MXsp .icon-shape,#mermaid-svg-ltVew6mExAC5MXsp .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ltVew6mExAC5MXsp .icon-shape p,#mermaid-svg-ltVew6mExAC5MXsp .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-ltVew6mExAC5MXsp .icon-shape .label rect,#mermaid-svg-ltVew6mExAC5MXsp .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ltVew6mExAC5MXsp .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-ltVew6mExAC5MXsp .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-ltVew6mExAC5MXsp :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

共享资源 (所有PDB共用)

CDB (Container Database)

PDB3 (Testing)

PDB2 (Development)

PDB1 (Production)

克隆模板

克隆模板

克隆模板

CDB$ROOT━━━━━━━━━━━━• Oracle元数据• 公共用户(C##)• 公共角色• 系统参数

PDB$SEED━━━━━━━━━━━━• 只读模板• 创建新PDB的源• 不可修改

业务数据文件

本地用户: APP_USER

应用对象: 表/视图/过程

本地表空间

开发数据文件

本地用户: DEV_USER

开发对象

本地表空间

测试数据文件

本地用户: TEST_USER

测试对象

本地表空间

SGA (共享内存池)

后台进程 (DBWR/LGWR/SMON…)

Control Files

Online Redo Logs

SPFILE

Fast Recovery Area

A.7.2 PDB 生命周期操作流转

#mermaid-svg-agubpvC7NwO5Fv9g{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-agubpvC7NwO5Fv9g .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-agubpvC7NwO5Fv9g .error-icon{fill:#552222;}#mermaid-svg-agubpvC7NwO5Fv9g .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-agubpvC7NwO5Fv9g .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-agubpvC7NwO5Fv9g .marker{fill:#333333;stroke:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g .marker.cross{stroke:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-agubpvC7NwO5Fv9g p{margin:0;}#mermaid-svg-agubpvC7NwO5Fv9g defs #statediagram-barbEnd{fill:#333333;stroke:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g g.stateGroup text{fill:#9370DB;stroke:none;font-size:10px;}#mermaid-svg-agubpvC7NwO5Fv9g g.stateGroup text{fill:#333;stroke:none;font-size:10px;}#mermaid-svg-agubpvC7NwO5Fv9g g.stateGroup .state-title{font-weight:bolder;fill:#131300;}#mermaid-svg-agubpvC7NwO5Fv9g g.stateGroup rect{fill:#ECECFF;stroke:#9370DB;}#mermaid-svg-agubpvC7NwO5Fv9g g.stateGroup line{stroke:#333333;stroke-width:1;}#mermaid-svg-agubpvC7NwO5Fv9g .transition{stroke:#333333;stroke-width:1;fill:none;}#mermaid-svg-agubpvC7NwO5Fv9g .stateGroup .composit{fill:white;border-bottom:1px;}#mermaid-svg-agubpvC7NwO5Fv9g .stateGroup .alt-composit{fill:#e0e0e0;border-bottom:1px;}#mermaid-svg-agubpvC7NwO5Fv9g .state-note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-agubpvC7NwO5Fv9g .state-note text{fill:black;stroke:none;font-size:10px;}#mermaid-svg-agubpvC7NwO5Fv9g .stateLabel .box{stroke:none;stroke-width:0;fill:#ECECFF;opacity:0.5;}#mermaid-svg-agubpvC7NwO5Fv9g .edgeLabel .label rect{fill:#ECECFF;opacity:0.5;}#mermaid-svg-agubpvC7NwO5Fv9g .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-agubpvC7NwO5Fv9g .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-agubpvC7NwO5Fv9g .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-agubpvC7NwO5Fv9g .edgeLabel .label text{fill:#333;}#mermaid-svg-agubpvC7NwO5Fv9g .label div .edgeLabel{color:#333;}#mermaid-svg-agubpvC7NwO5Fv9g .stateLabel text{fill:#131300;font-size:10px;font-weight:bold;}#mermaid-svg-agubpvC7NwO5Fv9g .node circle.state-start{fill:#333333;stroke:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g .node .fork-join{fill:#333333;stroke:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g .node circle.state-end{fill:#9370DB;stroke:white;stroke-width:1.5;}#mermaid-svg-agubpvC7NwO5Fv9g .end-state-inner{fill:white;stroke-width:1.5;}#mermaid-svg-agubpvC7NwO5Fv9g .node rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-agubpvC7NwO5Fv9g .node polygon{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-agubpvC7NwO5Fv9g #statediagram-barbEnd{fill:#333333;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-cluster rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-agubpvC7NwO5Fv9g .cluster-label,#mermaid-svg-agubpvC7NwO5Fv9g .nodeLabel{color:#131300;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-cluster rect.outer{rx:5px;ry:5px;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-state .divider{stroke:#9370DB;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-state .title-state{rx:5px;ry:5px;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-cluster.statediagram-cluster .inner{fill:white;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-cluster.statediagram-cluster-alt .inner{fill:#f0f0f0;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-cluster .inner{rx:0;ry:0;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-state rect.basic{rx:5px;ry:5px;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-state rect.divider{stroke-dasharray:10,10;fill:#f0f0f0;}#mermaid-svg-agubpvC7NwO5Fv9g .note-edge{stroke-dasharray:5;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-note text{fill:black;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram-note .nodeLabel{color:black;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagram .edgeLabel{color:red;}#mermaid-svg-agubpvC7NwO5Fv9g #dependencyStart,#mermaid-svg-agubpvC7NwO5Fv9g #dependencyEnd{fill:#333333;stroke:#333333;stroke-width:1;}#mermaid-svg-agubpvC7NwO5Fv9g .statediagramTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-agubpvC7NwO5Fv9g :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

CREATE PLUGGABLE DATABASE

创建后默认CLOSED

CLOSE

UNPLUG INTO 'xml_path'

CREATE PLUGGABLE DATABASE … USING 'xml_path'

DROP PLUGGABLE DATABASE … INCLUDING DATAFILES

作为克隆源

克隆完成

DROP PLUGGABLE DATABASE … INCLUDING DATAFILES

ALTER PLUGGABLE DATABASE … OPEN

ALTER PLUGGABLE DATABASE … CLOSE

OPEN READ ONLY

OPEN READ WRITE

SAVE STATE

CREATED

CLOSED

OPEN

OPEN RESTRICT

OPEN

Normal

Restricted

READ_ONLY

UNPLUGGED

DELETED

CLONED_SOURCE

SAVED_STATE

CDB重启后PDB自动恢复到保存的状态

A.7.3 CDB vs 传统架构对比

A.8 PL/SQL 程序单元关系图

A.8.1 PL/SQL 程序单元层次

#mermaid-svg-Nxj0ZBZCMRk8WSOw{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .error-icon{fill:#552222;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .marker.cross{stroke:#333333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw p{margin:0;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster-label text{fill:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster-label span{color:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster-label span p{background-color:transparent;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .label text,#mermaid-svg-Nxj0ZBZCMRk8WSOw span{fill:#333;color:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .node rect,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node circle,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node ellipse,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node polygon,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .rough-node .label text,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node .label text,#mermaid-svg-Nxj0ZBZCMRk8WSOw .image-shape .label,#mermaid-svg-Nxj0ZBZCMRk8WSOw .icon-shape .label{text-anchor:middle;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .rough-node .label,#mermaid-svg-Nxj0ZBZCMRk8WSOw .node .label,#mermaid-svg-Nxj0ZBZCMRk8WSOw .image-shape .label,#mermaid-svg-Nxj0ZBZCMRk8WSOw .icon-shape .label{text-align:center;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .node.clickable{cursor:pointer;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .arrowheadPath{fill:#333333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-Nxj0ZBZCMRk8WSOw .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Nxj0ZBZCMRk8WSOw .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster text{fill:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .cluster span{color:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-Nxj0ZBZCMRk8WSOw rect.text{fill:none;stroke-width:0;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .icon-shape,#mermaid-svg-Nxj0ZBZCMRk8WSOw .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .icon-shape p,#mermaid-svg-Nxj0ZBZCMRk8WSOw .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .icon-shape .label rect,#mermaid-svg-Nxj0ZBZCMRk8WSOw .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Nxj0ZBZCMRk8WSOw .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-Nxj0ZBZCMRk8WSOw .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-Nxj0ZBZCMRk8WSOw :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

独立程序单元

Package (程序包)

私有组件 (仅包内可见)

公共组件 (对外可见)

实现

调用

调用

调用

调用

调用

调用

Package Specification(包规范 – 公共接口)

Package Body(包体 – 私有实现)

Public Procedure

Public Function

Public Type/Record

Public Constant

Public Cursor

Private Procedure

Private Function

Private Variable

Standalone Procedure

Standalone Function

Trigger

Anonymous Block

A.8.2 异常处理传播机制

#mermaid-svg-1zfG4CMKAfFu6IF7{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-1zfG4CMKAfFu6IF7 .error-icon{fill:#552222;}#mermaid-svg-1zfG4CMKAfFu6IF7 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-1zfG4CMKAfFu6IF7 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .marker.cross{stroke:#333333;}#mermaid-svg-1zfG4CMKAfFu6IF7 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-1zfG4CMKAfFu6IF7 p{margin:0;}#mermaid-svg-1zfG4CMKAfFu6IF7 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster-label text{fill:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster-label span{color:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster-label span p{background-color:transparent;}#mermaid-svg-1zfG4CMKAfFu6IF7 .label text,#mermaid-svg-1zfG4CMKAfFu6IF7 span{fill:#333;color:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .node rect,#mermaid-svg-1zfG4CMKAfFu6IF7 .node circle,#mermaid-svg-1zfG4CMKAfFu6IF7 .node ellipse,#mermaid-svg-1zfG4CMKAfFu6IF7 .node polygon,#mermaid-svg-1zfG4CMKAfFu6IF7 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .rough-node .label text,#mermaid-svg-1zfG4CMKAfFu6IF7 .node .label text,#mermaid-svg-1zfG4CMKAfFu6IF7 .image-shape .label,#mermaid-svg-1zfG4CMKAfFu6IF7 .icon-shape .label{text-anchor:middle;}#mermaid-svg-1zfG4CMKAfFu6IF7 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .rough-node .label,#mermaid-svg-1zfG4CMKAfFu6IF7 .node .label,#mermaid-svg-1zfG4CMKAfFu6IF7 .image-shape .label,#mermaid-svg-1zfG4CMKAfFu6IF7 .icon-shape .label{text-align:center;}#mermaid-svg-1zfG4CMKAfFu6IF7 .node.clickable{cursor:pointer;}#mermaid-svg-1zfG4CMKAfFu6IF7 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .arrowheadPath{fill:#333333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-1zfG4CMKAfFu6IF7 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-1zfG4CMKAfFu6IF7 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-1zfG4CMKAfFu6IF7 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster text{fill:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 .cluster span{color:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-1zfG4CMKAfFu6IF7 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-1zfG4CMKAfFu6IF7 rect.text{fill:none;stroke-width:0;}#mermaid-svg-1zfG4CMKAfFu6IF7 .icon-shape,#mermaid-svg-1zfG4CMKAfFu6IF7 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-1zfG4CMKAfFu6IF7 .icon-shape p,#mermaid-svg-1zfG4CMKAfFu6IF7 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-1zfG4CMKAfFu6IF7 .icon-shape .label rect,#mermaid-svg-1zfG4CMKAfFu6IF7 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-1zfG4CMKAfFu6IF7 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-1zfG4CMKAfFu6IF7 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-1zfG4CMKAfFu6IF7 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

是

是

否

是

否

否

是

是

否

是

否

否

是

否

运行时错误发生

内部块有EXCEPTION?

匹配到具体异常?

执行内部异常处理器

有WHEN OTHERS?

执行WHEN OTHERS

向外部块传播 ↑

向外部块传播 ↑

外部块有EXCEPTION?

匹配到具体异常?

执行外部异常处理器

有WHEN OTHERS?

执行WHEN OTHERS

继续向上传播 ↑

继续向上传播 ↑

到达最外层?

❌ 未处理异常返回错误给调用方

继续向更外层传播…

✅ 异常已处理继续执行后续代码

A.9 网络安全通信架构

A.9.1 Oracle Net Services 连接流程

Database InstanceListener (1521)DNS/TNSNames客户端Database InstanceListener (1521)DNS/TNSNames客户端#mermaid-svg-veC9UedKphVxecMa{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-veC9UedKphVxecMa .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-veC9UedKphVxecMa .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-veC9UedKphVxecMa .error-icon{fill:#552222;}#mermaid-svg-veC9UedKphVxecMa .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-veC9UedKphVxecMa .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-veC9UedKphVxecMa .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-veC9UedKphVxecMa .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-veC9UedKphVxecMa .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-veC9UedKphVxecMa .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-veC9UedKphVxecMa .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-veC9UedKphVxecMa .marker{fill:#333333;stroke:#333333;}#mermaid-svg-veC9UedKphVxecMa .marker.cross{stroke:#333333;}#mermaid-svg-veC9UedKphVxecMa svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-veC9UedKphVxecMa p{margin:0;}#mermaid-svg-veC9UedKphVxecMa .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-veC9UedKphVxecMa text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-veC9UedKphVxecMa .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-veC9UedKphVxecMa .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-veC9UedKphVxecMa .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-veC9UedKphVxecMa .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-veC9UedKphVxecMa #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-veC9UedKphVxecMa .sequenceNumber{fill:white;}#mermaid-svg-veC9UedKphVxecMa #sequencenumber{fill:#333;}#mermaid-svg-veC9UedKphVxecMa #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-veC9UedKphVxecMa .messageText{fill:#333;stroke:none;}#mermaid-svg-veC9UedKphVxecMa .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-veC9UedKphVxecMa .labelText,#mermaid-svg-veC9UedKphVxecMa .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-veC9UedKphVxecMa .loopText,#mermaid-svg-veC9UedKphVxecMa .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-veC9UedKphVxecMa .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-veC9UedKphVxecMa .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-veC9UedKphVxecMa .noteText,#mermaid-svg-veC9UedKphVxecMa .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-veC9UedKphVxecMa .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-veC9UedKphVxecMa .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-veC9UedKphVxecMa .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-veC9UedKphVxecMa .actorPopupMenu{position:absolute;}#mermaid-svg-veC9UedKphVxecMa .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-veC9UedKphVxecMa .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-veC9UedKphVxecMa .actor-man circle,#mermaid-svg-veC9UedKphVxecMa line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-veC9UedKphVxecMa :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}包含SERVICE_NAME/SIDPMON每60秒注册服务SID_LIST_LISTENER配置alt[动态注册 (PMON)][静态注册(listener.ora)]alt[Dedicated Server][Shared Server]loop[数据交互]1. 解析服务名 → HOST:PORT192.168.1.100:15212. 发送连接请求(TNS Connect Packet)3. 验证服务是否注册4. 派生/重定向服务器进程Fork专用服务器进程5a. 直接建立连接放入共享服务器队列5b. 调度器分配连接6. 认证(用户名/密码)7. 认证成功，会话建立SQL请求结果集断开连接PMON清理资源

A.9.2 监听器注册方式对比

#mermaid-svg-JI3X5HVmWy0LZsmt{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-JI3X5HVmWy0LZsmt .error-icon{fill:#552222;}#mermaid-svg-JI3X5HVmWy0LZsmt .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-JI3X5HVmWy0LZsmt .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-JI3X5HVmWy0LZsmt .marker{fill:#333333;stroke:#333333;}#mermaid-svg-JI3X5HVmWy0LZsmt .marker.cross{stroke:#333333;}#mermaid-svg-JI3X5HVmWy0LZsmt svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-JI3X5HVmWy0LZsmt p{margin:0;}#mermaid-svg-JI3X5HVmWy0LZsmt .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster-label text{fill:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster-label span{color:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster-label span p{background-color:transparent;}#mermaid-svg-JI3X5HVmWy0LZsmt .label text,#mermaid-svg-JI3X5HVmWy0LZsmt span{fill:#333;color:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt .node rect,#mermaid-svg-JI3X5HVmWy0LZsmt .node circle,#mermaid-svg-JI3X5HVmWy0LZsmt .node ellipse,#mermaid-svg-JI3X5HVmWy0LZsmt .node polygon,#mermaid-svg-JI3X5HVmWy0LZsmt .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-JI3X5HVmWy0LZsmt .rough-node .label text,#mermaid-svg-JI3X5HVmWy0LZsmt .node .label text,#mermaid-svg-JI3X5HVmWy0LZsmt .image-shape .label,#mermaid-svg-JI3X5HVmWy0LZsmt .icon-shape .label{text-anchor:middle;}#mermaid-svg-JI3X5HVmWy0LZsmt .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-JI3X5HVmWy0LZsmt .rough-node .label,#mermaid-svg-JI3X5HVmWy0LZsmt .node .label,#mermaid-svg-JI3X5HVmWy0LZsmt .image-shape .label,#mermaid-svg-JI3X5HVmWy0LZsmt .icon-shape .label{text-align:center;}#mermaid-svg-JI3X5HVmWy0LZsmt .node.clickable{cursor:pointer;}#mermaid-svg-JI3X5HVmWy0LZsmt .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-JI3X5HVmWy0LZsmt .arrowheadPath{fill:#333333;}#mermaid-svg-JI3X5HVmWy0LZsmt .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-JI3X5HVmWy0LZsmt .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-JI3X5HVmWy0LZsmt .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-JI3X5HVmWy0LZsmt .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-JI3X5HVmWy0LZsmt .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-JI3X5HVmWy0LZsmt .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster text{fill:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt .cluster span{color:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-JI3X5HVmWy0LZsmt .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-JI3X5HVmWy0LZsmt rect.text{fill:none;stroke-width:0;}#mermaid-svg-JI3X5HVmWy0LZsmt .icon-shape,#mermaid-svg-JI3X5HVmWy0LZsmt .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-JI3X5HVmWy0LZsmt .icon-shape p,#mermaid-svg-JI3X5HVmWy0LZsmt .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-JI3X5HVmWy0LZsmt .icon-shape .label rect,#mermaid-svg-JI3X5HVmWy0LZsmt .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-JI3X5HVmWy0LZsmt .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-JI3X5HVmWy0LZsmt .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-JI3X5HVmWy0LZsmt :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

静态注册

启动时读取

listener.oraSID_LIST_LISTENER

Listener

✅ 实例未启动也可注册✅ 适用于OEM/RMAN连接✅ 可注册非默认端口⚠️ 需手动维护配置

动态注册 (推荐)

每60秒

注册信息

PMON进程

Listener

✅ 自动维护✅ SERVICE_NAMES参数控制✅ 支持RAC负载均衡⚠️ 实例MOUNT后才注册

A.10 关键参数速查表

A.10.1 内存相关参数

A.10.2 安全与审计参数

A.10.3 闪回相关参数

A.11 常用数据字典视图速查

A.11.1 核心视图分类

#mermaid-svg-I4SUfhgCUzUL00QD{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-I4SUfhgCUzUL00QD .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-I4SUfhgCUzUL00QD .error-icon{fill:#552222;}#mermaid-svg-I4SUfhgCUzUL00QD .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-I4SUfhgCUzUL00QD .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-I4SUfhgCUzUL00QD .marker{fill:#333333;stroke:#333333;}#mermaid-svg-I4SUfhgCUzUL00QD .marker.cross{stroke:#333333;}#mermaid-svg-I4SUfhgCUzUL00QD svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-I4SUfhgCUzUL00QD p{margin:0;}#mermaid-svg-I4SUfhgCUzUL00QD .edge{stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .section–1 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section–1 path,#mermaid-svg-I4SUfhgCUzUL00QD .section–1 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section–1 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section–1 path{fill:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section–1 text{fill:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon–1{font-size:40px;color:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge–1{stroke:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth–1{stroke-width:17;}#mermaid-svg-I4SUfhgCUzUL00QD .section–1 line{stroke:hsl(60, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-0 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-0 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-0 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-0 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-0 path{fill:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-0 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-0{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-0{stroke:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-0{stroke-width:14;}#mermaid-svg-I4SUfhgCUzUL00QD .section-0 line{stroke:hsl(240, 100%, 83.5294117647%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-1 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-1 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-1 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-1 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-1 path{fill:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-1 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-1{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-1{stroke:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-1{stroke-width:11;}#mermaid-svg-I4SUfhgCUzUL00QD .section-1 line{stroke:hsl(260, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-2 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-2 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-2 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-2 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-2 path{fill:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-2 text{fill:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-2{font-size:40px;color:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-2{stroke:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-2{stroke-width:8;}#mermaid-svg-I4SUfhgCUzUL00QD .section-2 line{stroke:hsl(90, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-3 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-3 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-3 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-3 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-3 path{fill:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-3 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-3{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-3{stroke:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-3{stroke-width:5;}#mermaid-svg-I4SUfhgCUzUL00QD .section-3 line{stroke:hsl(120, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-4 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-4 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-4 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-4 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-4 path{fill:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-4 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-4{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-4{stroke:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-4{stroke-width:2;}#mermaid-svg-I4SUfhgCUzUL00QD .section-4 line{stroke:hsl(150, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-5 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-5 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-5 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-5 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-5 path{fill:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-5 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-5{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-5{stroke:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-5{stroke-width:-1;}#mermaid-svg-I4SUfhgCUzUL00QD .section-5 line{stroke:hsl(180, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-6 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-6 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-6 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-6 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-6 path{fill:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-6 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-6{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-6{stroke:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-6{stroke-width:-4;}#mermaid-svg-I4SUfhgCUzUL00QD .section-6 line{stroke:hsl(210, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-7 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-7 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-7 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-7 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-7 path{fill:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-7 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-7{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-7{stroke:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-7{stroke-width:-7;}#mermaid-svg-I4SUfhgCUzUL00QD .section-7 line{stroke:hsl(270, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-8 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-8 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-8 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-8 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-8 path{fill:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-8 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-8{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-8{stroke:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-8{stroke-width:-10;}#mermaid-svg-I4SUfhgCUzUL00QD .section-8 line{stroke:hsl(330, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-9 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-9 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-9 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-9 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-9 path{fill:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-9 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-9{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-9{stroke:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-9{stroke-width:-13;}#mermaid-svg-I4SUfhgCUzUL00QD .section-9 line{stroke:hsl(0, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-10 rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-10 path,#mermaid-svg-I4SUfhgCUzUL00QD .section-10 circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-10 polygon,#mermaid-svg-I4SUfhgCUzUL00QD .section-10 path{fill:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-10 text{fill:black;}#mermaid-svg-I4SUfhgCUzUL00QD .node-icon-10{font-size:40px;color:black;}#mermaid-svg-I4SUfhgCUzUL00QD .section-edge-10{stroke:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .edge-depth-10{stroke-width:-16;}#mermaid-svg-I4SUfhgCUzUL00QD .section-10 line{stroke:hsl(30, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled,#mermaid-svg-I4SUfhgCUzUL00QD .disabled circle,#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:lightgray;}#mermaid-svg-I4SUfhgCUzUL00QD .disabled text{fill:#efefef;}#mermaid-svg-I4SUfhgCUzUL00QD .section-root rect,#mermaid-svg-I4SUfhgCUzUL00QD .section-root path,#mermaid-svg-I4SUfhgCUzUL00QD .section-root circle,#mermaid-svg-I4SUfhgCUzUL00QD .section-root polygon{fill:hsl(240, 100%, 46.2745098039%);}#mermaid-svg-I4SUfhgCUzUL00QD .section-root text{fill:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .section-root span{color:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .section-2 span{color:#ffffff;}#mermaid-svg-I4SUfhgCUzUL00QD .icon-container{height:100%;display:flex;justify-content:center;align-items:center;}#mermaid-svg-I4SUfhgCUzUL00QD .edge{fill:none;}#mermaid-svg-I4SUfhgCUzUL00QD .mindmap-node-label{dy:1em;alignment-baseline:middle;text-anchor:middle;dominant-baseline:middle;text-align:center;}#mermaid-svg-I4SUfhgCUzUL00QD :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

数据字典视图

USER_系列

USER_TABLES

USER_INDEXES

USER_VIEWS

USER_SEQUENCES

USER_OBJECTS

USER_TAB_COLUMNS

USER_CONSTRAINTS

USER_SOURCE

USER_TRIGGERS

ALL_系列

ALL_TABLES

ALL_OBJECTS

ALL_TAB_PRIVS

ALL_SOURCE

ALL_VIEWS

DBA_系列

DBA_TABLES

DBA_USERS

DBA_TABLESPACES

DBA_DATA_FILES

DBA_SEGMENTS

DBA_ROLES

DBA_SYS_PRIVS

DBA_TAB_PRIVS

DBA_PROFILES

V$_动态视图

V$SESSION

V$SQL

V$LOCK

V$PARAMETER

V$SGA

V$DATABASE

V$CONTROLFILE

V$LOG

V$ARCHIVED_LOG

V$PROCESS

V$WAITSTAT

V$ACTIVE_SESSION_HISTORY

GV$_集群视图

GV$SESSION

GV$SQL

GV$INSTANCE

A.11.2 DBA高频查询速查表

📎 附录使用建议：