将数据文件复制到新的驱动器,再使用 alter tablespace yourtablespace rename datafile 'old_path_name' to 'new_path_name'; 然后删除旧文件,如果不能成功,先执行alter tablespace yourtablespace datafile offline; 完成后再online
在startup mount下转移数据文件. alter tablespace yourtablespace datafile
楼上说的对,在mount状态下执行 :)
数据文件迁移 14:37:23 SQL> create tablespace tsp_u01 datafile 'C:\oracle\oradata\tsp_u01.dbf' size 10M 14:38:52 2 autoextend on next 5M maxsize unlimited;表空间已创建。已用时间: 00: 00: 01.31 14:40:16 SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE RBS ONLINE USERS ONLINE TEMP ONLINE TOOLS ONLINE INDX ONLINE SFMIS ONLINE ZBMIS ONLINE HBMIS ONLINE HBXNMIS ONLINE OEM_REPOSITORY ONLINETABLESPACE_NAME STATUS ------------------------------ --------- TSP_U01 ONLINE已选择12行。已用时间: 00: 00: 00.63 14:40:38 SQL> alter tablespace tsp_u01 offline;表空间已更改。已用时间: 00: 00: 00.38 14:41:05 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSP_U01';TABLESPACE_NAME STATUS ------------------------------ --------- TSP_U01 OFFLINE已用时间: 00: 00: 00.16 14:41:26 SQL> alter tablespace tsp_u01 rename datafile 'C:\oracle\oradata\tsp_u01.dbf' 14:43:00 2 to 'C:\oracle\oradata\oral\tsp_u01.dbf';表空间已更改。已用时间: 00: 00: 00.63 14:43:32 SQL> alter tablespace tsp_u01 online;表空间已更改。已用时间: 00: 00: 00.81 14:43:45 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSP_U01';TABLESPACE_NAME STATUS ------------------------------ --------- TSP_U01 ONLINE已用时间: 00: 00: 00.15 14:45:35 SQL> drop tablespace tsp_u01 including contents cascade constraints;表空间已丢弃。已用时间: 00: 00: 00.28 14:45:43 SQL>
--Moving Data Files: ALTER TABLESPACE 1. Take the tablespace offline. 2. Use an operating system command to move or copy the files. 3. Execute the ALTER TABLESPACE RENAME DATAFILE command. 4. Bring the tablespace online. 5. Use an operating system command to delete the file if necessary.ALTER TABLESPACE usert1 OFFLINE --The tablespace must be offline. commit
ALTER TABLESPACE USERT1--The target data files must exist. RENAME DATAFILE 'C:\ORACLE\ORADATA\USERT1.DBF' TO 'C:\ORACLE\ORADATA\USERT1A.DBF'ALTER TABLESPACE usert1 ONLINE ---Moving Data Files:ALTER DATABASE 1. Shut down the database. 2. Use an operating system command to move the files. 3. Mount the database. 4. Execute the ALTER DATABASE RENAME FILE command. 5. Open the database.ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\USERT1A.DBF' TO 'C:\ORACLE\ORADATA\USERT1B.DBF'alter database open
然后删除旧文件,如果不能成功,先执行alter tablespace yourtablespace datafile offline; 完成后再online
在startup mount下转移数据文件.
alter tablespace yourtablespace datafile
14:37:23 SQL> create tablespace tsp_u01 datafile 'C:\oracle\oradata\tsp_u01.dbf' size 10M
14:38:52 2 autoextend on next 5M maxsize unlimited;表空间已创建。已用时间: 00: 00: 01.31
14:40:16 SQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
RBS ONLINE
USERS ONLINE
TEMP ONLINE
TOOLS ONLINE
INDX ONLINE
SFMIS ONLINE
ZBMIS ONLINE
HBMIS ONLINE
HBXNMIS ONLINE
OEM_REPOSITORY ONLINETABLESPACE_NAME STATUS
------------------------------ ---------
TSP_U01 ONLINE已选择12行。已用时间: 00: 00: 00.63
14:40:38 SQL> alter tablespace tsp_u01 offline;表空间已更改。已用时间: 00: 00: 00.38
14:41:05 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSP_U01';TABLESPACE_NAME STATUS
------------------------------ ---------
TSP_U01 OFFLINE已用时间: 00: 00: 00.16
14:41:26 SQL> alter tablespace tsp_u01 rename datafile 'C:\oracle\oradata\tsp_u01.dbf'
14:43:00 2 to 'C:\oracle\oradata\oral\tsp_u01.dbf';表空间已更改。已用时间: 00: 00: 00.63
14:43:32 SQL> alter tablespace tsp_u01 online;表空间已更改。已用时间: 00: 00: 00.81
14:43:45 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSP_U01';TABLESPACE_NAME STATUS
------------------------------ ---------
TSP_U01 ONLINE已用时间: 00: 00: 00.15
14:45:35 SQL> drop tablespace tsp_u01 including contents cascade constraints;表空间已丢弃。已用时间: 00: 00: 00.28
14:45:43 SQL>
1. Take the tablespace offline.
2. Use an operating system command to move or copy the files.
3. Execute the ALTER TABLESPACE RENAME DATAFILE command.
4. Bring the tablespace online.
5. Use an operating system command to delete the file if necessary.ALTER TABLESPACE usert1 OFFLINE --The tablespace must be offline.
commit
ALTER TABLESPACE USERT1--The target data files must exist.
RENAME
DATAFILE 'C:\ORACLE\ORADATA\USERT1.DBF'
TO 'C:\ORACLE\ORADATA\USERT1A.DBF'ALTER TABLESPACE usert1 ONLINE ---Moving Data Files:ALTER DATABASE
1. Shut down the database.
2. Use an operating system command to move the files.
3. Mount the database.
4. Execute the ALTER DATABASE RENAME FILE command.
5. Open the database.ALTER DATABASE RENAME
FILE 'C:\ORACLE\ORADATA\USERT1A.DBF'
TO 'C:\ORACLE\ORADATA\USERT1B.DBF'alter database open