第01篇_Oracle

第01章_Oracle概述

第一节 Oracle简介

1. 什么是Oracle？

Oracle是美国甲骨文公司开发的一款关系型数据库管理系统（RDBMS），是市场上最流行的商业数据库之一。

第二节 安装部署

1. Windows版本安装

详情见“部署文档”目录下相关文档说明！

2. Linux版本安装

详情见“部署文档”目录下相关文档说明！

3. 配置文件

• listener.ora：监听器配置文件。监听器用于侦听客户端的连接请求以及建立客户端和服务器端连接通道，默认端口为1521。
• tnsnames.ora：网络服务名配置文件。配置客户端到服务器端的连接服务，包括客户端要连接到的服务器和数据库的配置信息。
• sqlnet.ora：连接属性配置文件。控制和管理Oracle连接的属性，根据参数作用的不同决定在 Client 端配置还是在 Server 端配置。

4. 监听程序

Oracle监听程序用于监听远程连接，在远程访问数据库时必不可少。

xxxxxxxxxx
9
1
# 查看监听状态
2
lsnrctl status 
3

4
# 启动监听程序
5
lsnrctl start 
6

7
# 停止监听程序
8
lsnrctl stop 
9

第三节 客户端

1. 命令行客户端

xxxxxxxxxx
46
1
# 用户登录
2
sqlplus 用户名/密码[ @服务名 | @IP:端口/实例名 ] [ as sysdba | as sysoper ]   
3
sqlplus / as sysdba                               # 操作系统用户登录默认数据库
4
sqlplus /nolog                                    # 不暴露密码的登陆方式SQL
5
sqlplus sys/123@ORCL01 as sysdba                  # 系统用户通过服务名连接
6
sqlplus sys/123@127.0.0.1:1521/ORCL01 as sysdba   # 系统用户通过连接字符串连接
7
sqlplus kfms/123@ORCL01                           # 普通用户通过服务名连接
8
sqlplus /nolog                                    # 仅登录SqlPlus，不连接数据库
9

10
# 用户登出
11
[ exit | quit ]
12

13
# 连接到其它用户
14
connect 用户名/密码[ @服务名 | @IP:端口/实例名 ] [ as sysdba | as sysoper ]
15

16
# 显示登录用户
17
show user
18

19
# 显示表结构
20
desc 表名
21

22
# 执行Shell命令
23
host [shell命令]
24
host mkdir /tmp/test01                # 在SqlPlus中新建目录
25

26
# 编辑上一条命令
27
ed                                    # 语句末尾不加分号，编辑完成后用”/”运行
28

29
# 导出SQL执行日志到文件
30
spool C:\sql.txt
31
>>执行一批SQL语句<<
32
spool off
33

34
# SqlPlus配置
35
set timing [ on | off ]                # 打开与关闭命令记时
36
set feedback [ on | off | num ]        # 打开与关闭结果显示(num表示超过n行才显示)
37
set linesize xx                        # 设置行尺寸
38
set pagesize xx                        # 设置页尺寸
39
col 字段名 for 99999                  # 修改数字的列宽
40
col 字段名 for a20                    # 修改字符的列宽
41

42
# 执行脚本
43
start xxx.sql                          # 执行SQL脚本，与@效果一致
44
@xxx.sql                               # 执行SQL脚本
45
sqlplus -S ${DB_USER}/${DB_PASSWORD}@${DB_SERVER} <create_tables.sql >./log/create_tables.log
46

2. 其它客户端

• 官方Web客户端：Enterprise Manager(主要在Oracle11g版本使用，相关使用说明参考“部署文档”目录下相关文档)。
• 官方GUI客户端：SqlDeveloper、PLSQL Developer 13等。
• 通用GUI客户端：DataGrip、Navicat、Dbeaver等。

第四节 体系结构

29张图看清Oracle 19c 技术架构 - 墨天轮 (modb.pro)

1. 磁盘文件

Oracle数据库在运行过程中使用的一些文件如下：

• 参数文件：用于定义实例启动时的选项，以及配置实例和数据库的特征，如：OracleBase\admin\ORCL01\pfile\init.ora.8720212396、OracleHome\database\SPFILEORCL01.ORA等，在实例启动后也可通过show parameter / alter system进行查看和修改。
• 控制文件：在数据库启动时，要根据控制文件的信息查找数据文件。控制文件记录了以下关键信息：数据文件的位置和大小、重做日志文件的位置及大小、数据库名称及创建时间、日志序列号等。
• 数据文件：包含用户数据、程序数据、元数据和数据字典的文件，每个数据文件都有自己所属的表空间。
• 重做日志文件：记录了对数据的所有更改信息，并提供一种数据恢复机制，如果数据库服务器发生崩溃，但未丢失任何数据文件，那么实例便可使用这些文件中的信息恢复数据库。
• 归档日志文件：使用这些文件和数据库备份，可以恢复丢失的数据文件。也就是说，归档日志能够恢复还原的数据文件。
• 口令文件：允许sysdba、sysoper 和sysasm 远程连接到实例并执行管理任务。
• 跟踪日志文件：每一个服务器和后台进程都可以对关联的跟踪文件进行写操作。如果在进程中检测到内部错误，进程就会将关于错误的信息转储到进程的跟踪文件。
• 预警日志文件：这些是特殊的跟踪文件，又称为预警日志。数据库预警日志是按时间顺序列出的消息日志和错误日志。

2. 逻辑结构

Oracle逻辑结构从大到小依次分为：集群 -> 实例 -> 数据库 -> 用户/表空间/模式 -> 段 -> 区 -> 数据块等。

• 集群：Oracle实现高可用的手段之一，主要有RAC集群(多实例共享存储模型)和DataGrip集群(多实例多存储模型)。

• 实例：可以理解为一系列后台进程的组合，对各类数据进行管理。在多租户模式下，一个实例由一个CDB和多个PDB组成。

• 数据库：可以理解为一系列磁盘文件的集合。一个数据库可以被多个用户访问，创建多个模式、表空间等。

• 用户：数据库访问对象，一个数据库下可创建多个用户。

