Oracle 12C 在线重命名和迁移数据文件

1.熟悉環境

[oracle@DB01 ~]$ cd /u02/oradata/MPCDB/MPMON/

[oracle@DB01 MPMON]$ pwd
/u02/oradata/MPCDB/MPMON

[oracle@DB01 MPMON]$ ll
total 9931700
-rw-r----- 1 oracle oinstall  268443648 Sep 25 12:40 I_MPMON_UD_1.dbf
-rw-r----- 1 oracle oinstall  268443648 Sep 25 12:31 I_MPMON_UD_2.dbf
-rw-r----- 1 oracle oinstall  268443648 May 26  2019 MPMON_TD1_1.dbf
-rw-r----- 1 oracle oinstall  268443648 Sep 25 07:01 MPMON_TD1_2.dbf
-rw-r----- 1 oracle oinstall 1207967744 Sep 25 12:42 MPMON_UD_1.dbf
-rw-r----- 1 oracle oinstall  939532288 Sep 25 12:41 MPMON_UD_2.dbf
-rw-r----- 1 oracle oinstall  754982912 Sep 25 12:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall  283123712 Sep 25 12:43 system01.dbf
-rw-r----- 1 oracle oinstall 6442459136 Sep 25 12:43 undotbs01.dbf

[oracle@DB01 MPMON]$ sql sys/MPCDBMPCDB@10.0.2.6:1521/MPMON as sysdba

SQL> SET SQLFORMAT ansiconsole

SQL> show pdbs;
CON_ID  CON_NAME  OPEN MODE   RESTRICTED  
6       MPMON     READ WRITE  NO          

SQL> show con_name
CON_NAME 
------------------------------
MPMON

SQL> select t.tablespace_name,t.file_name from dba_data_files t;
TABLESPACE_NAME  FILE_NAME                                  
SYSTEM           /u02/oradata/MPCDB/MPMON/system01.dbf      
SYSAUX           /u02/oradata/MPCDB/MPMON/sysaux01.dbf      
UNDOTBS1         /u02/oradata/MPCDB/MPMON/undotbs01.dbf     
MPMON_UD         /u02/oradata/MPCDB/MPMON/MPMON_UD_1.dbf    
MPMON_UD         /u02/oradata/MPCDB/MPMON/MPMON_UD_2.dbf    
I_MPMON_UD       /u02/oradata/MPCDB/MPMON/I_MPMON_UD_1.dbf  
I_MPMON_UD       /u02/oradata/MPCDB/MPMON/I_MPMON_UD_2.dbf

2.創建測試用途表空間及表

create tablespace TAB_UD datafile 
'/u02/oradata/MPCDB/MPMON/TAB_1.dbf' size 8M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED, 
'/u02/oradata/MPCDB/MPMON/TAB_2.dbf' size 8M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
 extent management local segment space management auto;

create table t1
(
  aaa number
) tablespace TAB_UD;

insert into t1 values(1);
commit;

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD'; 
TABLESPACE_NAME  FILE_NAME                           
TAB_UD           /u02/oradata/MPCDB/MPMON/TAB_1.dbf  
TAB_UD           /u02/oradata/MPCDB/MPMON/TAB_2.dbf

3.重命名数据文件 & 迁移数据文件 (同命名)

 alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_1.dbf' to '/u02/oradata/MPCDB/MPMON/TAB_01.dbf';
 alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_2.dbf' to '/u02/oradata/MPCDB/MPMON/TAB_02.dbf';

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD'; 
TABLESPACE_NAME  FILE_NAME                            
TAB_UD           /u02/oradata/MPCDB/MPMON/TAB_02.dbf  
TAB_UD           /u02/oradata/MPCDB/MPMON/TAB_01.dbf  

[oracle@DB01 MPMON]$ ll TAB*
-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:56 TAB_01.dbf
-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:57 TAB_02.dbf

4.迁移后保留原数据文件，源数据文件如果不需要可以在操作系统层面直接删除

 alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_01.dbf' to '/u02/oradata/MPCDB/MPMON2/TAB_01.dbf' keep;

[oracle@DB01 MPMON2]$ pwd
/u02/oradata/MPCDB/MPMON2

[oracle@DB01 MPMON2]$ ll
total 8200
-rw-r----- 1 oracle oinstall 8396800 Sep 25 13:03 TAB_01.dbf

[oracle@DB01 MPMON]$ ll TAB*
-rw-r----- 1 oracle oinstall 8396800 Sep 25 13:03 TAB_01.dbf
-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:57 TAB_02.dbf

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD';
TABLESPACE_NAME  FILE_NAME                             
TAB_UD           /u02/oradata/MPCDB/MPMON/TAB_02.dbf   
TAB_UD           /u02/oradata/MPCDB/MPMON2/TAB_01.dbf

最后编辑于：2021.01.15 23:22:26