oracle学习笔记 事务概述

这节课讲oracle的事务

---

一）什么是事务

oracle的事务单纯从开发角度来讲它比较简单
很多人认为oracle的事务就是两个，一个是commit一个是rollback

oracle中最重要的就是数据的一致性、数据的安全以及oracle数据的优化
这几块是非常重要的

事务它关系到数据的一致性
对oracle数据库来讲或者对任何数据库来讲数据的一致性是重中之重

作为DBA去维护oracle数据库
把数据给搞坏了搞的不一致了这个是不允许的
这是DBA来讲所有错误的一个底线
不能出现数据不一致的情况

事务从简单的概念上来讲就是一组DML语句
所谓DML语句无非就是有insert数据的插入，还有delete，还有update
主要就这几个语句
oracle开发里面还有一些别的DML语句如CALL、EXPLAIN PLAN等
主要的DML语句就是对数据进行操作的增删改

事务就是一组DML语句，看上去是一组DML语句
输入一组DML语句以后然后输入commit，就把这个事务给提交了
rollback就是把这个事务给它回滚了回退了

我们可以这么认为
commit以后这一组DML语句所产生的效果被保存起来了
rollback以后这一组DML语句所修改的数据被回退了，也就是相当于没有修改

事务从简单的理解上就这么简单

事务的操作命令commit、rollback还有SAVEPOINT、ROLLBACK TO、SET TRANSACTION等
它们都是属于oracle的数据控制语言DCL。

二）演示一个事务

简单演示一下

1）搭建环境

先登陆数据库
老师讲课使用的是hr用户
本人数据库安装时创建了这个用户
但还没有使用过hr用户

这里给它解锁并且赋予密码