• 表空间：可以理解为虚拟文件夹，包含1个或多个数据文件，在创建表、索引等时可指定所在的表空间。其分类如下：

  • SYSTEM表空间：包含数据字典、存储过程、触发器和系统回滚段等系统信息，是数据库运行的必要条件，其名称是不可更改的。
  • SYSAUX表空间：系统表空间的辅助表空间，主要存储除数据字典以外的其他对象。
  • UNDO表空间：保存用户回滚段信息，如：UNDOTBS1。
  • 用户表空间：存放用户的数据和私有信息，如：USERS、TEST_TS01。
  • TEMP表空间：存储Oracle数据库运行期间所产生的临时数据。当数据库关闭后，临时表空间中所有数据将全部被清除。

• 模式：可以理解为多个表、索引、视图等数据库对象的集合。

• 段/区/块：表空间可由多个段(数据段/索引段/回滚段/临时段)组成，段可跨文件，当段空间不足时，以区为单位分配空间。每个区又由多个数据块构成，块是读写的最小单位。

3. 内存分区

Oracle占用的内存大致可分为系统全局区(SGA)、进程全局区(PGA)、用户全局区(UGA)：

• SGA：在实例启动时分配的内存，可以被多个用户共享，包含数据信息或控制信息等内容，相关可通过 v$sga 视图查看。

  • 数据块缓存区：用于缓存数据块，由参数文件 init.ora 中的 DB_LOCK_BUFFERS 参数配置。
  • 重做日志缓冲区：REDO文件的缓冲，保存对数据库进行的修改，以便在数据库恢复过程中用于向前滚动操作，由 LOG_BUFFER 参数配置。
  • 共享池：由 SHARED_POOL_SIZE 参数配置，包括字典缓存区和库缓存区。前者用于缓存用户、表名、权限等系统信息；后者用于缓存执行计划及SQL语句的语法分析树等。
  • 大型池：是一个可选内存区，作为SQL共享池的备用池，防止大量的不常用SQL(如备份/恢复时)导致SQL共享池命中率变低，由LARGE_POOL_SIZE 或 LARGE_POOL_MIN_ALLOC 设置。
  • Java池：为Java命令提供语法分析，由 JAVA_POOL_SIZE 参数设置。
  • 多缓冲池：可以在SGA中创建多个缓冲池，能够用多个缓冲池把大数据集与其他的应用程序分开，以减少它们争夺数据块缓存区内相同资源的可能性。
  • 流池：由 Oracle Streams 使用。

• PGA：在用户进程连接到数据库并创建一个会话时自动分配，当会话结束，PAG就会释放，相关信息可通过 v$process 视图查看。

• UGA：用于存储用户进程会话状态。当通过共享服务器连接Oracle时，UGA包含在SGA中，当通过专有服务器连接时，包含在PGA中。

4. 主要服务/进程

在Windows机器上，Oracle实例主要的服务如下(实例名为ORCL01)：

• OracleServiceORCL01：数据块实例的核心进程。
• OracleOraDB19Home1TNSListener：监听器进程(用于远程访问数据库)。
• OracleVssWriterORCL01：卷映射拷贝写入进程。

在Linux下，Oracle实例主要的进程如下(实例名为pdborcl)：

• oraclepdborcl：服务端进程(SPID)，由Oracle实例自动创建，用于处理客户端进程的请求和响应。
• tnslsnr：监听器进程，用于远程访问数据库。
• ora_smon_pdborcl：系统监控进程，用于执行实例恢复，清空未使用的临时段，合并数据字典管理的表空间里的连续的空闲区块等。
• ora_pmon_pdborcl：进程监控进程，负责监控和唤醒调度进程和服务器进程，清理buffer cache以及释放客户端进程使用的资源等。
• ora_dbw0_pdborcl：数据写进程，在合适时机(缓冲数量或时间超限/无可用缓冲区/检查点发生/表删除或截断/联机备份等)将缓冲区数据写入数据文件。
• ora_lgwr_pdborcl：日志写进程，在合适时机(缓冲尺寸或时间超限/commit/日志切换等)将日志缓冲区写入日志文件。
• ora_ckpt_pdborcl：检查点进程，负责更新控制文件和数据文件的头文件的检查点信息，保证数据库日志文件和数据文件的同步。
• ora_reco_pdborcl：在具有分布式选项时所使用的一个进程，自动地解决在分布式事务中的故障。
• ARCn：归档进程，发生日志切换时将重做日志文件复制到归档存储器。当日志是为ARCHIVELOG使用方式、并可自动地归档时ARCH进程才存在。
• Dnnn：调度进程，允许用户进程共享有限的服务器进程。
• LCKn：在具有并行服务器选件环境下使用，可多至10个进程（LCK0，LCK1……，LCK9），用于实例间的封锁。

其它进程可通过ps -ef | grep oracle或ps -ef | grep ora_等查看。

注意：

1. 在专用服务器模式，客户端进程和服务端进程一一对应；在共享服务器模式，一个服务端进程可同时服务多个客户端进程。
2. 进程监控(PMON)进程、系统监控(SMON)进程、数据写入(DBWR)进程、日志写入(LGWR)进程、检查点(CKPT)进程是必须启动的，否则实例无法启动成功。

第五节 启动和关闭流程

启动或关闭数据库需要以SYSDBA/SYSOPER权限登录：

xxxxxxxxxx
4
1
#su - oracle             # 切换至Oracle用户及其shell环境
2
$lsnrctl [start|stop]    # 启动监听/停止监听(可以用 lsnrctl status 查看监听状态)
3
$sqlplus / as sysdba     # 以dba身份进入sqlplus
4

1. 三阶段启动

Oracle数据库启动流程分为三个阶段：

• nomount：加载参数文件，启动实例，创建后台进程，分配SGA区，但没有加载控制文件、数据文件、重做日志文件等。
• mount：在 nomount 的基础上，加载控制文件。
• open：在 mount 的基础上，加载数据文件等其它数据，并打开数据库，此时非DBA用户才可访问数据库。

