Oracle+11g+笔记(10)-数据库控制

Oracle+11g+笔记(10)-数据库控制

10、数据库控制

10.1 用事务控制操作

10.1.1 设置事务

1、设置只读事务

set transaction read only;

2、设置读写事务

set transaction read write;

3、为事务分配回滚段

可以为事务分配和指定回滚段。Oracle赋予用户可以自行分配回退段的权限，其目的是可以灵活地调整性能，用

户可以按照不同的事务来分配大小不同的回滚段，一般的分配原则如下。

• 若没有长时间运行查询读取相同的数据表，则可以把小的事务分配给小的回滚段，这样查询结果容易保存在内

  存中。

• 若长时间运行的查询读取相同的数据表，则可以把修改该表的事务分配给大的回滚段，这样读一致的查询结果

  就不用改写回滚信息。

• 可以将插入、删除和更新大量数据的事务分配给那些足以保存该事务的回滚信息的回滚段。

# 下面给出为事务设置回滚段的程序。
Set transaction use rollback segment sysyem;

4、事务提交

事务的提交方式包括如下3种：

显式提交：使用 commit命令使当前事务生效。

自动提交：在SQL*Plus 里执行set autocommit on;命令。

隐式提交：除了显式提交之外的提交，如发出DDL 命令、程序中止和关闭数据库等。

5、操作实例

connect  sys/sysroot as sysdba;
select recid,first_change#,first_time,next_change# from v$log_history;

RECID|FIRST_CHANGE#|FIRST_TIME             |NEXT_CHANGE#|
-----+-------------+-----------------------+------------+
   19|      1370635|2022-10-01 11:52:52.000|     1388525|
   20|      1388525|2022-10-01 16:26:38.000|     1435824|
   21|      1435824|2022-10-02 08:48:54.000|     1450519|
   22|      1450519|2022-10-02 09:27:46.000|     1478979|
   23|      1478979|2022-10-02 18:02:34.000|     1498959|
   24|      1498959|2022-10-02 22:11:09.000|     1519911|
   25|      1519911|2022-10-03 08:24:35.000|     1553816|

# 执行修改
UPDATE hr.IT_EMPLOYEES SET FIRST_NAME = 'Dinan-Up' WHERE EMPLOYEE_ID  = 107; 

在SQL*Plus里重新以SYSTEM用户登录数据库，执行日志转换命令，这样就强制执行了后台的LGWR(日志写入进

程)向日志文件中写入。

alter system switch logfile;

执行检查点转换命令，这样就强制执行了后台的CKPT(检查点进程)将所有已经修改的数据缓冲区内的数据写入磁

盘，同时更新控制文件和数据文件。

alter system checkpoint;

重新查询视图v$log_history

select recid,first_change#,first_time,next_change# from v$log_history;

RECID|FIRST_CHANGE#|FIRST_TIME             |NEXT_CHANGE#|
-----+-------------+-----------------------+------------+
   19|      1370635|2022-10-01 11:52:52.000|     1388525|
   20|      1388525|2022-10-01 16:26:38.000|     1435824|
   21|      1435824|2022-10-02 08:48:54.000|     1450519|
   22|      1450519|2022-10-02 09:27:46.000|     1478979|
   23|      1478979|2022-10-02 18:02:34.000|     1498959|
   24|      1498959|2022-10-02 22:11:09.000|     1519911|
   25|      1519911|2022-10-03 08:24:35.000|     1553816|
   26|      1553816|2022-10-03 16:42:39.000|     1565290|

从上面结果可以发现最后一行的数据已经发生变化，表明事务的提交将强制执行日志写入进程和检查点进程，使得

日志文件中的SCN 发生了变化。

6、事务回滚

事务回滚是指撤销未提交事务中SQL命令对数据所做的修改，已经提交的事务不能进行回滚。整个事务的回滚将完

成如下主要工作。

• 利用回滚段中存储的数据来撤销未提交事务中SQL 命令所做的修改。

• 解除对所有数据的事务封锁。

• 结束事务。

SELECT EMPLOYEE_ID ,FIRST_NAME  FROM hr.IT_EMPLOYEES;

EMPLOYEE_ID|FIRST_NAME|
-----------+----------+
        103|Alexander |
        104|Bruce     |
        105|David     |
        106|Valli     |
        107|Dinan-Up  |

DELETE  FROM hr.IT_EMPLOYEES WHERE EMPLOYEE_ID = 103;

