« 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

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

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

12c