xxxxxxxxxx
7
1
# 数据库启动
2
# nomount/mount/open：启动到哪一阶段，默认为open。
3
# force：强制关闭并重新启动数据库。
4
# restrict：以受限会话方式启动数据库，只允许具有restricted session或者create session权限的用户访问数据库。
5
# pfile：用于指定启动实例时所使用的文本参数文件。
6
startup[nomount|mount|open|force] [restrict][pfile=filename]
7

2. 转换启动阶段

在进行某些特定的管理和维护操作时，需要处于特定的阶段：

转换启动阶段命令如下：

xxxxxxxxxx
7
1
# 数据库启动
2
startup nomount             -- 启动到 nomount 阶段
3

4
# 数据库启动状态转换
5
alter database mount;       -- 转换到 mount 阶段
6
alter database open;        -- 转换到 open 阶段 
7

3. 四种关闭模式

关闭Oracle数据库使用shutdown命令，它有四种模式：

• 正常关闭模式：阻止新建连接，在等待已有连接主动断开后才关闭数据库，在对关闭数据库的时间没有限制时使用。
• 立即关闭模式：阻止新建连接和新建事务，将未提交的事务进行回滚，然后直接关闭数据库。
• 事务关闭模式：正常关闭和立即关闭的一个折中，在等待所有未提交的事务完成后，再关闭数据库。
• 终止关闭模式：当其它三种方式无法关闭数据库时，才使用这种方式，说明数据库发生了严重错误，使用该方式可能丢失数据。

xxxxxxxxxx
3
1
# 关闭数据库
2
shutdown [normal | transactional | immediate | abort]
3

4. 会话受限状态

在启动数据库时，如果加上restrict参数，将会进入受限状态，只允许具有restricted session或者create session权限的用户访问数据库。

通过如下命令可进行受限状态的切换：

若是启动数据库的时候设置为受限状态，在完成管理工作后，需要将数据库恢复为非受限状态：

xxxxxxxxxx
2
1
# 切换受限状态
2
alter system [ enable | disable ] restricted session;

5. 读写/只读状态

正常启动模式下，数据库默认处于读写状态，但有时候也需要将数据库设置为只读状态，保证用户只能查询数据，但不能以任何方式对数据库对象进行修改。

xxxxxxxxxx
12
1
-- 查看数据库读写状态
2
select DBID, NAME, OPEN_MODE from v$database;
3

4
-- 切换只读状态
5
shutdown immediate
6
startup mount
7
alter database open read only;
8

9
-- 切换读写状态
10
shutdown immediate
11
startup open read write;
12

6. 静默/挂起状态

静默状态和挂起状态是两种特殊的数据库状态，利用这两种数据库状态，数据库管理员可以完成一些特殊的管理和维护操作。

• 静默状态：在静默状态下，只有SYS和SYSTEM用户才能够在数据库中执行查询、更新操作，运行PL/SQL程序，任何非管理员用户都不能在数据库中执行任何操作。在数据库运行过程中，执行如下的语句将进入和退出静默状态：

xxxxxxxxxx
5
1
-- 进入静默状态
2
alter system quiesce restricted;
3

4
-- 退出静默状态
5
alter system unquiesce;

• 挂起状态：当数据库处于挂起状态时，数据库所有的物理文件（控制文件、数据文件和重做日志文件）的I/O操作都被暂停，这样能够保证数据库在没有任何I/O操作的情况下进行物理备份。

xxxxxxxxxx
5
1
-- 修改为挂起状态
2
alter system suspend;
3

4
-- 修改为静默状态
5
alter system resume;

挂起状态与静默状态的区别是：挂起状态并不禁止非管理员用户进行数据库操作，只是暂时停止所有用户的I/O操作。

第02章_实例管理

第一节 数据库

1. 查看数据库信息

xxxxxxxxxx
16
1
# 查看当前数据库容器(CDB)
2
select sys_context('USERENV','CON_NAME') from dual;  -- show con_name;
3

4
# 查看当前PDB数据库(注：如当前会话位于CDB$ROOT容器下，则可查看所有PDB信息，若位于某一PDB下，则只可查看当前PDB信息)
5
select con_id,dbid,name,open_mode from v$pdbs;    -- show pdbs
6
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
7
------ ------------------------------ ---------- ----------
8
     2 PDB$SEED                       READ ONLY  NO
9
     3 PDB1                           READ WRITE NO
10
     4 PDB2                           READ WRITE NO
11
     5 PTESTUAT                       READ WRITE NO
12
     7 PDB3                           MOUNTED
13
     
14
# 查看数据库内部的编码方式
15
select userenv('language') from dual;
16

2. 新建/销毁数据库

xxxxxxxxxx
1
1

3. 打开/切换数据库

在Oracle12c及之后，一个实例可以创建多个PDB数据库，打开和切换方式如下：

xxxxxxxxxx
9
1
# 打开PDB数据库
2
alter pluggable database pdb1 open;
3

4
# 切换到CDB
5
alter session set container=CDB$ROOT;
6

7
# 切换到某PDB
8
alter session set container = pdborcl;
9

注意：

1. 在Oracle中，无法使用use xxx语法来切换数据库，必须执行 alter session 命令。

4. 数据库配置参数

xxxxxxxxxx
8
1
-- 查看数据库参数
2
SELECT NAME, VALUE, ISDEFAULT, DESCRIPTION FROM V$PARAMETER WHERE NAME = 'sga_max_size';
3

4
-- 修改数据库参数
5
ALTER SYSTEM SET sga_max_size=8192M SCOPE=SPFILE;
6

7
-- 创建SPFILE文件来修改参数(需重启生效)
8
CREATE PFILE='<pfile路径>' FROM SPFILE;

常用数据库参数如下：

第二节 用户

1. 查询用户信息

xxxxxxxxxx
9
1
-- 查询数据库所有用户
2
select * from dba_users; 
3

4
-- 查询可访问的用户
5
select * from all_users; 
6

7
-- 查询当前用户
8
select * from user_users; 
9

注：在创建实例时，会创建若干个内置用户，它们分别有不同的用途。

