oracle提交所有事务,oracle事务提交

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITsz

50 dbasz

11:05:01 scott@PRIMARY>

11:04:03 sys@PRIMARY>

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

DEPTNO DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITsz

50 dbasz

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITsz

50 dbasz

11:07:24 sys@PRIMARY>

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

11:07:05 scott@PRIMARY> commit;

提交完成。

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

DEPTNO DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITchina

50 dbasz

11:09:04 scott@PRIMARY>

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

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

DEPTNO DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITchina

50 dbasz

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

80 ITchina

50 dbasz

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

70 dbabeijing

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

70 dbabeijing

90 oracleusa

已选择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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

70 dbabeijing

90 oracleusa

已选择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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

90 oracleusa

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

90 oracleusa

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 DNAMELOC

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

10 ACCOUNTINGsz

20 RESEARCHDALLAS

30 SALESCHICAGO

50 dbasz

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