SELECT EMPLOYEE_ID ,FIRST_NAME  FROM hr.IT_EMPLOYEES;

EMPLOYEE_ID|FIRST_NAME|
-----------+----------+
        104|Bruce     |
        105|David     |
        106|Valli     |
        107|Dinan-Up  |

ROLLBACK;

SELECT EMPLOYEE_ID ,FIRST_NAME  FROM hr.IT_EMPLOYEES;

EMPLOYEE_ID|FIRST_NAME|
-----------+----------+
        103|Alexander |
        104|Bruce     |
        105|David     |
        106|Valli     |
        107|Dinan-Up  |

上述操作的结果表明：事务的回滚可以撤销未提交事务中SQL 命令对数据所做的修改。

10.1.2 设置回退点

回退点又称为保存点，即指在含有较多 SQL 语句的事务中间设定的回滚标记，其作用类似于调试程序的中断点。

利用保存点可以将事务划分成若干小部分，这样就不必回滚整个事务，可以回滚到指定的保存点，有更大的灵活

性。回滚到指定保存点将完成如下主要工作。

• 回滚保存点之后的部分事务。

• 删除在该保存点之后建立的全部保存点，该保存点保留，以便多次回滚。

• 解除保存点之后表的封锁或行的封锁。

下面以在数据表IT_EMPLOYEES中插入数据为例来说明回滚到指定保存点的使用。

# step1 查询IT_EMPLOYEES数据表中的数据
SELECT EMPLOYEE_ID ,FIRST_NAME  FROM hr.IT_EMPLOYEES;

EMPLOYEE_ID|FIRST_NAME|
-----------+----------+
        105|David     |
        106|Valli     |
        107|Dinan-Up  |

# step2 建立保存点sp01
savepoint sp01;

# Step3 向IT_EMPLOYEES表中添加记录
insert into hr.IT_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,JOB_ID,SALARY,MANAGER_ID) values(108,'tom','Tim','QWER','234,567,1289','IT_PROG',1000,103);

# Step4 建立保存点sp02
savepoint sp02;

# Step5 在IT_EMPLOYEES数据表中删除数据
DELETE  FROM hr.IT_EMPLOYEES WHERE EMPLOYEE_ID = 106;

SQL> select * from hr.IT_EMPLOYEES;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         JOB_ID         SALARY MANAGER_ID
------------------------- -------------------- ---------- ---------- ----------
        108 tom                  Tim
QWER                      234,567,1289         IT_PROG          1000        103

        105 David                Austin
DAUSTIN                   590.423.4569         IT_PROG          4800        103

        107 Dinan-Up             Lorentz
DLORENTZ                  590.423.5567         IT_PROG          4200        103

# Step6 回滚到保存点sp02
rollback to sp02;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         JOB_ID         SALARY MANAGER_ID
------------------------- -------------------- ---------- ---------- ----------
        108 tom                  Tim
QWER                      234,567,1289         IT_PROG          1000        103

        105 David                Austin
DAUSTIN                   590.423.4569         IT_PROG          4800        103

        106 Valli                Pataballa
VPATABAL                  590.423.4560         IT_PROG          4800        103


EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         JOB_ID         SALARY MANAGER_ID
------------------------- -------------------- ---------- ---------- ----------
        107 Dinan-Up             Lorentz
DLORENTZ                  590.423.5567         IT_PROG          4200        103

比较两次查询结果，可以发现当事务回退到保存点sp02时，在保存点sp02后所作的操作已经被撤销。但发生在

保存点之前的操作并没有被撤销。

注意：以上介绍的使用rollback 命令回滚事务称之为显式回滚，还有一种回滚叫隐式回滚。如果系统在事务执行期

间发生错误、死锁和中止等情况时，系统将自动完成隐式回滚。

10.2 用锁控制并发存取

10.2.1 查询锁信息

Oracle 在动态状态表 V$lock 中存储与数据库中的锁有关的所有信息。

describe v$lock;

SQL> describe v$lock;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 KADDR                                              RAW(4)
 SID                                                NUMBER
 TYPE                                               VARCHAR2(2)
 ID1                                                NUMBER
 ID2                                                NUMBER
 LMODE                                              NUMBER
 REQUEST                                            NUMBER
 CTIME                                              NUMBER
 BLOCK                                              NUMBER

