oracle事务提交

最新推荐文章于 2024-07-23 23:58:27 发布

真的不知道取什么名

最新推荐文章于 2024-07-23 23:58:27 发布

阅读量1.1k

点赞数

分类专栏： oracle 管理文章标签： oracle database delete insert table structure

本文链接：https://blog.csdn.net/zhuomingwang/article/details/6560669

版权

oracle 管理专栏收录该内容

5 篇文章 0 订阅

订阅专栏

oracle dml语句何时的commit;

本文演示什么操作会导致oracle的事务提交

演示说明：开启了两个session,一个用来做操作实验，用scott用户登录，dept为实验表

还有一个用sys用户登录（蓝色字体），查询scott下dept的变化情况

为了演示需要我们先修改[ORACLE_HOME]/sqlplus/admin/glogin.sql文件添加如下内容：

set termout off

define gname=idle

column global_name new_value gname

select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name),dot-1) ) global_name

from (select global_name, instr(global_name,'.') dot from global_name );

set sqlprompt '&gname> '

set termout on

set time on

从两个session分别查看dept表的原始数据

11:03:32 scott@PRIMARY> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT sz

50 dba sz

11:05:01 scott@PRIMARY>

11:04:03 sys@PRIMARY>

11:03:04 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT sz

50 dba sz

11:04:03 sys@PRIMARY>

1、主动的commit;

11:06:19 scott@PRIMARY> update dept set loc='china' where deptno=80;

已更新 1 行。

11:07:05 scott@PRIMARY>

--scott表被修改但是未提交所有从sys用户看deptno=80的loc仍然是sz而不是china

11:04:03 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT sz

50 dba sz

11:07:24 sys@PRIMARY>

--scott手动执行commit命令再分别查看结果

11:07:05 scott@PRIMARY> commit;

提交完成。

11:09:01 scott@PRIMARY> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT china

50 dba sz

11:09:04 scott@PRIMARY>

--deptno=80的loc被修改为china了

11:07:24 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT china

50 dba sz

11:09:08 sys@PRIMARY>

2、执行dcl语句会隐式提交

11:09:04 scott@PRIMARY> delete from dept where deptno=80;

已删除 1 行。

11:11:34 scott@PRIMARY>

--虽然在scott执行了delete命令，但是sys下看dept表没有发生变化

11:09:08 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

80 IT china

50 dba sz

11:11:41 sys@PRIMARY>

--scott尝试执行一个grant命令

11:11:34 scott@PRIMARY> grant connect to aspen;

grant connect to aspen

第 1 行出现错误:

ORA-01917: 用户或角色 'ASPEN' 不存在

11:12:29 scott@PRIMARY>

--在scott用户下执行了grant命令，但是没有成功，可是从sys用户下查看到dept表中deptno=80的数据还是被删除了

--所以在dml语句后紧接中执行dcl语句不管dcl语句有没有执行成功都会隐式提交

11:11:41 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

11:12:44 sys@PRIMARY>

3、执行ddl语句

11:12:29 scott@PRIMARY> insert into dept values (70,'dba','beijing');

已创建 1 行。

11:15:55 scott@PRIMARY>

11:12:44 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

11:15:58 sys@PRIMARY>

--scott用户在修改dept表，紧接着执行了一天ddl语句

11:15:55 scott@PRIMARY> create table aspen as select * from dept where 1=2;

表已创建。

11:16:42 scott@PRIMARY>

--在scott用户下执行了ddl语句，从sys用户下可以看到我们新添加的数据

11:15:58 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

70 dba beijing

11:16:46 sys@PRIMARY>

--同样如果是ddl语句没有执行成功也是相同的效果

11:16:42 scott@PRIMARY> insert into dept values (90,'oracle','usa');

已创建 1 行。

11:20:11 scott@PRIMARY> create table aspen as select * from dept where 1=2;

create table aspen as select * from dept where 1=2

第 1 行出现错误:

ORA-00955: 名称已由现有对象使用

11:20:48 scott@PRIMARY>

11:20:15 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

70 dba beijing

90 oracle usa

已选择6行。

11:20:52 sys@PRIMARY>

4、切换用户（当前用户先正常退出，再登录另一个用户）用户正常退出就会隐式提交事务。而用户异常退出则会自动rollback

11:20:48 scott@PRIMARY> delete from dept where deptno=70;

已删除 1 行。

11:21:52 scott@PRIMARY>

11:20:52 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

70 dba beijing

90 oracle usa

已选择6行。

11:22:11 sys@PRIMARY>

--scott删除一条数据后切换为sys用户，导致前面的事务隐式提交

11:21:52 scott@PRIMARY> conn / as sysdba

已连接。

11:22:24 sys@PRIMARY>

11:22:11 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

90 oracle usa

11:22:27 sys@PRIMARY>

--切换用户不成功也是同样的结果（当前用户正常退出）

11:23:37 scott@PRIMARY> delete from dept where deptno=90;

已删除 1 行。

11:23:40 scott@PRIMARY>

11:22:27 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

90 oracle usa

11:23:46 sys@PRIMARY>

11:23:40 scott@PRIMARY> conn sys/oracle

ERROR:

ORA-28009: 应当以 SYSDBA 身份或 SYSOPER 身份建立 SYS 连接

警告: 您不再连接到 ORACLE。

11:23:53 scott@PRIMARY>

11:23:46 sys@PRIMARY> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING sz

20 RESEARCH DALLAS

30 SALES CHICAGO

50 dba sz

11:24:04 sys@PRIMARY>

补充资料：

DDL

　　Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

　　CREATE - to create objects in the database

　　ALTER - alters the structure of the database

　　DROP - delete objects from the database

　　TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

　　COMMENT - add comments to the data dictionary

　　RENAME - rename an object

　　DML

　　Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

　　SELECT - retrieve data from the a database

　　INSERT - insert data into a table

　　UPDATE - updates existing data within a table

　　DELETE - deletes all records from a table, the space for the records remain

　　MERGE - UPSERT operation (insert or update)

　　CALL - call a PL/SQL or Java subprogram

　　EXPLAIN PLAN - explain access path to data

　　LOCK TABLE - control concurrency

　　DCL

　　Data Control Language (DCL) statements. Some examples:

　　GRANT - gives user's access privileges to database

　　REVOKE - withdraw access privileges given with the GRANT command

　　TCL

　　Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

　　COMMIT - save work done

　　SAVEPOINT - identify a point in a transaction to which you can later roll back

　　ROLLBACK - restore database to original since the last COMMIT

　　SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

真的不知道取什么名

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle事务提交

oracle dml语句何时的commit; 本文演示什么操作会导致oracle的事务提交演示说明：开启了两个session,一个用来做操作实验，用scott用户登录，dept为实验表还有一个用sys用户登录（蓝色字体），查询scott下dept的变化情况为了演示需要我们先修改[ORACLE_HOME]/sqlplus/admin/glogin.sql文件添加如下内
复制链接

扫一扫

专栏目录