12c move数据文件不会自动提交使用该数据文件的事务

环境:
OS:Centos 7
DB:12.2.0.1

 

1.创建pdb
create pluggable database pdb03 admin user hxl identified by oracle roles=(dba)
CREATE_FILE_DEST='/u01/app/oracle/oradata/slnngk/pdb03';

 

2.创建表空间

alter session set container=pdb03;
create tablespace tps_pdb
logging datafile '/u01/app/oracle/oradata/slnngk/pdb03/SLNNGK/F48F4A46B1B807A0E055CEFFD4D56E1F/datafile/tps_pdb01.dbf' size 50m
autoextend on
next 10m maxsize 2048m
extent management local
segment space management auto;

 

3.创建用户并默认表空间设置为上面步骤创建的表空间tps_pdb
alter session set container=pdb03;
create user hxl01 identified by oracle default tablespace tps_pdb;
grant dba to hxl01;

 

4.使用新建的账号连接数据库

tnspdb03 =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb03)
    )
  )

 

连接pdb03
SQL> connect hxl01/oracle@tnspdb03
建表
SQL> create table tb_test(id number,name varchar2(32));

Table created.

查看表对应的表空间

SQL> column table_name format a32;
SQL> column tablespace_name format a16;
SQL> select table_name,tablespace_name from dba_tables where table_name='TB_TEST';

TABLE_NAME                       TABLESPACE_NAME
-------------------------------- ----------------
TB_TEST                          TPS_PDB

 

写入数据但是不提交
insert into tb_test values(1,'name1');
insert into tb_test values(2,'name2');
insert into tb_test values(3,'name3');
insert into tb_test values(4,'name4');
insert into tb_test values(5,'name5');

 

5.在线迁移数据文件

SQL> show con_name;

CON_NAME
------------------------------
PDB03

set linesize 1000;
column file# format 99;
column name format a200;
SQL> select file#,name from v$datafile;

FILE# NAME
----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   21 /u01/app/oracle/oradata/slnngk/pdb03/SLNNGK/F48F4A46B1B807A0E055CEFFD4D56E1F/datafile/o1_mf_system_kymmc4wh_.dbf
   22 /u01/app/oracle/oradata/slnngk/pdb03/SLNNGK/F48F4A46B1B807A0E055CEFFD4D56E1F/datafile/o1_mf_sysaux_kymmc4x0_.dbf
   23 /u01/app/oracle/oradata/slnngk/pdb03/SLNNGK/F48F4A46B1B807A0E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kymmc4x1_.dbf
   24 /u01/app/oracle/oradata/slnngk/pdb03/SLNNGK/F48F4A46B1B807A0E055CEFFD4D56E1F/datafile/tps_pdb01.dbf

 

将含有未提交事务的数据文件迁移到指定的目录

SQL> alter database move datafile 24 to '/u01/app/oracle/oradata/slnngk/pdb03/tps_pdb01.dbf';

发现数据文件迁移过去了,下面查看之前未提交的事务是否自动提交了,开启一个新的会话查询tb_test表数据

SQL> connect hxl01/oracle@tnspdb03
Connected.
SQL> select * from tb_test;

no rows selected

发现move数据文件不会提交未完成的事务.

 

--The End --