报错信息如下: Msg 5064, Level 16, State 1, Line 1Changes to the state or options of database 'test' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE
sql proc触发异常处理回滚 针对proc嵌套proc很有用 begin begin try begin tran --判断错误 BEGIN --ROLLBACK TRAN SET @vcResult = '添加失败' ,) END commit tran end try begin catch ROLLBACK TRAN ; --@@tranCount = 1 代表开启事物的个数,如果嵌套调用开启了两个事物,则在外部事物回滚 end catch end
在事务语句最前面加上 set xact_abort on GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[test] @a int, @b int, @c int, @d int, @e int, @f int AS set xact_abort on begin tran insert into testtb values(@a,@b,@c,@d) insert into test4 values(@e,@f) commit tra
USE [TestDB] GO /****** 对象: Table [dbo].[Person] 脚本日期: 11/23/2008 13:37:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( ) NOT NULL, ) NOT NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [PersonId] ASC )WI
As we already saw, the reasons why we have blocking issues and deadlocks in the system are pretty much the same. They occur because of non-optimized queries. So, not surprisingly, troubleshooting techniques are very similar. Let’s have a quick look.
闪回归档 1. 什么是闪回数据归档? 闪回归档是用来保存一个或多个表的历史数据的新数据库对象,以及该数据的存储保留和清除策略.归档只是保存数据库中一个或多个表的所有事务处理的变化的一个或多个表空间,数据库将缓冲区高速缓存中的所有原始数据作为撤销数据写入撤销表空间,一个称为Flashback Data Archiver Process(FBDA)的新后台进程把原始数据收集并写入一个闪回数据归档中,因而创建了一个所有表数据的历史,为了启用闪回归档必须创建一个带有flashback data arch
使用闪回技术查询数据 闪回查询:就是查询表在过去某个时间点的数据,所用到的技术就是undo数据 SQL> conn scott/tiger 创建测试表 SQL> create table test as select * from dept; SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 插入数据 SQL> select sysdate from dual; SQL> insert into t