• sys：权限最大的超级用户，相当于SQL server中的sa帐户，只能以系统管理员(sysdba)或系统操作员(sysoper)的权限登录。
• system：通常用来创建一些用于查看管理信息的表或视图，权限比sys小，登陆时以normal权限登陆。
• scott：Oracle提供的示例用户之一，为普通用户，默认密码为tiger。

2. 创建新用户

xxxxxxxxxx
8
1
-- 创建新用户，设置密码，指定默认表空间，指定临时表空间
2
create user hyx identified by hyx123 default tablespace ts_hyx temporary tablespace ts_tmp_hyx;
3

4
-- 给用户授权
5
grant connect to hyx;
6
grant resource to hyx;
7
grant dba to hyx;
8

3. 删除或锁定用户

xxxxxxxxxx
6
1
-- 删除用户，并级联删除用户下的数据对象
2
drop user hyx cascade; 
3

4
-- 锁定用户
5
alter user hyx account lock;
6

4. 修改用户信息

xxxxxxxxxx
3
1
-- 修改用户密码
2
alter user hyx identified by hyx147741
3

第三节 表空间

1. 查询表空间信息

xxxxxxxxxx
10
1
-- 查询数据库所有表空间
2
select * from dba_tablespaces;
3

4
-- 查询可访问的表空间
5
select * from user_tablespaces;
6

7
-- 查询表空间对应的的文件信息
8
select * from dba_data_files where tablespace_name = upper('TS_HYX');  -- 注意表空间名称大写
9
select * from dba_temp_files;
10

2. 创建或删除表空间

xxxxxxxxxx
8
1
-- 创建普通表空间(注意：需要拥有对应目录的读写权限，下同)
2
create tablespace ts_hyx datafile 'c:\app\hyx\oradata\orcl\ts_hyx.dbf' size 10M;
3

4
-- 创建临时表空间
5
create temporary tablespace ts_tmp_hyx tempfile 'c:\app\hyx\oradata\orcl\ts_tmp_hyx.dbf' size 20M;
6

7
-- 删除表空间，连同表空间文件也一起删除
8
drop tablespace TS_TEST including content;

3. 修改表空间

xxxxxxxxxx
11
1
-- 修改用户的默认表空间或临时表空间
2
ALTER USER username DEFAULT | TEMPORARY TABLESPACE tablespace_name;
3

4
-- 修改表空间数据文件
5
alter tablespace ts_hyx add datafile 'ts_hyx_02.dbf' size 10M;  -- 增加数据文件
6
alter tablespace ts_hyx drop datafile 'ts_hyx_02.dbf';          -- 删除数据文件(注：不能删除表空间中的第一个创建的数据文件，如果需要删除的话，我们需要把整个的表空间删掉)
7

8
-- 修改表空间状态
9
ALTER TABLESPACE tablespace_name ONLINE | OFFLINE;       -- 联机或脱机
10
ALTER TABLESPACE tablespace_name READ ONLY | READ WRITE; -- 只读或可读写
11

第四节 角色

角色就是一组权限或者说是权限的集合。用户可以给角色赋予指定的权限，然后将角色赋给相应的用户，如：

• SYSDBA：系统管理员，可以执行任何SYS操作，包括重建数据库和更改数据库配置文件等操作。
• SYSOPER：系统操作员，是一种用于管理数据库的特殊权限，如打开/关闭数据库，管理服务器进程等。
• DBA：数据库管理员，拥有对数据库的完全访问权限，包括可以创建、删除、备份和还原数据库。
• CONNECT：连接角色，允许用户连接到数据库。
• RESOURCE：为用户提供了创建对象的权限，例如表、索引、视图等。
• PUBLIC：提供了所有对象的访问权限，任何用户都可以被授予PUBLIC角色。

注意：

1. SYSDBA角色在实例启动后就可以使用，可用其加载和打开数据，而DBA角色只有在数据库完全打开才有意义。

1. 查询角色信息

xxxxxxxxxx
8
1
-- 查询所有角色
2
select * from dba_roles;
3

4
-- 查询角色所拥有的权限
5
select * from role_sys_privs;  -- 角色的系统权限
6
select * from role_tab_privs;  -- 角色的对象权限
7
select * from role_role_privs; -- 该角色所拥有的其它角色
8

2. 创建或删除角色

xxxxxxxxxx
9
1
-- 创建角色
2
create role role_01;
3

4
-- 创建需要口令的角色
5
CREATE ROLE role_02 IDENTIFIED BY password00001;
6

7
-- 删除角色
8
drop role role_01;
9

3. 使用角色

xxxxxxxxxx
9
1
-- 为角色赋权
2
grant create table,create view to role_01;
3

4
-- 将角色赋予用户
5
grant role_01 to hyx;
6

7
-- 收回角色
8
revoke role_01 from hyx;
9

第五节 权限

权限指的是执行特定命令或访问数据库对象的权利，对数据库的安全性(系统安全性、数据安全性)有着至关重要的作用，可分为：

• 系统权限：允许用户执行特定的数据库动作，如创建表、索引、连接实例等。
• 对象权限：也叫实体权限，允许用户操纵一些特定的对象，如读取视图、更新某些列、执行存储过程等。

1. 查询权限信息

xxxxxxxxxx
22
1
-- 查询Oracle支持的系统权限、对象权限
2
SELECT * FROM SYSTEM_PRIVILEGE_MAP;
3
SELECT * FROM TABLE_PRIVILEGE_MAP;
4

5
-- 查询用户所拥有的权限
6
SELECT * FROM DBA_SYS_PRIVS;  -- 所有用户所拥有的系统权限
7
SELECT * FROM USER_SYS_PRIVS; -- 当前用户拥有的系统权限
8
SELECT * FROM DBA_TAB_PRIVS;  -- 所有用户所拥有的对象权限
9
SELECT * FROM ALL_TAB_PRIVS;  -- 可访问用户拥有的对象权限
10
SELECT * FROM USER_TAB_PRIVS; -- 当前用户拥有的对象权限
11

