事务会把数据库从一种一致状态转变为另一种一致状态
Oracle中的事务体现了所有必要的ACID特征：
1. 原子性（atomicity）：事务中的所有动作要么都发生，要么都不发生。
2. 一致性（consistency）：事务将数据库从一种一致状态转变为下一种一致状态。
3. 隔离性（isolation）：一个事务的影响在该事务提交前对其他事务都不可见。
4. 持久性（durability）：事务一旦提交，其结果就是永久性的。
1. 事务控制语句
Oracle中 事务会在修改数据的第一条语句处开始（也就是得到TX锁的第一条语句）。也可以使用SET TRANSACTION或DBMS_TRANSACTION包来显示地开始一个事务，但是这一步并不是必要的。如果发出COMMIT或ROLLBACK语句，就会显式地结束一个事务。
注意 ROLLBACK TO SAVEPOINT命令不会结束事务！正确地写为ROLLBACK（只有这一个词）才能结束事务。
注意 ROLLBACK TO SAVEPOINT命令不会结束事务！正确地写为ROLLBACK（只有这一个词）才能结束事务。
一定要显式地使用COMMIT或ROLLBACK来终止你的事务；否则，你使用的工具/环境就会从中挑一个来结束事务。如果正常地退出SQL*Plus会话，而没有提交或回滚事务，SQL*Plus就会认为你希望提交前面做的工作，并为你完成提交。另一方面，如果你只是退出一个Pro*C程序，就会发生一个隐式的回滚。
Oracle中 的事务是原子性的，构成事务的每条语句都会提交（成为永久），或者所有语句都回滚。这种保护还延伸到单个的语句。一条语句要么完全成 功，要么这条语句完全回滚。注意，我说的是“语句”回滚。如果一条语句失败，并不会导致先前已经执行的语句自动回滚。它们的工作会保留，必须由你来提交或 回滚。
1.
COMMIT：也可以写为COMMIT WORK。COMMIT会结束你的事务，并使得已做的所有修改成为永久性的（持久保存）。COMMIT语句还有一些扩展用于分布式事务中。利用这些扩展，允许增加一些有意义的注释为COMMIT加标签（对事务加标签），以及强调提交一个可疑的分布式事务。
2. ROLLBACK：你也可以写为ROLLBACK WORK。回滚会结束你的事务，并撤销正在进行的所有未提交的修改。 为此要读取存储在回滚段/undo段中的信息， 并把数据库块恢复到事务开始之前的状态。
3. SAVEPOINT： 允许你在事务中创建一个“标记点”（marked point），一个事务中可以有多个SAVEPOINT。
4. ROLLBACK TO <SAVEPOINT>：可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。所以，可以发出两条UPDATE语句，后面跟一个SAVEPOINT，然后又是两条DELETE语句。如果执行DELETE语句期间出现了某种异常情况，而且你捕获到这个异常，并发出ROLLBACK TO SAVEPOINT命令，事务就会回滚到指定的SAVEPOINT，撤销DELETE完成的所有工作，而UPDATE语句完成的工作不受影响。
5. SET TRANSACTION：允许你设置不同的事务属性，如事务的隔离级别以及事务是只读的还是可读写的。使用手动undo管理时，还可以使用这个来指示事务使用某个特定的undo段，不过不推荐这种做法。
2. ROLLBACK：你也可以写为ROLLBACK WORK。回滚会结束你的事务，并撤销正在进行的所有未提交的修改。 为此要读取存储在回滚段/undo段中的信息， 并把数据库块恢复到事务开始之前的状态。
3. SAVEPOINT： 允许你在事务中创建一个“标记点”（marked point），一个事务中可以有多个SAVEPOINT。
4. ROLLBACK TO <SAVEPOINT>：可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。所以，可以发出两条UPDATE语句，后面跟一个SAVEPOINT，然后又是两条DELETE语句。如果执行DELETE语句期间出现了某种异常情况，而且你捕获到这个异常，并发出ROLLBACK TO SAVEPOINT命令，事务就会回滚到指定的SAVEPOINT，撤销DELETE完成的所有工作，而UPDATE语句完成的工作不受影响。
5. SET TRANSACTION：允许你设置不同的事务属性，如事务的隔离级别以及事务是只读的还是可读写的。使用手动undo管理时，还可以使用这个来指示事务使用某个特定的undo段，不过不推荐这种做法。
2. 原子性
语句级原子性
scott@ORCL>create table t2 ( cnt int );
表已创建。
scott@ORCL>insert into t2 values ( 0 );
已创建 1 行。
scott@ORCL>commit;
提交完成。
scott@ORCL>create table t ( x int check ( x>0 ) );
表已创建。
scott@ORCL>create trigger t_trigger
2 before insert or delete on t for each row
3 begin
4 if ( inserting ) then
5 update t2 set cnt = cnt +1;
6 else
7 update t2 set cnt = cnt -1;
8 end if;
9 dbms_output.put_line( 'I fired and updated ' ||
10 sql%rowcount || ' rows' );
11 end;
12 /
触发器已创建scott@ORCL>set serveroutput on
scott@ORCL>insert into t values (1);
I fired and updated 1 rows
已创建 1 行。
scott@ORCL>insert into t values(-1);
I fired and updated 1 rows
insert into t values(-1)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0015511)
scott@ORCL>select * from t2;
CNT
----------
1为了得到这种语句级原子性，Oracle悄悄地在每个数据库调用外面包了一个SAVEPOINT。前面的两个INSERT实际上处理如下：
Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( -1 );
If error then rollback to statement2;过程级原子性
Oracle把PL/SQL匿名块也当作是语句scott@ORCL>create or replace procedure p
2 as
3 begin
4 insert into t values ( 1 );
5 insert into t values (-1 );
6 end;
7 /
过程已创建。
scott@ORCL>select * from t;
未选定行
scott@ORCL>select * from t2;
CNT
----------
0scott@ORCL>begin
2 p;
3 end;
4 /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0015511)
ORA-06512: 在 "SCOTT.P", line 5
ORA-06512: 在 line 2
scott@ORCL>select * from t;
未选定行
scott@ORCL>select * from t2;
CNT
----------
0scott@ORCL>begin
2 p;
3 exception
4 when others then null;
5 end;
6 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL 过程已成功完成。
scott@ORCL>select * from t;
X
----------
1
scott@ORCL>select * from t2;
CNT
----------
1
对此可以用另一种方式编写代码，将语句级原子性恢复为整个PL/SQL块级原子性，如下所示：
为了实现这个目标，事务也是原子性的，事务完成的所有工作要么完全提交并成为永久性的，要么会回滚并撤销。像语句一样，事务是一个原子性的工作单元。提交一个事务后，接收到数据库返回的“成功”信息后，你就能知道事务完成的所有工作都已经成为永久性的。
scott@ORCL>begin
2 savepoint sp;
3 p;
4 exception
5 when others then
6 rollback to sp;
7 end;
8 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL 过程已成功完成。
scott@ORCL>select * from t;
未选定行
scott@ORCL>select * from t2;
CNT
----------
0事务级原子性
事务（也就是一组SQL语句作为一个工作单元一同执行）的总目标是把数据库从一种一致状态转变为另一种一致状态。为了实现这个目标，事务也是原子性的，事务完成的所有工作要么完全提交并成为永久性的，要么会回滚并撤销。像语句一样，事务是一个原子性的工作单元。提交一个事务后，接收到数据库返回的“成功”信息后，你就能知道事务完成的所有工作都已经成为永久性的。
3.完整性约束和事务
默认情况下，完整性约束会在整个SQL语句得到处理之后才进行检查。也有一些可延迟的约束允许将完整性约束的验证延迟到应用请求时（发出一个SET CONSTRAINTS ALL IMMEDIATE命令）才完成，或者延迟到发出COMMIT时再检查。
3. IMMEDIATE约束
一般情况，约束都是IMMEDIATE模式。在这种情况下，完整性约束会在整个SQL语句得到处理之后立即检查。如果一个PL/SQL存储过程中有多条SQL语句，那么在每条SQL语句执行之后都会立即验证其完整性约束，而不是在这个存储过程完成后才检查它。
为什么约束要在SQL语句执行之后才验证呢？为什么不是在SQL语句执行期间验证？这是因为， 一条语句可能会使表中的各行暂时地“不一致”。尽管一条语句全部完成后的最终结果是对的，但如果查看这条语句所做的部分工作，会导致Oracle拒绝这个结果。例如，假设有下面这样一个表：
为什么约束要在SQL语句执行之后才验证呢？为什么不是在SQL语句执行期间验证？这是因为， 一条语句可能会使表中的各行暂时地“不一致”。尽管一条语句全部完成后的最终结果是对的，但如果查看这条语句所做的部分工作，会导致Oracle拒绝这个结果。例如，假设有下面这样一个表：
scott@ORCL>create table t ( x int unique );
表已创建。
scott@ORCL>insert into t values ( 1 );
已创建 1 行。
scott@ORCL>insert into t values ( 2 );
已创建 1 行。scott@ORCL>update t set x = x+1;
已更新2行。3.2 DEFERRABLE约束和级联更新
scott@ORCL>create table p
2 ( pk int primary key )
3 /
表已创建。
scott@ORCL>create table c
2 ( fk constraint c_fk
3 references p(pk)
4 deferrable
5 initially immediate
6 )
7 /
表已创建。
scott@ORCL>insert into p values ( 1 );
已创建 1 行。
scott@ORCL>insert into c values ( 1 );
已创建 1 行。
scott@ORCL>update p set pk = 2;
update p set pk = 2
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.C_FK) - 已找到子记录scott@ORCL>set constraint c_fk deferred;
约束条件已设置。
scott@ORCL>update p set pk = 2;
已更新 1 行。
scott@ORCL>set constraint c_fk immediate;
set constraint c_fk immediate
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.C_FK) - 未找到父项关键字scott@ORCL>update c set fk = 2;
已更新 1 行。
scott@ORCL>set constraint c_fk immediate;
约束条件已设置。
scott@ORCL>commit;
提交完成。
4.不好的事务习惯
A.在循环中提交
如果交给你一个任务，要求更新多行，大多数程序员都会力图找出一种过程性方法，通过循环来完成这个任务，这样就能提交多行。这样做的两个主要原因是：
1. 频繁地提交大量小事务比处理和提交一个大事务更快，也更高效。
2. 没有足够的undo空间。
这 两个结论都存在误导性。另外，如果提交得太过频繁，很容易让你陷入危险，倘若更新做到一半的时候失败了，这会使你的数据库处于一种“未知”的状态。要编写一个过程从而在出现失败的情况下能平滑地重启动，这需要复杂的逻辑。到目前为止，最好的方法是按业务过程的要求以适当的频度提交，并且相应地设置undo段大小。
下面将更详细地分析这些问题。
1. 频繁地提交大量小事务比处理和提交一个大事务更快，也更高效。
2. 没有足够的undo空间。
这 两个结论都存在误导性。另外，如果提交得太过频繁，很容易让你陷入危险，倘若更新做到一半的时候失败了，这会使你的数据库处于一种“未知”的状态。要编写一个过程从而在出现失败的情况下能平滑地重启动，这需要复杂的逻辑。到目前为止，最好的方法是按业务过程的要求以适当的频度提交，并且相应地设置undo段大小。
下面将更详细地分析这些问题。
a.1 性能影响
如果频繁地提交，通常并不会更快。一般地，在一个SQL语句中完成工作几乎总是更快一些。可以通过一个小例子来说明，假设我们有一个表T，其中有大量的行，而且我们希望为该表中的每一行更新一个列值。这里使用两个表T1和T2来进行演示：
scott@ORCL>create table t1 as select * from all_objects;
表已创建。
scott@ORCL>exec dbms_stats.gather_table_stats( user, 'T1' );
PL/SQL 过程已成功完成。
scott@ORCL>create table t2 as select * from t1;
表已创建。
scott@ORCL>exec dbms_stats.gather_table_stats( user, 'T2' );
PL/SQL 过程已成功完成。scott@ORCL>set timing on
scott@ORCL>update t1 set object_name = lower(object_name);
已更新71896行。
已用时间: 00: 00: 03.38scott@ORCL>begin
2 for x in ( select rowid rid, object_name, rownum r from t2 )
3 loop
4 update t2
5 set object_name = lower(x.object_name)
6 where rowid = x.rid;
7 if ( mod(x.r,100) = 0 ) then
8 commit;
9 end if;
10 end loop;
11 commit;
12 end;
13 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.11scott@ORCL>declare
2 type ridArray is table of rowid;
3 type vcArray is table of t2.object_name%type;
4
5 l_rids ridArray;
6 l_names vcArray;
7
8 cursor c is select rowid, object_name from t2;
9
10 begin
11 open c;
12 loop
13 fetch c bulk collect into l_rids, l_names LIMIT 100;
14 forall i in 1 .. l_rids.count
15 update t2
16 set object_name = lower(l_names(i))
17 where rowid = l_rids(i);
18 commit;
19 exit when c%notfound;
20 end loop;
21 close c;
22 end;
23 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 04.76下面再对这个讨论做个补充，给出一个对应的例子。如果要针对一个行子集（有一个WHERE 子句）执行先前的UPDATE语句，而其他用户正在修改这个UPDATE在WHERE子句中使用的列，就可能需要使用一系列较小的事务而不是一个大事务，或者更适合在执行大量更新之前先锁定表。这样做的目标是减少出现重启动的机会。如果要UPDATE表中的大量行，这会导致我们使用LOCK TABLE命 令。不过，根据我的经验，这种大量更新或大量删除（只有这些语句才可能遭遇重启动）都是独立完成的。一次性的大量更新或清除旧数据通常不会在活动高发期间 完成。实际上，数据的清除根本不应受此影响，因为我们一般会使用某个日期字段来定位要清除的信息，而其他应用不会修改这个日期数据。
a.2 Snapshot Too Old错误
开发人员循环提交更新的第二个原因，试图节俭地使用“受限资源”（ undo段）。这是一个配置问题； 你需要确保有足够的undo空间来适当地确定事务的大小。如果在循环中提交，一般会更慢，不仅如此，这也是导致让人胆战心惊的ORA-01555错误的最常见的原因。
Oracle的多版本模型会使用undo段数据依照语句或事务开始时的原样来重建块（究竟是语句还是事务，这取决于隔离模式）。如果必要的undo信息不再存在，你就会收到一个ORA-01555：snapshot too old错误消息，查询也不会完成。所以，如果像前面的那个例子一样，你一边在读取表，一边在修改这个表，就会同时生成查询所需的undo信息。UPDATE生成了undo信息，你的查询可能会利用这些undo信息来得到待更新数据的读一致视图。如果提交了所做的更新，就会允许系统重用刚刚填写的undo段空间。如果系统确实重用了undo段空间，擦除了旧的undo数据（查询随后要用到这些undo信息），你就有大麻烦了。SELECT会失败，而UPDATE也会中途停止。这样就有了一个部分完成的逻辑事务，而且可能没有什么好办法来重启动。
下面通过一个小演示例子来看看这个概念具体是怎样的：
scott@ORCL>create table t as select * from all_objects;
表已创建。
scott@ORCL>create index t_idx on t(object_name);
索引已创建。
scott@ORCL>exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL 过程已成功完成。
scott@ORCL>create undo tablespace undotbs2 datafile
2 'D:\app\Administrator\oradata\orcl\undotbs2.dbf' size 5M
3 autoextend off
4 /
表空间已创建。
scott@ORCL>alter system set undo_tablespace = undotbs2;
系统已更改。scott@ORCL>begin
2 for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
3 from t
4 where object_name > ' ' )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
第 1 行出现错误:
ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小
ORA-06512: 在 line 2这是一个特意构造的例子，纯粹是为了说明如何以一种可靠的方式发生这个错误。UPDATE语句正在生成undo信息。我只能用一个很小的undo表空间（大小为5MB）。这里多次在undo段中回绕，因为undo段要以一种循环方式使用。 每次提交时，都允许Oracle覆盖前面生成的undo数据。最终，可能需要某些已生成的undo数据，但是它已经不在了（即已经被覆盖），这样就会收到ORA-01555错误。
在这种情况下，如果没有在上一个例子的第10行提交，就会收到以下错误：
begin
*
第 1 行出现错误:
ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDOTBS2' 中)
ORA-06512: 在 line 6报ORA-01555错误的例子会使更新处于一种完全未知的状态。有些工作已经做了，而有些还没有做。
如果在游标的FOR循环中提交，要想避免ORA-01555，绝对是无计可施。
ORA-30036错误是可以避免的，只需在系统中分配适当的资源。通过设置正确的大小就可以避免这个错误；但是第一个错误（ORA-01555）则不然。另外，即使我未能避免ORA-30036错误，至少更新会回滚，数据库还是处于一种已知的一致状态，而不会半路停在某个大更新的中间。
这里的关键是，无法通过频繁提交来“节省”undo空间——你会需要这些undo信息。收到ORA-01555错误时，我运行的是单用户系统。只需一个会话就能导致这个错误，在实际中，很多情况下甚至各个会话都能导致自己的ORA-01555错误。开发人员和DBA需要协作来适当地确定这些段的大小，从而完成所需完成的任务。你必须通过分析系统来发现最大的事务是什么，并适当地为之确定段大小。动态性能视图V$UNDOSTAT对于监视所生成的undo数量可能非常有用，你可以用来监视运行时间最长的查询的持续时间。许多人认为像临时段、undo和redo都是“开销”，应该分配尽可能小的存储空间。这与计算机行业2000年1月1日遭遇的千年虫问题同出一辙，所有问题都只是因为想在日期字段中节省2个字节。数据库的这些组件不是开销，而是系统的关键组件。必须适当地设置大小。
a.3 可重启动的过程需要复杂的逻辑
如果采用“在逻辑事务结束之前提交”的方法，最验证的问题是： 如果UPDATE半截失败了，这会经常将数据库置于一种未知的状态中。除非你提取对此做了规划，否则很难重启动这个失败的过程，让它从摔倒的地方再爬起来。例如，假设我们不是像上一个例子那样对列应用LOWER()函数，而是应用了以下的列函数：last_ddl_time = last_ddl_time + 1;scott@ORCL>create table to_do
2 as
3 select distinct substr( object_name, 1,1 ) first_char
4 from T
5 /
表已创建。
scott@ORCL>begin
2 for x in ( select * from to_do )
3 loop
4 update t set last_ddl_time = last_ddl_time+1
5 where object_name like x.first_char || '%';
6
7 dbms_output.put_line( sql%rowcount || ' rows updated' );
8 delete from to_do where first_char = x.first_char;
9
10 commit;
11 end loop;
12 end;
13 /
34561 rows updated
71896 rows updated
1581 rows updated
1115 rows updated
3163 rows updated
45 rows updated
328 rows updated
3 rows updated
2 rows updated
726 rows updated
3089 rows updated
1258 rows updated
56 rows updated
82 rows updated
PL/SQL 过程已成功完成。如果能在SQL中完成，那就在SQL里完成。如果不能在SQL中完成，就用PL/SQL实 现。要用尽可能少的代码来完成，另外应当分配充分的资源。一定要考虑到万一出现错误会怎么样。应当正确地设置undo段的大小。 如果有非常大的表需要更新，就应该使用分区，这样就能单独地更新各个分区。甚至可以使用并行DML来执行更新。
B 使用自动提交
流行的编程API（ODBC和JDBC）,默认地”自动提交“（autocommit）
在JDBC中打开一个连接之后紧接着应该有下面这样几行代码：
connection conn = DriverManager.getConnection("jdbc:oracle:oci:@database","scott","tiger");
conn.setAutoCommit (false);5.分布式事务
Oracle有很多很好的特性，其中之一就是能够透明地处理分布式事务。在一个事务的范围内，可以更新多个不同数据库中的数据。提交时，要么提交所有实例中的更新，要么一个都不提交（它们都会回滚）。为此，我不需要另外编写任何代码：只是“提交“就行了。
Oracle中分布式事务的关键是数据库链接（database link）。数据库链接是一个数据库对象，描述了如何从你的实例登录到另一个实例。一旦建立了一个数据库链接，访问远程对象就很简单了，如下：
select * from T@another_database;这会从数据库链接ANOTHER_DATABASE所定义数据库实例的表T中选择。一般地，你会创建表T的一个视图（或一个同义词），来“隐藏“T是一个远程表的事实。例如，可以发出以下命令，然后就可以像访问本地表一样地访问T了：
create synonym T for T@another_database;既然建立了这个数据库链接，而且能读取一些表，还能够修改这些表（当然，假设有适当的权限）。现在执行一个分布式事务与执行一个本地事务没有什么两样。我要做的只是：
update local_table set x = 5;
update remote_table@another_database set y = 10;
commit;2PC会限制可能出现的严重错误的窗口（时间窗）。在2PC上“投票“之前，任何分布式错误都会导致所有这点回滚。对于事务的结果来说，这里不存在疑义。在提交或回滚之后，分布式事务的结果同样没有疑义。只有一个非常短的时间窗除外，此时协调器要收集投票结果，只有在这个时候如果失败，结果可能有疑义。
例如，假设有3个站点参与一个事务，其中站点1是协调器。站点1问站点2是否准备好提交，站点2报告说是。站点1再问站点3是否准备好提交，站点3也说准备好了。在这个时间点，站点1就是惟一知道事务结果的站点，它现在要负责把这个结果广播给其他站点。如果现在出现一个错误，比如说网络失败了，站点1掉电，或者其他某个原因，站点2 和站点3就会“挂起“它们就会有所谓的可疑分布式事务（in-doubt distributed transaction）。2PC协议力图尽可能地关闭这个错误窗口，但是无法完全将其关闭。站点2和站点3必须保持事务打开，等待站点1发出的结果通知。这个问题要由RECO进程来解决。有FORCE选项的COMMIT和ROLLBACK在这里就有了用武之地。如果问题的原因是站点1、2和3之间的网络故障，站点2和站点3的DBA可以打电话给站点1的DBA，问他结果是什么，并相应地手动应用提交或回滚。
对于分布式事务中能做的事情，还存在一些限制（不过并不多），这些限制是合理的。其中重要的限制如下：
1. 不能在数据库链接上发出COMMIT。也就是说，不能发出COMMIT@remote_site。只能从发起事务的那个站点提交。
2. 不能在数据库链接上完成DDL。这是上一个问题带来的直接结果。DDL会提交，而除了发起事务的站点外，你不能从任何其他站点提交，所以不能在数据库链接上完成DDL。
3. 不能在数据库链接上发出SAVEPOINT。简单地说，不能在数据库链接发出任务事务控制语句。所有事务控制都有最初打开数据库链接的会话继承得来；对于事务中的分布式实例，不能有不同的事务控制。
尽管数据库链接上缺乏事务控制，但是这是合理的，因为只有发起事务的站点才有参与事务的所有站点的一个列表。在我们的3站点配置中，如果站点2试图提交，它无从知道站点3也参与了这个事务。在Oracle中，只有站点1可以发出提交命令。此时，允许站点1把分布式事务控制的责任委托给另一个站点。
我们可以设置站点的COMMIT_POINT_STRENGTH（这是一个参数），从而改变具体的提交站点。提交点能力（commit-point strength） 会为分布式事务中的服务器关联一个相对的重要性级别。服务器越重要（要求这个服务器上的数据有更大的可用性），它就越有可能协调这个分布式事务。如果需要 在生产主机和测试主机之间完成一个分布式事务，你可能就希望这样做。由于事务协调器对于事务的结果绝对不会有疑义，最好是由生产主机协调分布式事务。
不能在数据库链接上执行DDL，实际上这并不太糟糕。首先，DDL很“少见“。只会在安装或升级期间执行一次DDL。生产系统不应该执行DDL。其次，要在数据库链接上执行DDL也是有办法的，可以使用作业队列工具DBMS_JOB，或者在Oracle 10g中可以使用调度工具包DBMS_SCHEDULER。你不用试图在链接上执行DDL，而是可以使用链接来调度一个远程作业，一旦提交就执行这个远程作业。采用这种方式，作业在远程主机上运行，这不是一个分布式事务，可以执行DDL。实际上，Oracle Replication Services（远程服务）就采用这种方法执行分布式DDL来实现模式复制。
6 自治事务
自治事务（autonomous transaction）允许你创建一个“事务中的事务”，它能独立于其父事务提交或回滚。利用自治事务，可以挂起当前执行的事务，开始一个新事务，完成一些工作，然后提交或回滚，所有这些都不影响当前执行事务的状态。自治事务提供了一种用PL/SQL控制事务的新方法，可以用于：
1. 顶层匿名块；
2. 本地（过程中的过程）、独立或打包的函数和过程；
3. 对象类型的方法；
4. 数据库触发器。
6.1 自治事务如何工作
创建一个简单的表来保存消息：scott@ORCL>create table t ( msg varchar2(25) );
表已创建。接下来创建两个过程，每个过程只是将其名字插入到消息表中，然后提交。不过，其中一个过程是正常的过程，另一个编写为自治事务。我们将使用这些对象来显示在各种情况下哪些工作会在数据库中持久保留（被提交）。
首先是AUTONOMOUS_INSERT过程：
scott@ORCL>create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /
过程已创建。以下是“正常”的NONAUTONOMOUS_INSERT过程：
scott@ORCL>create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /
过程已创建。下面来观察PL/SQL代码匿名块中非自治（nonautonomous）事务的行为：
scott@ORCL>begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL 过程已成功完成。
scott@ORCL>select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert可以看到，匿名块执行的工作（INSERT）由NONAUTONOMOUNS_INSERT过程提交。两个数据行都已提交，所以ROLLBACK命令没有什么可以回滚。把这个过程与自治事务过程的行为加以比较：
scott@ORCL>delete from t;
已删除2行。
scott@ORCL>commit;
提交完成。
scott@ORCL>begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL 过程已成功完成。
scott@ORCL>select * from t;
MSG
-------------------------
Autonomous Insert如果在一个“正常”的过程中COMMIT，它不仅会持久保留自己的工作，也会使该会话中未完成的工作成为永久性的。不过，如果在一个自治事务过程中完成COMMIT，只会让这个过程本身的工作成为永久性的。
6.2 何时使用自治事务？
Oracle数据库在内部支持自治事务时间已经不短了。我们看到的一直都是以递归SQL形式出现的自治事务。例如，从一个序列选择时就可以完成一个递归事务，从而在SYS.SEQ$表中立即递增序列。为支持你的序列，SYS.SEQ$表的更新会立即提交，并对其他事务可见，但是此时你的事务（即父事务）尚未提交。另外，如果回滚你的事务，序列的递增仍会保留；它没有随父事务回滚，因为这部分工作已经提交。空间管理、审计以及其他内部操作都是以类似的递归方式完成的。自治事务的一种可能合法的使用是用于定制审计。数据库中要对信息完成审计，与编写定制的触发器相比，还有许多更高效的方法。例如，可以使用DBMS_FGA包或者只是使用AUDIT命令本身。
下面是一个小例子。先在表上放一个自治事务触发器，它能捕获一个审计跟踪记录，详细地指出谁试图更新表，这个人什么时候想更新表，另外还会提供一个描述性消 息指出这个人想要修改什么数据。这个触发器的基本逻辑是：对于不向你直接或间接报告的员工，要防止更新这些员工记录的任何企图。
首先，从SCOTT模式建立EMP表的一个副本，以此作为本例使用的表：
system@ORCL>create table emp
2 as
3 select * from scott.emp;
表已创建。
system@ORCL>grant all on emp to scott;
授权成功。还要创建一个AUDIT_TAB表，在这个表中存储审计信息。我们使用了列的DEFAULT属性，从而默认具有当前登录的用户名以及登记审计跟踪信息的当前日期/时间：
system@ORCL>create table audit_tab
2 (username varchar2(30) default user,
3 timestamp date default sysdate,
4 msg varchar2(4000)
5 )
6 /
表已创建。接下来，创建一个EMP_AUDIT触发器对EMP表上的UPDATE活动进行审计：
system@ORCL>create or replace trigger EMP_AUDIT
2 before update on emp
3 for each row
4 declare
5 pragma autonomous_transaction;
6 l_cnt number;
7 begin
8
9 select count(*) into l_cnt
10 from dual
11 where EXISTS ( select null
12 from emp
13 where empno = :new.empno
14 start with mgr = ( select empno
15
from emp
16
where ename = USER )
17 connect by prior empno = mgr );
18 if ( l_cnt = 0 )
19 then
20 insert into audit_tab ( msg )
21 values ( 'Attempt to update ' || :new.empno );
22 commit;
23
24 raise_application_error( -20001, 'Access Denied' );
25 end if;
26 end;
27 /
触发器已创建注意，这里使用了CONNECT BY查询。这会根据当前用户分析整个（员工）层次结构。它会验证我们试图更新的记录是某个下属员工的记录，即这个人会在某个层次上向我们报告。
关于这个触发器的要点，主要如下：
1. PRAGMA AUTONOMOUS_TRANSACTION应用于触发器定义。整个触发器是一个“自治事务”，因此它独立于父事务（即企图完成更新的事务）。
2. 触发器在查询中从它保护的表（EMP表） 中具体读取。如果这不是一个自治事务，它本身在运行时就会导致一个变异表错误。自治事务使我们绕开了这个问题，它允许我们读取表，但是也带来了一个缺点， 我们无法看到自己对表做的修改。在这种情况下需要特别小心，这个逻辑必须仔细审查。如果我们完成的事务是对员工层次结构本身的一个更新会怎么样？我们不会在触发器中看到这些修改，在评估触发器的正确性时也要把这考虑在内。
3. 触发器提交。这在以前不可能的，触发器以前从来不能提交工作。这个触发器并不是提交父事务的工作（实际触发器触发的工作，即更新员工记录），而只是提交了触发器所完成的工作（审计记录）。
在此，我们建立了EMP表，其中一个妥善的层次结构（EMPNO-MGR递归关系）。另外还有一个AUDIT_TAB表，要在其中记录修改信息的失败企图。我们的触发器可以保证这样一个规则：只有我们的经理或经理的经理（依此类推）可以修改我们的记录。
下面尝试在EMP表中更新一条记录，来看看这是如何工作的：
system@ORCL>update emp set sal = sal*2 ;
update emp set sal = sal*2
*
第 1 行出现错误:
ORA-20001: Access Denied
ORA-06512: 在 "SYSTEM.EMP_AUDIT", line 21
ORA-04088: 触发器 'SYSTEM.EMP_AUDIT' 执行过程中出错
system@ORCL>select * from audit_tab;
USERNAME TIMESTAMP MSG
--------- -------------- ----------
SYSTEM 14-5月 -18 Attempt to update 7369触发器发现了情况，能防止UPDATE发生，而与此同时，会为这个企图创建一个永久记录（注意它在AUDIT_TAB表的CREATE TABLE语句上如何使用DEFAULT关键字来自动插入USER和SYSDATE值）。接下来，假设我们作为一个用户登录，想实际完成一个UPDATE，并做一些尝试：
system@ORCL>connect scott/123456
已连接。
scott@ORCL>set echo on
scott@ORCL>update system.emp set sal = sal*1.05 where ename = 'ADAMS';
已更新 1 行。
scott@ORCL>update system.emp set sal = sal*1.05 where ename = 'SCOTT';
update system.emp set sal = sal*1.05 where ename = 'SCOTT'
*
第 1 行出现错误:
ORA-20001: Access Denied
ORA-06512: 在 "SYSTEM.EMP_AUDIT", line 21
ORA-04088: 触发器 'SYSTEM.EMP_AUDIT' 执行过程中出错在演示表EMP的默认安装中，员工ADAMS是SCOTT的下属，所以第一个UPDATE成功。再看第二个UPDATE，SCOTT试图给自己加薪，但是由于SCOTT不向SCOTT报告（SCOTT不是自己的下属），所以这个更新失败了。再登录回到包括AUDIT_TAB表的模式，可以看到以下结果：
scott@ORCL>connect system/123456
已连接。
system@ORCL>set echo on
system@ORCL>select * from audit_tab;
USERNAME TIMESTAMP MSG
------------------------------ -------------- ----------------------------------------
SYSTEM 14-5月 -18 Attempt to update 7369
SCOTT 14-5月 -18 Attempt to update 7788
扫一扫
2060
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
