移动Oracle数据文件(Windows操作记录)

-- 移动数据文件使用的有2种办法 
-- 根据该博文进行整理：http://www.2cto.com/database/201301/180909.html


-- 方法一、以数据文件为单位移动 
--1.查看数据文件  
--SQL>select name from v$datafile; 


--2.关闭数据库 
--SQL> shutdown immediate


--3.MOUNT到数据库 
--SQL> startup mount 


--4. 在OS里移动数据文件，以下是生成执行命令
SELECT 'move '||d.file_name||' (这是新路径)E:\icanmove3\'||
substr(d.file_name,instr(d.file_name,'\',-1)+1,LENGTH(d.file_name))
FROM Dba_Data_Files d; 


--5.调整database数据文件路径
SELECT 'alter database rename file '''||d.file_name||''' to ''(这是新路径)E:\icanmove3\'||
substr(d.file_name,instr(d.file_name,'\',-1)+1,LENGTH(d.file_name))||'''' 
FROM dba_tablespaces t,Dba_Data_Files d WHERE t.tablespace_name=d.tablespace_name; 


--启用DB
alter database open
=======================执行结果============================
move E:\ICANMOVE2\USERS.DBF E:\icanmove3\USERS.DBF
move D:\ORACLE\ORADATA\ICANMOVE\UNDOTBS01.DBF E:\icanmove3\UNDOTBS01.DBF
move E:\ICANMOVE2\SYSAUX.DBF E:\icanmove3\SYSAUX.DBF
move D:\ORACLE\ORADATA\ICANMOVE\SYSTEM01.DBF E:\icanmove3\SYSTEM01.DBF


alter database rename file 'E:\ICANMOVE2\USERS.DBF' to 'E:\icanmove3\USERS.DBF';
alter database rename file 'D:\ORACLE\ORADATA\ICANMOVE\UNDOTBS01.DBF' to 'E:\icanmove3\UNDOTBS01.DBF';
alter database rename file 'E:\ICANMOVE2\SYSAUX.DBF' to 'E:\icanmove3\SYSAUX.DBF';
alter database rename file 'D:\ORACLE\ORADATA\ICANMOVE\SYSTEM01.DBF' to 'E:\icanmove3\SYSTEM01.DBF';
=========================================================


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as my@icanmove
============================1============================
SQL> select name from v$datafile; 
 
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\ICANMOVE\SYSTEM01.DBF
E:\ICANMOVE2\SYSAUX.DBF
D:\ORACLE\ORADATA\ICANMOVE\UNDOTBS01.DBF
E:\ICANMOVE2\USERS.DBF
============================2============================
SQL>  shutdown immediate;
 
shutdown immediate
 
ORA-00900: invalid SQL statement
-- 打开cmd窗口（代号A窗口）中执行：
C:\Users\Administrator>sqlplus sys/sys@icanmove as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 19 17:12:13 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


============================3============================
-- 再开一新cmd窗口（代号B窗口）
C:\Users\Administrator>move E:\ICANMOVE2\USERS.DBF E:\icanmove3\USERS.DBF
移动了         1 个文件。
C:\Users\Administrator>move D:\ORACLE\ORADATA\ICANMOVE\UNDOTBS01.DBF E:\icanmove3\UNDOTBS01.DBF
移动了         1 个文件。
C:\Users\Administrator>move E:\ICANMOVE2\SYSAUX.DBF E:\icanmove3\SYSAUX.DBF
移动了         1 个文件。
C:\Users\Administrator>move D:\ORACLE\ORADATA\ICANMOVE\SYSTEM01.DBF E:\icanmove3\SYSTEM01.DBF
移动了         1 个文件。
  
============================4============================  
-- 在A窗口中执行：
SQL> startup mount
ORACLE instance started.


Total System Global Area  521936896 bytes
Fixed Size                  2177328 bytes
Variable Size             339740368 bytes
Database Buffers          171966464 bytes
Redo Buffers                8052736 bytes
Database mounted.


--如果忘记执行上条语句而直接执行第5部分，则会提示
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 12036
Session ID: 9 Serial number: 5


============================5============================
SQL> alter database rename file 'E:\ICANMOVE2\USERS.DBF' to 'E:\icanmove3\USERS.DBF';


Database altered.


SQL> alter database rename file 'D:\ORACLE\ORADATA\ICANMOVE\UNDOTBS01.DBF' to 'E:\icanmove3\UNDOTBS0
1.DBF';


Database altered.


SQL> alter database rename file 'E:\ICANMOVE2\SYSAUX.DBF' to 'E:\icanmove3\SYSAUX.DBF';


Database altered.


SQL> alter database rename file 'D:\ORACLE\ORADATA\ICANMOVE\SYSTEM01.DBF' to 'E:\icanmove3\SYSTEM01.
DBF';


Database altered.


============================最后============================
SQL> alter database open;


Database altered.