12
-- 查询用户所拥有的角色
13
SELECT * FROM DBA_ROLE_PRIVS; -- 用户所拥有的角色
14
SELECT * FROM USER_ROLE_PRIVS;-- 当前用户所拥有的角色
15

16
-- 查询当前会话的权限
17
SELECT * FROM SESSION_PRIVS; -- 当前会话拥有的权限
18
SELECT * FROM SESSION_ROLES; -- 当前会话拥有的角色
19

20
-- 针对表的访问权限的视图
21
SELECT * FROM TABLE_PRIVILEGES;
22

2. 授权与回收

xxxxxxxxxx
8
1
-- 授予/回收系统权限
2
grant create table,create sequence to hyx;
3
revoke create table, create sequence from hyx;
4

5
-- 授予/回收对象权限
6
grant select,update,insert on scott.emp to role_02;
7
revoke all on scott.emp from hyx;
8

3. 权限的传递

默认情况下，Oracle中的权限是不能传递的，如A授权给B，B不能再将权限授予给C，如需允许传递，需加如下一些选项：

xxxxxxxxxx
4
1
-- with admin option / with grant option ：授权时一并授予该权限的“赋权”权限，以支持权限的传递
2
grant connect to tyger1 with admin option;  -- 当取消权限时不会级联取消，一般用于系统权限
3
grant select on scott.emp to tyger1 with grant option; -- 在取消授权时会级联取消所有权限，一般用于对象权限
4

第六节 事务

事务是一组独立不可分割的工作单元，事务中的操作要么全部执行，要么都不执行。

1. 事务控制命令

xxxxxxxxxx
13
1
-- 设置隔离级别(Oracle仅支持READ COMMITTED和SERIALIZABLE两种)
2
SET TRANSACTION ISOLATION LEVEL [READ COMMITTED|SERIALIZABLE]
3

4
-- 设置保存点(可用rollback回退到指定的保存点，当事务提交后该会话的所有保存点都将被删除)
5
savepoint sp1
6

7
-- 提交事务
8
-- 事务提交后，会删除保存点，释放锁；在RC隔离级别下，其它会话将可以查看到事务变化后的数据
9
commit;
10

11
-- 回滚事务(对未提交的事务进行撤销)
12
rollback [ to savapoint_name]
13

注意：

1. 关于事务的四大特性和隔离级别等请参考MySql篇学习笔记。

第七节 锁

1. 锁的分类

锁用于多事务场景下对共享资源的访问，以保证数据库的完整性和一致性。

• 按照锁的特性分类：

  • 排它锁(X锁)：即写锁，持有 X 锁即能对数据进行读取，也能进行修改。如果事务A对数据T加上X锁，则其他事务不能对数据T加任何类型的锁。
  • 共享锁(S锁)：即读锁，持有 S 锁对数据只能查看，但是无法修改和删除。如果事务A对数据对象T加S锁，则事务A只能读取T，其他事务只能再对T加S锁，不能加X锁，直到S锁释放掉。这样就保证了其他事务可以读数据对象T，但是不能进行任何修改。

• 根据保护的对象分类：

  • 数据锁(DML锁)：用于保护数据的完整性，能够防止同步冲突的DML和DDL操作的破坏性交互。
  • 字典锁(DDL锁)：用于保护数据库对象的结构，如表、索引等的结构定义。
  • 内部锁与闩(internal locks and latches)：保护数据库的内部结构，如数据文件，对用户是不可见的。
  • 其它锁：如分布式锁(Distributed locks)和并行高速缓存管理锁(PCM locks)等，常用于OPS(并行服务器)中。

注意：

1. SELECT语句(无for update等)不需要任何锁，即使记录被锁定，依然可以执行(oracle是用到undo的内容进行一致性读来实现的)。
2. Oracle中，每行数据都有标志位来表示该行数据是否被锁定；而在MySql(InnoDB)中，行锁是加在索引上的，如果没有索引，将通过隐藏的聚集索引来对记录加锁。

2. DML锁详解

DML锁(数据锁)主要包括TM锁和TX锁，其中TM锁称为表级锁(意向锁)，TX锁称为行级锁(事务锁)。当Oracle执行INSERT/DELETE/ UPDATE/SELECT FOR UPDATE等DML语句时，首先申请表级锁，当表级锁获得后，才会申请行级锁。

1) 表级锁

不同类型的SQL语句需要申请不同类型的表级锁，简述如下：

扩展：

1. LOCK TABLE 语句后可接 NO WAIT，如果获取不到锁，将不会等待而是直接返回，放弃执行当前指令并抛出一个错误。
2. LOCK TABLE 语句后也可接 WAIT num，表示最多等待多少秒。

当表级锁相互兼容时，才可以加锁成功：

当出现不兼容时，就直接进入等待，而无需每次都逐行检查锁标志，大大提高了系统的效率。

2) 行级锁

行级锁只有排它锁(X锁)一种，作用是防止两个事务同时修改相同的数据行。

• Oracle 同时支持多版本并发访问控制及行级锁技术，因此用户只有在修改相同数据行时才会出现竞争。
• 读取操作无需等待对相同数据行的写入操作，写入操作也无需等待对相同数据行的读取操作。
• 当事务获得了某些数据行上的行级锁时，此事务同时获得了数据行所属表上的表级锁。
• 表级锁能够防止系统中并发地执行有冲突的 DDL 操作，避免当前事务中的数据操作被并发地 DDL 操作影响。

3. 锁存器机制

1) 锁存器机制简介

锁存器(latch)用来保护对Oracle内存结构的访问，是一种特殊类型的锁，采用低层次的序列化技术，用以保护SGA中的共享数据结构。Oracle通过锁存器机制保证没有任何两个进程可以同时访问同一块数据结构。

Latch按请求方式分为两种类型：

• 等待型（willing-to-wait）：请求的进程通过数次短时间的等待后再次发出请求，直到获得latch控制权。
• 立即型（immediate）：请求的进程如果未能获得latch控制权，则不等待，继续处理其它指令。

