- 浏览: 224293 次
- 性别:
- 来自: 广东
文章分类
最新评论
-
wangmuchang:
解压需要密码
CAS单点登录之测试应用 -
ayang722:
首先就要在运行报表birt的IEngineTask中加入, J ...
birt配置动态数据源 -
lihong11:
very good!
js常用方法 -
qtlkw:
你共享出来为什么要密码?要密码为何要共享出来?汗
CAS单点登录之测试应用 -
lishouxinghome:
请问如何获得用户的Id呢,往指点
使用 CAS 在 Tomcat 中实现单点登录
transaction has no link to the calling transaction, so only commited data can be shared by both transactions.
自治事务允许你离开调用的事务上下文,执行一个独立的事务,然后返回调用的事务而不会影响到调用事务的状态。自治事务和调用事务不同,只有提交的事务才会在事务见共享。
The following types of PL/SQL blocks can be defined as autonomous transactions:
以下的PL/SQL代码块可以定义为自治事务。
* Stored procedures and functions. 存储过程和函数
* Local procedures and functions defined in a PL/SQL declaration block. 定义在声明块里的本地存储过程和函数
* Packaged procedures and functions. 打包的存储过程和函数
* Type methods. 类型方法
* Top-level anonymous blocks. 顶层的匿名块
The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.
最简单的理解自治事务的方法是查看他们的行为。我们创建一个测试表格,然后放入2行数据,注意数据没有提交。
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
下一步,我们使用匿名的自治事务块插入另外8行数据,同时提交。
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
和预想的一样,我们得到了10行数据。如果我们执行一个回滚(rollback)语句,我们得到了如下的结果
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.
被我们当前事务插入的2行数据被回滚了,而被自治事务插入的数据继续存在。编译描述符 PRAGMA AUTONOMOUS_TRANSACTION 使得自治块在自己的事务里运行,所以内部的提交语句不会影响调用方的事务。
Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.
自治事务一般用于日常的错误日志,错误信息必须保留,无论事务是提交还是回滚。例如下面的表保存了基本的错误信息。
CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
log_timestamp TIMESTAMP NOT NULL,
error_message VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);
CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
我们定义了一个自治事务的存储过程来记录错误信息
CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
下面的代码强制产生一个错误,被捕获且记录到日志
BEGIN
INSERT INTO at_test (id, description)
VALUES (998, 'Description for 998');
-- Force invalid insert.
INSERT INTO at_test (id, description)
VALUES (999, NULL);
EXCEPTION
WHEN OTHERS THEN
log_errors (p_error_message => SQLERRM);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test WHERE id >= 998;
no rows selected
SELECT * FROM error_logs;
ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")
1 row selected.
SQL>
From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren't, we would expect the first insert in the anonymous block to be preserved by the commit statement in the LOG_ERRORS procedure.
由此可知,LOG_ERRORS事务被分割为自治块。如果不是,我们可以期待在自治块插入的第一个数据被LOG_ERRORS存储过程的提交语句后保留。
Be careful how you use autonomous transactions. If they are used indiscriminately they can lead to deadlocks, and cause confusion when analyzing session trace. To hammer this point home, here's a quote from Tom Kyte posted on my blog (here):
小心你使用自治存储过程的方式。如果你胡乱使用,可能会引起死锁,同时在分析跟踪事务时引起冲突。下面是Tom Kyte在我的博客里提供的一些建议:
... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.
Where do people try to use them?
* in that trigger that calls a procedure that commits (not an error logging routine). Ouch, that has to hurt when you rollback.
* in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*
Error logging - OK.
Almost everything else - not OK.
文章出处:飞诺网(www.diybl.com):http://www.diybl.com/course/7_databases/oracle/oraclejs/200919/154466.html
发表评论
-
Oracle 连接故障的排查
2014-04-10 16:33 678Oracle 连接故障的排查 1、 故障现象 通 ... -
系统共享内存的修改(ORA-27102: out of memory)
2014-04-10 16:24 729今天做压力测试的时候,修改数据参数出现ORA-27102: ... -
Oracle的AWR报告分析
2014-04-10 16:23 624今晚来分析一下awr报告,首先说一下什么是awr报告,它能给 ... -
oracle 10g Enterprise Manager 无法连接到数据库实例分析
2014-03-27 17:40 9231 问题描述客户端通过IE 浏览器登陆oracle 10g ... -
linux Oracle服务启动&停止脚本与开机自启动
2014-03-27 17:39 952在CentOS 6.3下安装完Oracl ... -
plsql设置
2012-12-25 17:54 8021.设置date类型显示格式 TOOLS(工具)——> ... -
Oracle date timestamp 时间函数总结
2012-12-21 11:30 1055yyyy-mm-dd hh24:mi:ss.ff 年-月-日 ... -
oracle异常收集
2012-12-21 11:14 7761. ORA-12516: TNS: 监听程序找不到符合协议 ... -
oracle基础sql语句二
2012-12-21 10:03 642linux 切换oracel用户: su oracle ... -
修改oracle数据库的字符集
2012-12-21 09:58 973SHUTDOWN IMMEDIATE;STARTUP MOUN ... -
Oracle对象类型详解
2012-03-26 14:38 1198一、抽象数据类型1、创建类型--地址类型CREATE O ... -
oracle数组类型简单实例介绍
2012-03-26 10:49 1012Oracle数组一般可以分为固定数组和可变数组 固定数组 ... -
oracle问题收集一
2011-08-30 15:37 11491.dblink创建语法 --删除dblink dr ... -
oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
2011-08-05 15:03 921今天发现在oracle中的sele ... -
oracle树中prior的用法
2011-08-05 15:02 1727在oracle生成树时,prior ... -
事务中的Savepoints
2011-07-29 09:59 735事务中的Savepoints 你可以在事务上下文中声明称为sa ... -
Oracle参数游标
2011-07-29 09:55 840一、参数游标 参数 ... -
PLSQL(语法--异常处理--游标--存储过程--触发器--oracle分页)
2011-07-08 17:00 1246《PL/SQL编程》 ... -
用pl/sql developer导出表结构和数据
2011-06-24 11:45 1258点击Tools--Export User Objects 这 ... -
Oracle学习笔记一:体系结构
2011-06-24 11:40 705http://blog.csdn.net/chenmo_zha ...
相关推荐
Oracle自治事务的介绍(Autonomous_Transactions)PRAGMA_AUTONOMOUS_TRANSACTION
在触发器中使用自制事务及调用存储过程 Declare Pragma Autonomous_Transaction; ...
要创建一个自治事务,您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中,使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION语句。在这样的模块或过程中执行的SQL Server语句都是自治的。
利用pragma data_seg实现程序只能单个实例运行测试工程。已在VC 6.0环境下成功运行
快速学习函数创建和练习。 创建函数、存储过程、创建过程、调用存储过程、AUTHID、PRAGMA AUTONOMOUS_TRANSACTION、开发存储过程步骤、删除过程和函数、过程与函数的比较
PRAGMA table_info('keys');--获取表的列信息 PRAGMA database_list; 对每个打开的数据库,使用该数据库的信息调用一次回叫函数。使用包括附加的数据库名和索引名在内的参数。第一行用于主数据库,第二行用于存放...
#pragma用法 汇总 doc 最近总有人问#pragma CODE SEG NEAR SEG NON BANKED 还有#pragma LINK INFO DERIVATIVE "mc9s12xs128"这些函数是什么意思 我在网上收集了一些资料希望能解大家疑惑 #pragma LINK ...
#pragma_命令集合 介绍pragma_命令的用法,使你不再迷茫
代码如下:create or replace Function GetInvitationNO...– 需要使用“Current_User”的权限防止无法运行“Execute Immediate”命令 & “PRAGMA AUTONOMOUS_TRANSACTION”自制事务防止DML无法DDL的问题 Totalprev V
pragma_comment用法,jian dan de jie shao.pragma_comment用法
#pragma_命令集合.pdf 不错的东西 很重要
create or replace procedure delBigTab(p_TableName in varchar2,p_Condition in varchar2,p_Count in varchar2) as pragma autonomous_transaction; n_delete number:=0; begin while 1=1 loop ...
IAR软件的操作使用说明,非常详细,哈哈哈哈哈
#pragma使用详解 .pdf #pragma使用详解 .pdf #pragma使用详解 .pdf
1 #pragma CODE_SECTION(InitFlash, "ramfuncs")? 众所周知,微处理器从内存RAM中取指令的速度要比从flash中取指令要快好多倍,但是RAM的缺陷限制了其不能存储用户程序代码,因为RAM掉电会丢失数据,但是其速度要快...
很详细的讲解 #Pragma_Pack的用法,作用,及效果,一目了然。
只不过对于加密数据库,需要在对数据库进行任何操作前,先执行“PRAGMA key=xxx;”。通过这种方式输入数据库密码。检验之前的密码是否正确可以在命令行中键入“.databases”,如果没有错误提示,那就是解密成功了;...
PRAGMA pragma_name; 要为 PRAGMA 设置一个新的值,语法如下: PRAGMA pragma_name = value; 设置模式,可以是名称或等值的整数,但返回的值将始终是一个整数。 auto_vacuum Pragma auto_vacuum Pragma 获取或...
#pragma data_seg("MySec") HWND g_hWnd=NULL; #pragma data_seg() #pragma comment(linker,"/section:MySec,RWS") /* SEGMENTS MySec READ WRITE SHARE */ #define WM_MSG WM_USER + 500 LRESULT CALLBACK ...
在所有的预处理指令中,#Pragma 指令可能是最复杂的了,它的作用是设定编译器的状态或者是指示编译器完成一些特定的动作。#pragma指令对每个编译器给出了一个方法,在保持与C和C++语言完全兼容的情况下,给出主机或...