Oracle Datafile Rename 資料檔案更名筆記

方法一：

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES

先查詢 TABLESPACE 狀態

COLUMN NAME FORMAT A50

格式化輸入結果

SELECT FILE, STATUS, NAME FROM V$DATAFILE

查詢 DATEFILE 狀態

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DB_NAME\MONEY_Y.DBF' OFFLINE

將 DATAFILE 離線

在作業系統中將 "MONEY_Y.DBF" 重新命名為 "MONEY.DBF"

ALTER DATABASE RENAME FILE 'D:\ORACLE\ORADATA\DB_NAME\MONEY_Y.DBF' TO 'D:\ORACLE\ORADATA\DB_NAME\MONEY.DBF'

重新設定 DATAFILE 路徑

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DB_NAME\MONEY.DBF' ONLINE

將 DATAFILE 啟用

如遇到錯誤，則先 RECOVER DATAFILE 再重新啟用即可
SELECT * FROM V$RECOVER_FILE
RECOVER DATAFILE 'D:\ORACLE\ORADATA\DB_NAME\MONEY.DBF'
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DB_NAME\MONEY_Y.DBF' ONLINE

SELECT FILE, STATUS, NAME FROM V$DATAFILE

確認 DATEFILE 狀態

方法二：

ALTER TABLESPACE MONEY OFFLINE

將 TABLESPACE 離線

SELECT NAME, STATUS FROM V$DATAFILE;

查詢 DATEFILE 狀態

NAME                                     STATUS
---------------------------------------- -------
/ORACLE/HRASMUSS/DATA/DISK1/SYSTEM01.DBF SYSTEM
/ORACLE/HRASMUSS/DATA/DISK2/RBS01.DBF    ONLINE
/ORACLE/HRASMUSS/DATA/DISK3/DATA01.DBF   ONLINE
/ORACLE/HRASMUSS/DATA/DISK2/TEMP01.DBF   ONLINE
/ORACLE/HRASMUSS/DATA/DISK2/INDX01.DBF   ONLINE
/ORACLE/HRASMUSS/DATA/DISK3/OEMREP01.DBF ONLINE
/ORACLE/HRASMUSS/DATA/DISK1/QUERY01.DBF  ONLINE
/ORACLE/HRASMUSS/DATA/DISK4/DATA01.DBF   ONLINE
/ORACLE/HRASMUSS/DATA/DISK5/DATA02.DBF   ONLINE
/ORACLE/HRASMUSS/DATA/DISK3/INDX01.DBF   OFFLINE
10 ROWS SELECTED.

SQL> !MV $HOME/DATA/DISK3/INDX01.DBF $HOME/DATA/DISK6/INDX01.DBF

在作業系統中將 "INDX01.DBF" 重新命名為 "INDEX01.DBF"

ALTER TABLESPACE MONEY RENAME DATAFILE 'D:\ORACLE\ORADATA\DB_NAME\INDX01.DBF' TO 'D:\ORACLE\ORADATA\DB_NAME\MONEY01.DBF'

重新設定 DATAFILE 路徑

ALTER TABLESPACE MONEY ONLINE

將 DATAFILE 啟用

SELECT NAME, STATUS FROM V$DATAFILE;

確認 DATEFILE 狀態