DBA无法对 Latch 直接控制，也无相关的初始化参数可以调配。从性能优化的角度看，DBA需要注意锁存器的争用情况，如果 Latch 存在竞争，表明SGA的一部分正在经历不正常的资源使用。

2) 查询锁存器争用

视图v$latch记录了等待型和立即型两类锁存器的活动。以下查询可以检查latch争用情况，锁存器是否存在等待。

xxxxxxxxxx
59
1
select name, gets, misses, wait_time from v$latch where misses <> 0 order by wait_time desc;
2
+-----------------------------------+-------+------+---------+
3
|NAME                               |GETS   |MISSES|WAIT_TIME(us)|
4
+-----------------------------------+-------+------+---------+
5
|space background task latch        |17542  |10735 |1271149  |
6
|qmn task queue latch               |2724   |736   |3579     |
7
|session statistics                 |303    |14    |852      |
8
|call allocation                    |5439   |39    |380      |
9
|ksv class latch                    |4645   |121   |269      |
10
|post/wait queue                    |44115  |4840  |250      |
11
|shared pool                        |315020 |911   |166      |
12
|messages                           |139878 |1344  |53       |
13
|AQ Slave freSlvL_kwsbgsgn latch    |516    |70    |51       |
14
|session state list latch           |470    |13    |20       |
15
|active checkpoint queue latch      |11108  |575   |11       |
16
|JS global state obj latch          |42     |5     |6        |
17
|enqueues                           |2998   |39    |4        |
18
|OS process allocation              |14259  |4     |0        |
19
|OS process: request allocation     |260    |2     |0        |
20
|lgwr LWN SCN                       |4993   |1     |0        |
21
|Consistent RBA                     |2840   |1     |0        |
22
|cache buffers lru chain            |26661  |11    |0        |
23
|checkpoint queue latch             |298081 |31    |0        |
24
|cache buffers chains               |1681279|306   |0        |
25
|cache buffer handles               |279    |24    |0        |
26
|object queue header operation      |61884  |2     |0        |
27
|redo writing                       |23134  |444   |0        |
28
|redo allocation                    |37043  |76    |0        |
29
|log write worker phase             |48     |1     |0        |
30
|transaction allocation             |39199  |2     |0        |
31
|dummy allocation                   |304    |14    |0        |
32
|corrupted undo seg latch           |42     |7     |0        |
33
|ILM Stats main anchor latch        |1435   |10    |0        |
34
|sequence cache                     |243    |4     |0        |
35
|remote tool request latch          |116    |3     |0        |
36
|Event Group Locks                  |366    |1     |0        |
37
|job_queue_processes free list latch|82     |1     |0        |
38
|query server freelists             |99     |1     |0        |
39
|error message lists                |31     |9     |0        |
40
|process queue reference            |9301   |6     |0        |
41
|parallel query alloc buffer        |134    |16    |0        |
42
|hash table column usage latch      |1833   |2     |0        |
43
|qmn state object latch             |140    |13    |0        |
44
|active service list                |184163 |36    |0        |
45
|ksv msg queue latch                |592    |18    |0        |
46
|channel operations parent latch    |2792   |48    |0        |
47
|channel handle pool latch          |584    |2     |0        |
48
|resmgr:free threads list           |297    |18    |0        |
49
|enqueue freelist latch             |13     |9     |0        |
50
|enqueue hash chains                |1057175|87    |0        |
51
|parameter table management         |604    |4     |0        |
52
|object stats modification          |3875   |13    |0        |
53
|client/application info            |1054   |6     |0        |
54
|session allocation                 |826    |8     |0        |
55
|interrupt manipulation             |62     |8     |0        |
56
|shardgroup list latch              |2372   |2     |0        |
57
|process allocation                 |435    |3     |0        |
58
+-----------------------------------+-------+------+---------+
59

另外通过v$system_event检查系统事件latch free是否出现来判断是否存在锁存器争用。

xxxxxxxxxx
8
1
-- 自实例启动以来，经历了与锁存器有关的等待事件次数和消耗的等待时间（毫秒）
2
select event, total_waits, time_waited from v$system_event where event='latch free';
3
+----------+-----------+-----------+
4
|EVENT     |TOTAL_WAITS|TIME_WAITED|
5
+----------+-----------+-----------+
6
|latch free|9276       |129        |
7
+----------+-----------+-----------+
8

以下统计latch争用导致的各类未命中：

xxxxxxxxxx
6
1
select parent_name, sum(longhold_count) from v$latch_misses group by parent_name having sum(longhold_count) > 0 order by sum(longhold_count) desc;
2
PARENT_NAME                                                      SUM(LONGHOLD_COUNT)
3
---------------------------------------------------------------- -------------------
4
In memory undo latch                                                               3
5
cache buffers chains                                                               3
6

4. 锁的查询

1) 锁相关的数据字典

xxxxxxxxxx
12
1
SELECT * FROM v$lock;               -- 锁信息
2
SELECT * FROM v$locked_object;      -- 当前被锁对象的信息
3
SELECT * FROM v$session;            -- 记录会话及锁的信息
4
SELECT * FROM v$session_wait;       -- 记录会话的等待信息
5
SELECT * FROM v$latch;              -- 锁存器信息
6
SELECT * FROM v$latch_misses;       -- latch争用的丢失统计
7
SELECT * FROM v$sqltext;            -- sga中的sql
8
SELECT * FROM v$sqlarea;            -- shared sql area中的sql
9
SELECT * FROM dba_objects;          -- 数据库中的所有对象
10
SELECT * FROM dba_locks;            -- 对V$lock的格式化视图
11
SELECT * FROM dba_blockers;         -- 正在阻塞资源的会话
12
SELECT * FROM dba_waiters;          -- 正在等待锁资源的会话

2) DML锁查询

日常开发中，最常用的为DML锁的查询：

xxxxxxxxxx
25
1
-- 查询DML锁
2
SELECT L.SESSION_ID "会话ID",
3
       S.SERIAL# "会话序列号",
4
       P.SPID "会话进程号",
5
       S.USERNAME "所属用户",
6
       S.MACHINE "客户端",
7
       O.OBJECT_NAME "被锁对象",
