eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle 12.2 新特性:在线PDB数据库克隆(Pluggable Hot Clone) | Blog首页 | Oracle 数据库 12.2新特性手册 - 可用性卷(Availability) »

Oracle 12c 多租户:未完成事务的隐式提交(commit)

在前面的测试过程中,偶然发现12.1版本的一个表现,确切的说,应该是在12.1.0.1版本中。当关闭数据库前,当前会话中,未被提交的事务将会隐式被提交。

[oracle@12c01db trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 1 16:48:42 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 2 PDB$SEED			  READ ONLY
	 3 YHEM 			  MOUNTED

SQL> alter session set container=YHEM;

Session altered.

SQL> startup
Pluggable Database opened.

SQL> alter session set current_schema=EYGLE;

Session altered.

SQL> create table enmotech as select con_id,name,open_mode from v$pdbs;

Table created.

SQL> select * from enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 3 YHEM 			  READ WRITE

SQL> update enmotech set con_id=10000+con_id;

1 row updated.

SQL> select * from enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
     10003 YHEM 			  READ WRITE

SQL> shutdown immediate;
Pluggable Database closed.

正常情况下,这个未完成的事务应当被回滚。

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database yhem open;

Pluggable database altered.

SQL> alter session set container=YHEM;

Session altered.

SQL> select * from eygle.enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
     10003 YHEM 			  READ WRITE

在12.1.0.2中已经改变。注意在以下的测试中,直接关闭PDB,Oracle会提示事务在进行中,需要提交或者回滚。此时abort命令拥有了进一步的实质性含义,不再提示,直接停止了PDB。当然最后事务被成功回退。

[oracle@enmocoredb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 1 17:06:53 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col name for a30
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ --------------------
	 2 PDB$SEED			  READ ONLY
	 3 PDBPROD1			  READ WRITE
	 4 PDBPROD2			  MOUNTED

SQL> alter session set container=PDBPROD1;

Session altered.

SQL> create table enmotech as select con_id,name from v$pdbs;

Table created.

SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
	 3 PDBPROD1

SQL> update enmotech set con_id=10000+con_id;

1 row updated.

SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
     10003 PDBPROD1

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first


SQL> shutdown abort;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
	 3 PDBPROD1

一个新特性的引入,往往是牵一发动全身的改变,完善就需要从头再来。


历史上的今天...
    >> 2010-12-09文章:
           《Oracle DBA手记 2》勘误表
    >> 2008-12-09文章:
    >> 2007-12-09文章:
    >> 2006-12-09文章:
           恭王府赏福字碑
    >> 2005-12-09文章:
           2005-12-09 EMC DISK Fault

无觅

By eygle on 2016-12-09 09:01 | Comments (0) | Oracle12c/11g | 3217 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com