Oracle使用锁在允许多个用户同时访问时维护数据的一致性和完整性。但是，当两个或两个以上的用户会话试图

竞争同一对象的锁时，锁将成为坏消息。DBA 应该监控并管理数据库中对象的锁的争用。监控锁的方法包含如下3

种：

(1)、使用 CATBLOCK.SQL和 UTLLOCKT.SQL

Oracle 提供了两个有用的锁监控脚本，称为CATBLOCK.SQL和 UTLLOCKT.SQL。这些脚本可在

$ORACLE_HOME/rdbms/admin目录中找到。脚本CATBLOCK.SQL创建许多从 V$lock 这样的数据字典视图中收集

的与锁相关的信息的视图。脚本UTLLOCKT.SQL查询由 CATBLOCK.SQL 创建的视图，以报告等待锁的会话及其相

应的阻塞会话。CATBLOCK.SQL必须在使用 UTLLOCKT.SQL前运行。

(2)、直接查询数据字典视图

以下脚本可用于确定数据库中持有和等待锁的会话。该脚本查询并连接 V$lock和V$SESSION 视图。

set echo off
set pagesize 60
Column SID FORMAT 999 heading "SessionID"
Column USERNAME FORMAT A8
Column TERMINAL FORMAT A8 Trunc
select B.SID,C.USERNAME,C.TERMINAL,B.ID2,B.TYPE,B.LMODE,B.REQUEST from DBA_OBJECTS A,V$LOCK B,V$SESSION C
where A.OBJECT_ID(+)=B.ID1
and B.SID=C.SID
and C.USERNAME IS NOT NULL
order by B.SID,B.ID2;

SessionID USERNAME TERMINAL        ID2 TY      LMODE    REQUEST
--------- -------- -------- ---------- -- ---------- ----------
        5 SYS      unknown           0 AE          4          0
       68 DBSNMP   DESKTOP-          0 AE          4          0
       96 DBSNMP   DESKTOP-          0 AE          4          0
       97 DBSNMP   DESKTOP-          0 AE          4          0
      127 DBSNMP   DESKTOP-          0 AE          4          0
      128 SYS      DESKTOP-          0 AE          4          0
      128 SYS      DESKTOP-          0 TM          3          0
      128 SYS      DESKTOP-        995 TX          6          0
      129 DBSNMP   DESKTOP-          0 AE          4          0
      160 DBSNMP   DESKTOP-          0 AE          4          0
      161 SYS      unknown           0 AE          4          0
      190 DBSNMP   DESKTOP-          0 AE          4          0
      193 SYS      DESKTOP-          0 AE          4          0
      222 DBSNMP   DESKTOP-          0 AE          4          0
      223 SYS      unknown           0 AE          4          0

(3)、使用Oracle企业管理器

使用 Oracle 企业管理器(Oracle Enterprise Manager)也可以得到会话的锁信息。这是获得锁信息最简单的方法之

一。

10.2.2 加锁的方法

(1)、行共享锁

对数据表定义行共享锁后，如果被事务A获得，那么其他事务可以进行并发查询、插入、删除及加锁，但不能以排

他方式存取该数据表。

Lock table xsjbxx in row share mode;

(2)、行排他锁

对数据表定义行排他锁后，如果被事务A获得，那么A事务对数据表中的行数据具有排他权利。其他事务可以对同

一数据表中的其他数据行进行并发查询、插入、修改、删除及加锁，但不能使用以下3种方式加锁。

• 行共享锁。

• 共享行排他锁。

• 行排他锁。

Lock table xsjbxx in row exclusive mode;

(3)、共享锁

对数据表定义共享锁后，如果被事务A获得，其他事务可以执行并发查询和加共享锁但不能修改表，也不能使用以

下3种方式加锁。

• 排他锁。

• 共享行排他锁。

• 行排他锁。

Lock table xsjbxx in share mode;

(4)、共享行排他锁

对数据表定义共享行排他锁后，如果被事务A获得，其他事务可以执行查询和对其他数据行加锁，但不能修改表，

也不能使用以下4种方式加锁。

• 共享锁

• 共享行排他锁。

• 行排他锁。

• 排他锁。

Lock table xsjbxx in share row exclusive mode;

(5)、排他锁

排他锁是最严格的锁。如果被事务A获得，A可以执行对数据表的读写操作，其他事务可以执行查询但不能执行插

入、修改和删除操作。

Lock table xsjbxx in exclusive mode;