8
       O.OBJECT_TYPE "被锁对象类型",
9
             CASE L.LOCKED_MODE 
10
                 WHEN 0 THEN 'NONE' 
11
                     WHEN 1 THEN 'NULL' 
12
                     WHEN 2 THEN 'RS' 
13
                     WHEN 3 THEN 'RX' 
14
                     WHEN 4 THEN 'S' 
15
                     WHEN 5 THEN 'SRX' 
16
                     WHEN 6 THEN 'X' 
17
                     ELSE   TO_CHAR(L.LOCKED_MODE)
18
             END "锁模式",
19
       CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
20
  FROM V$LOCKED_OBJECT L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
21
 WHERE L.SESSION_ID = S.SID
22
   AND L.OBJECT_ID = O.OBJECT_ID
23
   AND S.PADDR = P.ADDR
24
   AND S.SCHEMA# <> 0
25
AND S.USERNAME = 'KBSSFMS';

3) 从会话角度查询

xxxxxxxxxx
24
1
-- 查询有锁阻塞的会话
2
select * from v$lock where block=1;  
3
+------------------+------------------+----+----+------+-----+-----+-------+-----+-----+------+
4
|ADDR              |KADDR             |SID |TYPE|ID1   |ID2  |LMODE|REQUEST|CTIME|BLOCK|CON_ID|
5
+------------------+------------------+----+----+------+-----+-----+-------+-----+-----+------+
6
|'00007FFBB88992E0'|'00007FFBB8899318'|1103|TX  |262151|23576|6    |0      |1296 |1    |0     |
7
+------------------+------------------+----+----+------+-----+-----+-------+-----+-----+------+
8

9
-- 正在阻塞资源的会话
10
SELECT * FROM dba_blockers;         
11
+---------------+------+
12
|HOLDING_SESSION|CON_ID|
13
+---------------+------+
14
|1103           |0     |
15
+---------------+------+
16

17
-- 正在等待锁资源的会话
18
SELECT * FROM dba_waiters;          
19
+---------------+--------------+---------------+--------------+-----------+---------+--------------+--------+--------+
20
|WAITING_SESSION|WAITING_CON_ID|HOLDING_SESSION|HOLDING_CON_ID|LOCK_TYPE  |MODE_HELD|MODE_REQUESTED|LOCK_ID1|LOCK_ID2|
21
+---------------+--------------+---------------+--------------+-----------+---------+--------------+--------+--------+
22
|1227           |0             |1103           |0             |Transaction|Exclusive|Exclusive     |262151  |23576   |
23
+---------------+--------------+---------------+--------------+-----------+---------+--------------+--------+--------+
24

4)从被锁对象角度查询

xxxxxxxxxx
63
1
-- 查询被锁住的对象
2
select object_id, session_id, oracle_username, os_user_name, locked_mode from v$locked_object; 
3
+---------+----------+---------------+-------------+-----------+
4
|OBJECT_ID|SESSION_ID|ORACLE_USERNAME|OS_USER_NAME |LOCKED_MODE|
5
+---------+----------+---------------+-------------+-----------+
6
|155737   |1103      |KRMP           |Administrator|3          |
7
|155737   |1227      |KRMP           |Administrator|3          |
8
+---------+----------+---------------+-------------+-----------+
9

10
-- 查看被锁对象的信息
11
select object_id, object_name, object_type, owner from dba_objects where object_id = 155737; 
12
+---------+------------+-----------+-----+
13
|OBJECT_ID|OBJECT_NAME |OBJECT_TYPE|OWNER|
14
+---------+------------+-----------+-----+
15
|155737   |STK_CALENDAR|TABLE      |KRMP |
16
+---------+------------+-----------+-----+
17

18
-- 查看是谁锁住了谁
19
select a.sid holdsid, b.sid waitsid, a.type, a.id1, a.id2, a.ctime 
20
  from v$lock a, v$lock b 
21
  where a.id1 = b.id1 and a.id2 = b.id2 and a.block = 1 and b.block = 0; 
22
+-------+-------+----+------+-----+-----+
23
|HOLDSID|WAITSID|TYPE|ID1   |ID2  |CTIME|
24
+-------+-------+----+------+-----+-----+
25
|1103   |1227   |TX  |262151|23576|1446 |
26
+-------+-------+----+------+-----+-----+
27