[oracle@redhat4 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 10:06:56 2017Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter user hr account unlock;User altered.SQL> alter user hr identified by hr;User altered.

然后使用hr用户登陆

[oracle@redhat4 ~]$ sqlplus hr/hrSQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 10:10:57 2017Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>

登陆成功！

这个账户没有激活时sqlplus用hr登陆的时候可能会报一些错
报错的时候提示用户被锁住了
可以给它解开
上面例子我用了两个sql语句也可以用它们合成的一个sql语句
用sys用户登陆进去以后，然后

alter user hr account unlock identified by hr;

把hr用户这个账号解锁，同时把密码给它改成hr
然后hr可以登陆了

hr登陆后执行
select * from user_tables;
显示的结果字段太多影响查看
所以改为

SQL> select table_name from user_tables;TABLE_NAME------------------------------REGIONSLOCATIONSDEPARTMENTSJOBSCOUNTRIESJOB_HISTORYEMPLOYEES7 rows selected.

比老师结果少了一个T1表，因为我还没有建这个表
这里自己建一下

SQL> create table t1(id int,name varchar2(20));Table created.

然后插入一行数据

SQL> insert into t1 values (1,'xkj');1 row created.

提交

SQL> commit;Commit complete.

这样就和老师讲课此表的状态一样了。

新建一个hr用户的会话
再次执行

SQL> select table_name from user_tables;TABLE_NAME------------------------------REGIONSLOCATIONSDEPARTMENTSJOBST1COUNTRIESJOB_HISTORYEMPLOYEES8 rows selected.

和老师的结果一样了，这里有个表t1

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj

t1表中有一行数据

2）事务的开始

这里这个会话打开以后，还没有执行DML语句，只是执行select
这个时候其实还没有开始一个事务
然后我们去开始一个事务

第一个问题什么叫开始一个事务
开始和结束一个事务的标识是什么

一个会话登陆以后
执行的第一条DML语句，就是一个事务的开始

我们先看一下目前数据库系统有什么事务
select xidusn,ubablk,ubafil from v$transaction;

需要系统管理员账号执行此语句才有结果
因为v$transaction动态视图属于系统管理员
非管理员用户想使用这种系统表要得到管理员的授权才可使用
因为实际生产中一般不会给链接的用户这种权利，这里就不演示授权的情况了

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

结果为空，表示目前没有事务

用hr用户执行一条语句
做一个插入

SQL> insert into t1 values(2,'jiagulun');1 row created.

我们执行了一条DML语句了
一个事务开始了

系统账号查询事务

SQL> select xidusn,ubablk,ubafil from v$transaction;    XIDUSN     UBABLK     UBAFIL---------- ---------- ----------         5        627          2

结果出现一个事务
表示一个事务开始了

hr用户insert以后查一下insert里面的数据

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 jiagulun

一个1，一个2

接着hr用户update更新

SQL> update t1 set name='xkjjiagulun' where id=1;1 row updated.

又执行了一条sql语句DML语句

这两条DML语句，一个insert一个 update
都属于一个事务，一直没有提交

insert是一个事务的开始
update和insert同时属于一个事务

这时系统账号查一下当前的事务

SQL> select xidusn,ubablk,ubafil from v$transaction;    XIDUSN     UBABLK     UBAFIL---------- ---------- ----------         5        627          2

事务没有改变还是原来的一个

hr查询一下

SQL> select * from t1;        ID NAME---------- --------------------         1 xkjjiagulun         2 jiagulun

然后hr再删一行数据

SQL> delete from t1 where id=1;1 row deleted.

这时还是一个事务
目前事务还没有结束

3）事务的结束

事务的开始我们看到了
一个事务结束可以用commit

commit以后你所做的前面所做的修改全部保存
全部为永久保存

如果rollback
你前面所做的，这个事务所做的修改，自动取消

比如这里执行rollback

hr用户执行

SQL> rollback;Rollback complete.

系统用户查一下事务

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

没有事务了

rollback取消了，刚才所做的DML语句全当没有发生过
你就认为它没有发生过

hr用户执行

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj

一个事务在rollback的时候结束了

rollback结束一个事务后，也意味着我们可以开始一个新的事务
一个事务结束了，我们可以开始一个新的事务

比如这里hr用户再接着执行

SQL> insert into t1 values(2,'beijing');1 row created.

执行了一条DML语句
因为前面执行rollback的时候一个事务已经结束了
结束以后在insert的时候一个新的事务开始了

系统账号查一下当前的事务

SQL> select xidusn,ubablk,ubafil from v$transaction;    XIDUSN     UBABLK     UBAFIL---------- ---------- ----------         7       1811          2

又有了一个新的事务，并且和前面查询到的事务不一样，不是一个事务

当出现第一个DML的时候事务开始了
一个事务的结束，是rollback或commit都可以
一个事务的结束意味着另外一个事务可以开始了

hr用户接着执行

SQL> insert into t1 values(3,'china');1 row created.

这里hr用户执行了两条DML语句

然后提交

SQL> commit;Commit complete.

提交以后这个事务
上面两个insert所在的事务，就被保存了
也就是说这个事务结束了

hr用户查当前数据

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 beijing         3 china

系统账号查当前的事务

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

这个事务结束意味着一个新的事务可以开始了
事务说白了说的很简单就是这样。

三）保存点savepoint的使用

1）savepoint的实际使用

还有个savepoint命令

我们再开始一个事务

hr用户先查一下

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 beijing         3 china

有三行数据了

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

并且没有任何事务

SQL> insert into t1 values(4,'qingdao');1 row created.

输入一个insert
一个事务开始了

SQL> select xidusn,ubablk,ubafil from v$transaction;    XIDUSN     UBABLK     UBAFIL---------- ---------- ----------         3        735          2

执行savepoint起名随便起一个x1

SQL> savepoint x1;Savepoint created.

再插入数据

SQL> insert into t1 values (5,'jinan');1 row created.

执行到这个时候后悔了
insert语句执行多了
我想回滚
我不想回滚id为4的那一条
只想回滚到id为5的位置

这里在插入id为5的行前建了一个点x1

先查一下

SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 beijing         3 china         4 qingdao         5 jinan

然后回滚

SQL> rollback to x1;Rollback complete.

只回滚到x1的位置

查一下表

SQL>  select * from t1;        ID NAME---------- --------------------         1 xkj         2 beijing         3 china         4 qingdao

数据4存在，数据5不见了已被上面的rollback to 回滚掉

再看一下当前的事务

SQL> select xidusn,ubablk,ubafil from v$transaction;    XIDUSN     UBABLK     UBAFIL---------- ---------- ----------         3        735          2

还是那个事务，没有结束也没有改变
说明rollback to语句可以回滚数据但不会结束事务

这个时候我可以继续回滚，也可以继续往前作

如果rollback to后回滚

SQL> rollback;Rollback complete.SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 china         3 beijing

数据回滚

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

并且事务结束

如果rollback to后提交

SQL> commit;Commit complete.SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 china         3 beijing         4 qingdao

数据被提交

SQL> select xidusn,ubablk,ubafil from v$transaction;no rows selected

并且事务结束

rollback to 是回滚到一个已设置的savepoint的点上
不会结束整个事务
rollback是回滚到整个事务的开始，即使有savepoint点的设置也不会去理会
并且会结束整个事务

2）savepoint的意义

这个地方简单描述一下，还是很有意义的一个
这面看不出有多大意义，但是实际在工作中有帮助

记住了一个事务可以回滚一半

savepoint就是
一个事务开始了
执行了很多
在过程中的一个位置设置savepoint
然后又执行了很多

中间有可能突然发现savepoint点的后面的一条语句有问题

就可以从最后一个修改rollback到savepoint点的位置
这样有问题的语句被回滚
这样可以从savepoint接着往下面做

如我们想对数据库做一个比较大的批量操作一个非常大的一系列DML操作
这个批量操作有可能中间会失败

比如说有一万条sql语句
它作为一个transaction一个事务要执行

为了执行成功为了能够执行的更好一些
我每一千条的时候建一个savepoint

如果我失败的话我最多回滚一千条然后可以继续

否则比如一万条语句执行
我执行到9999条的时候或最后一条执行的时候失败了

或者我在最后一条执行的时候在第一万条执行的时候
我发现9998条语句我执行是失败的
如果没有加savepoint
就要也只能全部回滚

这个地方知道这么个东西
以后你应用的时候自然就会灵活去使用

这是关于事务
从简单来讲就是这些东西

四）事务中的概念

事务的基本概念包括：
一组DML语句 insert、delete、update
和
COMMIT;
ROLLBACK;
SAVEPOINT
ROLLBACK TO [SAVEPOINT]

事务就是一组DML语句
这组DML语句使用COMMIT和ROLLBACK来结束

commit意味着这一组DML语句全部被保存
rollback意味着这一组DML语句全部被回退，全部被后悔了
即使SAVEPOINT加了回退点rollback也是全部回退并且结束事务

五）实例崩溃后事务的回滚

1）事务中出现实例崩溃

有种情况数据库正常运行期间
我执行了一组DML语句
这组DML语句我还没有提交数据库突然崩溃了

举个很有帮助的一个小例子

数据库正常运行期间
数据库实例有
buffercache和数据文件

一个session登上来
每个session登上来以后都会有一个SID（session id）
登完了以后它执行了一组DML语句

本身要执行十条
它执行了五条
因为要执行十条，还没执行完所以还没有提交
数据库突然崩溃了
崩了后数据库重启了
前面讲过实例的崩溃恢复
我们知道这个事务所对应的脏块有可能被回滚回来

这个会话登上来以后
执行了五条DML语句修改了八个块
其中有六个块所对应的日志已经写到redolog里面去了
还有两个块对应的日志在logbuffer里面

因为这个事务没有提交
它的日志它所有的日志不一定全部保存到redolog里面去

然后数据库崩了，崩了以后重启以后
我们知道oracle要跑日志
从LRBA到On Disk RBA跑这个日志

跑完日志以后
前面的六个脏块被构造出来了
也就是修改的前六个块被构造出来了
后面的两个块没有被构造出来，没有被跑出来
因为它在logbuffer里面，没有磁盘日志跑不出来

但这六个块对我们来讲没有意义
因为这个特定的会话，登上来以后执行了五条DML语句
但这个会话的这个事务还没有提交
数据库关了这个会话就死了
这个会话死了
也就是这个会话永远不可能再重现了
即使是这个人重新再登陆上来
它也是启的另外一个会话

2）一个会话不能重现

一个会话一旦死掉
退出以后
这个会话不可能再重新建立
即使是同样的机器同样的人用同样的用户名密码登陆以后
它产生的也是另外一个会话

这个我们可以查一个v$session看一下

SQL> desc v$session Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- SADDR                                              RAW(4) SID                                                NUMBER SERIAL#                                            NUMBER AUDSID                                             NUMBER PADDR                                              RAW(4) USER#                                              NUMBER USERNAME                                           VARCHAR2(30) COMMAND                                            NUMBER OWNERID                                            NUMBER TADDR                                              VARCHAR2(8) LOCKWAIT                                           VARCHAR2(8) STATUS                                             VARCHAR2(8) SERVER                                             VARCHAR2(9) SCHEMA#                                            NUMBER SCHEMANAME                                         VARCHAR2(30) OSUSER                                             VARCHAR2(30) PROCESS                                            VARCHAR2(12) MACHINE                                            VARCHAR2(64) TERMINAL                                           VARCHAR2(30) PROGRAM                                            VARCHAR2(48) TYPE                                               VARCHAR2(10) SQL_ADDRESS                                        RAW(4) SQL_HASH_VALUE                                     NUMBER SQL_ID                                             VARCHAR2(13) SQL_CHILD_NUMBER                                   NUMBER PREV_SQL_ADDR                                      RAW(4) PREV_HASH_VALUE                                    NUMBER PREV_SQL_ID                                        VARCHAR2(13) PREV_CHILD_NUMBER                                  NUMBER MODULE                                             VARCHAR2(48) MODULE_HASH                                        NUMBER ACTION                                             VARCHAR2(32) ACTION_HASH                                        NUMBER CLIENT_INFO                                        VARCHAR2(64) FIXED_TABLE_SEQUENCE                               NUMBER ROW_WAIT_OBJ#                                      NUMBER ROW_WAIT_FILE#                                     NUMBER ROW_WAIT_BLOCK#                                    NUMBER ROW_WAIT_ROW#                                      NUMBER LOGON_TIME                                         DATE LAST_CALL_ET                                       NUMBER PDML_ENABLED                                       VARCHAR2(3) FAILOVER_TYPE                                      VARCHAR2(13) FAILOVER_METHOD                                    VARCHAR2(10) FAILED_OVER                                        VARCHAR2(3) RESOURCE_CONSUMER_GROUP                            VARCHAR2(32) PDML_STATUS                                        VARCHAR2(8) PDDL_STATUS                                        VARCHAR2(8) PQ_STATUS                                          VARCHAR2(8) CURRENT_QUEUE_DURATION                             NUMBER CLIENT_IDENTIFIER                                  VARCHAR2(64) BLOCKING_SESSION_STATUS                            VARCHAR2(11) BLOCKING_INSTANCE                                  NUMBER BLOCKING_SESSION                                   NUMBER SEQ#                                               NUMBER EVENT#                                             NUMBER EVENT                                              VARCHAR2(64) P1TEXT                                             VARCHAR2(64) P1                                                 NUMBER P1RAW                                              RAW(4) P2TEXT                                             VARCHAR2(64) P2                                                 NUMBER P2RAW                                              RAW(4) P3TEXT                                             VARCHAR2(64) P3                                                 NUMBER P3RAW                                              RAW(4) WAIT_CLASS_ID                                      NUMBER WAIT_CLASS#                                        NUMBER WAIT_CLASS                                         VARCHAR2(64) WAIT_TIME                                          NUMBER SECONDS_IN_WAIT                                    NUMBER STATE                                              VARCHAR2(19) SERVICE_NAME                                       VARCHAR2(64) SQL_TRACE                                          VARCHAR2(8) SQL_TRACE_WAITS                                    VARCHAR2(5) SQL_TRACE_BINDS                                    VARCHAR2(5)

数据库当前所有的会话都在这里面
它有session ID即SID

可以使用
select a.sid,a.serial# from vsessiona,vmystat b where a.sid=b.sid;
简单的看一下当前所有的会话的sid和它的序列号

执行结果

SQL> select a.sid,a.serial# from v$session a,v$mystat b where a.sid=b.sid;       SID    SERIAL#---------- ----------       147        538       147        538       147        538...       147        538       147        538363 rows selected.

结果太多只列出一部分
SID是147
SERIAL是538
这就是当前我的session

因为vsession，vmystat是系统视图
所以hr用户执行这条语句会提示视图不存在

当前会话关闭
再新建一个系统管理员会话
在此会话中执行这条语句

SQL> select a.sid,a.serial# from v$session a,v$mystat b where a.sid=b.sid;       SID    SERIAL#---------- ----------       144        734       144        734       144        734...       144        734       144        734363 rows selected.

这个新会话
SID是144
serial是 734

退出这个会话

SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options

原来的会话已经死掉了

再建立一个新会话

[oracle@redhat4 ~]$ sqlplus hr/hrSQL> connect / as sysdba

在这个会话中使用上面的sql查询

SQL> select a.sid,a.serial# from v$session a,v$mystat b where a.sid=b.sid;       SID    SERIAL#---------- ----------       129       1098       129       1098       129       1098...       129       1098       129       1098363 rows selected.

可以看到
会话和以前不一样了

我们把结果复制下来
现在的会话是
129 1098
这是会话的编号

然后退出
SQL> exit
还是同样的机器
还是同样的用户名密码
[oracle@redhat4 ~]$ sqlplus / as sysdba

然后重新执行一下上面的sql语句

SQL> select a.sid,a.serial# from v$session a,v$mystat b where a.sid=b.sid;       SID    SERIAL#---------- ----------       129       1156       129       1156       129       1156...       129       1156       129       1156363 rows selected.

结果是
129 1156

把这两个结果放到一起进行比较
第一个结果
129 1098
第二个结果
129 1156

我们发现结果不一样
serial不一样
一个是1098一个是1156

也就是说
一句话
一个会话死掉以后，无论是退出还是数据库崩溃，这个会话不可能重现了

那么这个会话不可能重现了
意味着当年没有完成的事务不能再回来了
也就是说这个事务不可能再完成了

事务所依赖的会话死掉了
这个事务不可能再完成了
所以这个事务只能被回滚

3）未完成事务的回滚

我们再来简单描述一下

数据库正常运行期间
一个会话登陆上来，会话有SID有serial
这个会话执行了一个事务开始了一个事务

这个事务有十条DML语句，但它只执行了五条产生了八个脏块
其中有六个脏块产生的日志已经写到磁盘上，有两个还在buffer里面
数据库突然崩了
崩了以后，然后数据库重启
重启了以后
我们知道oracle会跑日志
跑日志的时候
前六个脏块又被构造出来了
但是有一个问题我们有一个原则
一个会话一旦死掉以后这个会话不可能再生
会话不可能再生
这个会话当年所执行的没有完成的事务不可能再完成了
这个时候这个事务只能被回滚
oracle会自动的对这个事务回滚

这个事务这个SID
所执行的DML语句
其中有六个块被构造出来了
将来这六个块所做的修改会自动被rollback

对oracle数据库来讲
如果数据库崩了它首先要前滚
前滚完了对未完成的事务还要回滚

一个事务要么全成功要么全失败
不可能出现一个事务运行一半的情况
也就是说在oracle数据库里面
不可能出现一个事务跑了一半然后被保存了然后可以被访问
一个事务要么全成功要么全失败
这是事务的基本概念

事务有commit或者rollback可以提交和回滚
另外一种情况
数据库如果突然崩了
这时候数据库所有未提交的事务会被自动回滚
这也是一种被回滚的情况

五）DDL语句的提交

还有DDL语句
就是create建表的和删表的等等这些语句

我执行了一条DDL
比如create
这时oracle自动的在执行create以前，会自动的执行commit
执行完create以后，也自动的执行一个commit
执行DDL时候自动对前面的操作自动提交

举个例子

hr用户

SQL> desc t1; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER(38) NAME                                               VARCHAR2(20)SQL> select * from t1;        ID NAME---------- --------------------         1 xkj         2 beijing         3 china         4 qingdao

然后

SQL> delete from t1 where id=1;1 row deleted.

删除了一行数据，没有提交

这时候建另外一个表

SQL> create table t2(id int,name varchar2(20));Table created.

在执行delete以后
接着执行一个create

oracle在执行DDL语句前面会自动加一个隐含的一个commit
后面也有一个commit

也就是create前面的delete不小心被提交了
这也是一种提交方式

这时候有可能出现问题
比如我突然后悔了

SQL> select * from t1;        ID NAME---------- --------------------         2 beijing         3 china         4 qingdao

发现坏了，不小心把这个数据删了
我想回滚

SQL>  rollback;Rollback complete.

再去看

SQL> select * from t1;        ID NAME---------- --------------------         2 beijing         3 china         4 qingdao

发现数据没有回来
也就是说在执行DDL的时候，已经隐含的被提交了

六）正常退出和事务提交

另外一个退出exit
大家要注意
有时候这个地方很容易犯错

SQL> commit;Commit complete.

提交了

SQL> select * from t1;        ID NAME---------- --------------------         2 beijing         3 china         4 qingdao

再删一条数据

SQL> delete from t1 where id=2;1 row deleted.SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

2给删了
这时候我修改了但是这个事务没有提交
我退出

SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options

然后再去登陆

[oracle@redhat4 ~]$ sqlplus hr/hr

看一下

SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

发现事务退出以后事务被提交了

执行delete时并没有提交
然后exit时正常退出，发现事务被提交了，保存生效了

也就是说
不小心正常退出以后
我们所做的我们的事务被隐含的提交了
这个地方比较可怕

我们有的DBA作完操作以后吃饭去了exit退出了
退出以后可能它的操作不想保存但结果保存了

七）想模仿异常退出却都造成正常的退出

1）x窗口

还有异常退出

SQL> delete from t1 where id=3;1 row deleted.

删了以后异常退出
比如把建立会话的putty客户端这个窗口直接给X掉

但是这时PuTTY提示
确定要关闭本会话么？
既然有了这个提示表示也就并非异常退出了
可能这时对会话执行了退出命令

这时再去看
[oracle@redhat4 ~]$ sqlplus hr/hr

查一下

SQL> select * from t1;        ID NAME---------- --------------------         4 qingdao

发现第3号也被提交了

2）ctrl+d

会话中按ctrl+d组合键仍是一样属于正常退出

SQL> delete from t1 where id=4;1 row deleted.SQL> select * from t1;no rows selected

这时按ctrl+d退出

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options

重新登陆

[oracle@redhat4 ~]$ sqlplus hr/hrSQL> select * from t1;no rows selected

结果仍然被提交了
ctrl+d执行时系统有退出提示
说明对会话进行了退出操作没有构成异常退出

这些方法在客户端效果基本是一样的，都不会造成异常退出，实际都执行了退出操作。
这可能和各种客户端的退出设置有关

3）使用windows任务管理器

为了模仿异常退出
尝试使用windows 任务管理器
一、在任务管理器应用程序标签页结束任务
二、在进程标签页找到相关进程然后结束进程
这两种方法分别实验

先插入两行数据

SQL> insert into t1 values(3,'china');1 row created.SQL> insert into t1 values(4,'qingdao');1 row created.

然后提交

SQL> commit;Commit complete.SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

以这两行数据作为实验环境

这时删掉一行数据

SQL> delete from t1 where id=3;1 row deleted.

从这个地方我们分别采取结束任务和结束进程的方法进行实验

1、因为此会话是在putty中建立的
所以在windows 任务管理器中直接结束这个putty任务
会出现一个提示：确定要关闭本会话吗？
既然有退出的提示
结果

SQL> select * from t1;        ID NAME---------- --------------------         4 qingdao

仍然被提交了

2、在windows 任务管理器中直接结束这个putty的进程
出现任务管理器警告
警告：终止进程会导致不希望发生的结果，包括数据丢失和系统不稳定。
在被终止前，进程将没有机会保存其状态和数据。
确实想终止该进程吗？
选是结束进程
结果

SQL> select * from t1;        ID NAME---------- --------------------         4 qingdao

也被提交了

用任务管理器关掉客户端程序仍然没有造成异常退出。

4）linux系统退出

我在linux系统中打开终端，在终端中打开sqlplus
实验结果仍然一样
X掉终端和ctrl+d退出会话都提交了

上面我们做的实验针对我们的环境里面
各种退出方式
都造成了自动提交

这是关于提交和回滚

八）建议

为了避免刚才隐式的提交和回滚
老师给大家强烈的提一个建议

你做的任何DML语句都要显式的提交和回滚
执行了一段以后抓紧时间提交
或者提交或者回滚
这个很关键，一定要做这个事情
不要让oracle做隐式的提交回滚

可能最后你没有注意到而导致数据库的数据的异常

因为这种事情以前出来过好多情况
很多用户找我们的时候
最后发现出现这个问题

九）logminer工具

这个事务都作完了怎么知道
我们可以查
oralce有个logminer工具包
可以对你所做的所有的操作进行一个事后的跟踪查看
oracle 11G更好了
logminer直接通过图形界面架设出来

原来我们需要使用一个包
现在11g以后直接用图形界面全部展示出来

你做过什么事情
你对oracle所做的所有的操作
通过logminer都可以看出来

要使用这个工具需要自己安装它的两个包
SYS用户下

用$ORACLE_HOME/rdbms/admin/dbmslm.sql
来创建DBMS_LOGMNR包，该包用来分析日志文件

用$ORACLE_HOME/rdbms/admin/dbmslmd.sql
创建DBMS_LOGMNR_D包，该包用来创建数据字典文件

这样logminer就可以使用了
和其它oralce的工具包一样使用时有它的特定方法和步骤，这里不细讲了
其实际是一个分析oracle日志的工具

LogMiner是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，
使用该工具可以轻松获得Oracle在线重作日志文件(redo log file)和归档日志文件(archive log file)中的具体内容，
可以分析出所有对于数据库操作的DML语句，还可分析得到一些必要的回滚SQL语句等，从9i也可以分析ddl语句了。
特别适用于调试、审计或者回退某个特定的事务
Logminer还可以在很多条件限制下来分析其它数据库的日志文件

十）实现异常退出

因为课程中对会话异常退出没有实现
下面做一个异常退出的实验

先说明几个概念：
1.spid(system process id) 操作系统层的进程id
2.pid(process id) 基于oracle的，oracle给自己的进程的一个编号
3.sid(session id) oracle给连接上来的会话分配的一个ID
ID是英文Identifier的缩写

这几个概念在下面要用到

经过实践做出了异常退出的例子

1）sqlplus中使用alter system kill session ‘sid,serial#’

首先找到会话的sid，和serial#
然后使用
alter system kill session ‘sid,serial#’ immediate;
命令杀掉进程，这时会话异常终止退出
再次登录
数据产生了回滚。

因为是演示异常退出对事务的作用
这里对如何得到会话的sid和会话所在进程的spid就不深究了

获得当前会话的sid可以使用
select sid from v$mystat where rownum=1;

select userenv(‘sid’) from dual;

select sid from v$session where audsid=userenv(‘sessionid’);
等

hr用户不是系统用户可以使用
select userenv(‘sid’) from dual;
来查当前的会话sid

hr用户的连接中

SQL> select userenv('sid') from dual;USERENV('SID')--------------           138

在系统账号的连接中
使用SELECT sid,serial# FROM V$SESSION WHERE sid=138;
查询sid对应的serial#

SQL> SELECT sid,serial# FROM V$SESSION WHERE sid=138;       SID    SERIAL#---------- ----------       138        944

得到了hr用户连接的SID和SERIAL#
然后可以使用这个数据杀掉进程实验会话的异常退出

在hr用户下当前

SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

然后删一行数据

SQL> delete t1 where id=3;1 row deleted.SQL> select * from t1;        ID NAME---------- --------------------         4 qingdao

不提交，在此等待

然后系统管理员杀掉这个会话

SQL> alter system kill session '138,944';System altered.

在hr用户的连接中执行

SQL> select * from t1;select * from t1*ERROR at line 1:ORA-00028: your session has been killed

提示会话已被杀死
这时退出
SQL> exit
然后重新连接
sqlplus hr/hr

查询t1表的情况

SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

id为3的行仍然在
数据产生了回滚
说明会话异常退出会产生回滚

2）linux中使用kill命令

也可以使用linux的kill命令杀进程

SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

删除一行

SQL> delete t1 where id=3;1 row deleted.SQL> select * from t1;        ID NAME---------- --------------------         4 qingdao

在管理员会话中查hr用户的SPID

SQL> SELECT pid,spid FROM V$PROCESS WHERE ADDR IN (SELECT PADDR FROM V$SESSION where username ='HR');       PID SPID---------- ------------        23 14454

进入linux的管理员账号在linux中执行

[oracle@redhat4 ~]$ kill -9 14454

回到hr用户的会话连接

SQL> select * from t1;select * from t1*ERROR at line 1:ORA-03135: connection lost contact

会话已经失去连接

重新登录

SQL> exit[oracle@redhat4 ~]$ sqlplus hr/hr

再次查询

SQL> select * from t1;        ID NAME---------- --------------------         3 china         4 qingdao

删除行产生了回滚

3）简单查询方法：

有一个命令可以查出我们需要的当前所有用户的会话的信息

select s.username, s.osuser, s.sid, s.serial#, p.spid from vsessions,vprocess p
where s.paddr = p.addr and s.username is not null;

所有用户会话的sid,serial#，SPID都可以得到

管理员只查询hr用户的sid,serial#信息
select saddr,sid,serial#,paddr,username,status from v$session where username =upper(‘hr’);

得到这些信息后
在oracle中执行alter system kill session ‘sid,serial#’;
在linux系统中执行kill -9 spid
就可以了

强行杀掉会话进程也可以使用
ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;命令
杀掉连接的会话，它等价于从操作系统杀掉进程

十一）oracle提交数据的类型

这节课内容中有oracle提供的显示提交和隐式提交，初学者对自动提交也比较关心
这里把提交数据的三种类型总结一下：

显式提交、隐式提交及自动提交

1、显式提交：用COMMIT命令直接完成的提交为显式提交。
其格式为：SQL>COMMIT；
2、隐式提交：用SQL命令间接完成的提交为隐式提交。
这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
执行这些命令前oracle会执行一次commit，命令执行完后oracle也会自动执行一次commit
3、自动提交：若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。
其格式为：SQL>SET AUTOCOMMIT ON；
它只是DML语句执行后的自动提交，对会话的正常退出或异常退出都没有作用。
正常退出属于隐式提交的范围如exit和quit命令都是隐式提交的命令
异常退出AUTOCOMMIT有没有作用无法做出实例，在每个DML语句执行完都自动提交了，即使异常退出了，也没什么可提交的或回滚的了

这个参数属于客户端sqlplus，oracle自身并没有这个参数

查看这个参数的值：
SHOW AUTOCOMMIT

设置这个参数的值：
SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }

举一个使用这个参数的例子

设置这个参数前的操作

SQL> delete from t1 where id=17;1 row deleted.

设置为on后的操作

SQL> delete from t1 where id=14;1 row deleted.Commit complete.

可以看到后面多了一个
Commit complete.
的提示

不建议在sqlplus中使用autocommit 功能，这样会削弱用户对事务提交的控制权。

自动提交初学者觉得这样省事，实际在生产中这是非常不合理的
每条DML都自动提交会占用大量的系统软硬件资源
再者实际生产中完成一个任务往往需要一组DML语句，用它们来构成一个事务
如果一个整体的程序未执行完
使用自动提交的话前面的一半操作生效了，但后面的操作未作完或者无法完成了
这样不符合一个事务整体性的要求
非常容易产生错误的数据和导致数据不一致
使用这个参数不合理，实践中不应该使用。

2017年6月7日
文字：韵筝