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 狀態

Oracle Simple Replication 筆記

本篇為簡單的 Oracle 資料表同步設定，自己做個紀錄以免日後又忘記，讓資料庫專家們見笑了。目標是將 Source 資料庫中的 ABC 資料表同步到 Destination 資料庫中，並給予一個資料表名稱 TPE_ABC。隨後建立 Trigger 當 TPE_ABC 資料有異動時，將資料寫入另外一個總資料表 ALL_ABC。

DROP SNAPSHOT LOG ON SOURCE.ABC;

丟棄來源資料庫的 SNAPSHOT LOG

DROP MATERIALIZED VIEW DESTINATION.TPE_ABC;

丟棄目的端資料庫的 DROP MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG ON SOURCE.ABC
TABLESPACE SOURCE_MLOG
WITH PRIMARY KEY;

在來源資料庫建立 MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW DESTINATION.TPE_ABC
TABLESPACE TPE
NOLOGGING
BUILD DEFERRED
USING INDEX TABLESPACE TPE_I
REFRESH FAST
NEXT SYSDATE + 1/2
WITH PRIMARY KEY
AS SELECT
A, B, C, D,
ROWID AS SOURCE_ROWID
FROM SOURCE.ABC@SOURCE_DATABASE;

由來源資料庫建立 MATERIALIZED VIEW

CREATE TRIGGER "DESTINATION"."TR_TPE_ABC"
AFTER INSERT OR UPDATE OR DELETE OF
"A", "B", "C", "D"
ON "DESTINATION"."TPE_ABC"
FOR EACH ROW
BEGIN

IF ( DELETING OR UPDATING ) THEN
DELETE ABC WHERE SOURCE_DB = 'SOURCE_DATABASE'
AND A = :OLD.A;
END IF;

IF INSERTING OR UPDATING THEN
INSERT INTO ALL_ABC
( A, B, C, D, SOURCE_DB, SOURCE_ROWID, REC_SEQ_NO)
VALUES
( :NEW.A, :NEW.B, :NEW.C, :NEW.D, 'SOURCE_DATABASE', :NEW.SOURCE_ROWID, SEQ_SOURCE_DATABASE.NEXTVAL);
END IF;

建立 Trigger 當資料有異動時寫入