28
-- 进一步查看锁阻塞和锁请求的信息
29
select sid, type, id1, id2,
30
       decode(lmode, 0, 'none',1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') lmode,
31
       decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,
32
       ctime, block
33
from v$lock where type in ('TM', 'TX'); 
34
+----+----+------+-----+-------------+---------+-----+-----+
35
|SID |TYPE|ID1   |ID2  |LMODE        |REQUEST  |CTIME|BLOCK|
36
+----+----+------+-----+-------------+---------+-----+-----+
37
|1227|TX  |262151|23576|none         |exclusive|863  |0    |
38
|1103|TM  |155737|0    |row exclusive|none     |1462 |0    |
39
|1227|TM  |155737|0    |row exclusive|none     |863  |0    |
40
|1103|TX  |262151|23576|exclusive    |none     |1462 |1    |
41
+----+----+------+-----+-------------+---------+-----+-----+
42

43
-- 通过SID查看相关会话的信息
44
select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (1227,9);
45
+----+-------+--------+--------+------+-------+--------+-------------------+----------------+-------------+
46
|SID |SERIAL#|AUDSID  |USERNAME|STATUS|MACHINE|PROGRAM |LOGON_TIME         |LOCKWAIT        |ROW_WAIT_OBJ#|
47
+----+-------+--------+--------+------+-------+--------+-------------------+----------------+-------------+
48
|1227|60892  |28170016|KRMP    |ACTIVE|HYX-PC |DataGrip|2023-09-14 10:24:23|00007FFBC019EF58|155737       |
49
+----+-------+--------+--------+------+-------+--------+-------------------+----------------+-------------+
50

51
-- 也可以将几个视图联合查询，同时找出被锁对象及其会话信息
52
select t3.row_wait_obj#, t2.object_name, t2.object_type, t2.owner, t1.os_user_name, t1.session_id, 
53
       t3.serial#, t3.audsid, t3.username, t3.logon_time, t3.machine, t3.program, t3.status
54
  from v$locked_object t1 
55
  join dba_objects t2 on t1.object_id = t2.object_id 
56
  join v$session t3 on t1.session_id = t3.sid;
57
+-------------+------------+-----------+-----+-------------+----------+-------+--------+--------+-------------------+-------+--------+--------+
58
|ROW_WAIT_OBJ#|OBJECT_NAME |OBJECT_TYPE|OWNER|OS_USER_NAME |SESSION_ID|SERIAL#|AUDSID  |USERNAME|LOGON_TIME         |MACHINE|PROGRAM |STATUS  |
59
+-------------+------------+-----------+-----+-------------+----------+-------+--------+--------+-------------------+-------+--------+--------+
60
|-1           |STK_CALENDAR|TABLE      |KRMP |Administrator|1103      |38434  |28170015|KRMP    |2023-09-14 10:13:40|HYX-PC |DataGrip|INACTIVE|
61
|155737       |STK_CALENDAR|TABLE      |KRMP |Administrator|1227      |60892  |28170016|KRMP    |2023-09-14 10:24:23|HYX-PC |DataGrip|ACTIVE  |
62
+-------------+------------+-----------+-----+-------------+----------+-------+--------+--------+-------------------+-------+--------+--------+
63

5. 释放锁

当事务执行COMMIT或ROLLBACK操作，持有的锁都会被释放。此外，也可以强制释放锁：

xxxxxxxxxx
7
1
-- 释放锁(SQL层面杀掉会话)
2
alter system kill session 'SID,SERIAL#';
3

4
-- 释放锁(操作系统层面杀掉服务端进程)
5
orakill KFMS 3876
6
kill -9 9876   # 9876为SPID
7

6. 锁分析案例

1) 数据库连接与会话信息

xxxxxxxxxx
27
1
-- session1:
2
sqlplus cmes/cmes@mes
3
select sys_context('userenv','sessionid') from dual;
4

5
SYS_CONTEXT
6
-----------
7
330036
8

9
 
10
-- session2:
11
sqlplus rmes/rmes@mes
12
select sys_context('userenv','sessionid') from dual;
13

14
SYS_CONTEXT
15
-----------
16
330038
17

18

19
-- session3：查询两个会话的基本信息
20
sqlplus / as sysdba
21
select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where audsid in (330036, 330038);
22

23
       SID    SERIAL#     AUDSID USERNAME   STATUS   MACHINE                        PROGRAM              LOGON_TIME          LOCKWAIT             ROW_WAIT_OBJ#
24
---------- ---------- ---------- ---------- -------- ------------------------------ -------------------- ------------------- -------------------- -------------
25
       133          8     330036 CMES       INACTIVE WORKGROUP\ORACLE11G-2          sqlplus.exe          2016-10-30 14:16:38                              13482
26
       197         28     330038 RMES       INACTIVE WORKGROUP\ORACLE11G-2          sqlplus.exe          2016-10-30 14:17:30                                 -1
27

2) TM-S锁与TX-X锁

xxxxxxxxxx
58
1
-- session1:
2
lock table rmes.r_wip_tracking_t in share mode;
3

4

5
-- session2：此更新被卡住，未能执行
6
update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';
7

8
-- session3：查询锁阻塞和锁请求的基本信息（谁阻塞了谁）
9
select a.sid holdsid, b.sid waitsid, a.type, a.id1, a.id2, a.ctime from v$lock a, v$lock b where a.id1 = b.id1 and a.id2 = b.id2 and a.block = 1 and b.block = 0;
10

11
   HOLDSID    WAITSID TY        ID1        ID2      CTIME
12
---------- ---------- -- ---------- ---------- ----------
13
       133        197 TM      97125          0         69
14

15
HOLDSID：锁阻塞会话的SID
16
WAITID：锁请求会话的SID
17
TYPE：锁类型，TM-DML锁，TX-DDL锁, UL-用户定义的锁类型
18
ID1：当锁类型为TM锁时，为被锁对象的object_id
19
ID2：当锁为TX锁时，ID1为usn+slot，而ID2为seq
20
CTIME：锁持续的时间
21

22

23
-- 进一步查询锁阻塞和锁请求的详细信息
24
select sid, type, id1, id2,
25
  decode(lmode, 0, 'none',1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') lmode,
26
  decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,
27
  ctime, block from v$lock where type in ('TM', 'TX');
28
  
29
       SID TY        ID1        ID2 LMODE               REQUEST                  CTIME      BLOCK
30
---------- -- ---------- ---------- ------------------- ------------------- ---------- ----------
31
       133 TM      97125          0 share               none                       351          1
32
       197 TM      97125          0 none                row exclusive              335          0
33

34
lmode：锁占用类型
35
request：锁请求类型
36
block：阻塞者标志
37

38
 
39
-- 此时再看两个会话的基本信息
40
select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (133，197);
41

42
       SID    SERIAL#     AUDSID USERNAME   STATUS   MACHINE                        PROGRAM              LOGON_TIME          LOCKWAIT             ROW_WAIT_OBJ#
43
---------- ---------- ---------- ---------- -------- ------------------------------ -------------------- ------------------- -------------------- -------------
44
       133          8     330036 CMES       INACTIVE WORKGROUP\ORACLE11G-2          sqlplus.exe          2016-10-30 14:16:38                                 -1
45
       197         28     330038 RMES       ACTIVE   WORKGROUP\ORACLE11G-2          sqlplus.exe          2016-10-30 14:17:30 000007FF47C507E8             97125
46

47
status：inactive，表示命令执行已完成正处于就绪状态，active，表示命令正在执行中
48
lockwait：锁等待的地址
49
row_wait_object#：出现行等待对象的ID
50

51
 
52
-- 可以看到行等待的对象
53
select object_name from dba_objects where object_id=97125;
54

55
OBJECT_NAME
56
--------------------------
57
R_WIP_TRACKING_T
58