建立 Oracle 觸發 Trigger 來 Debug

有時候系統會到遇到靈異事件，
經常發生還容易查，用 r.d2+ 就大概可以 debug 出來問題在那，
偶發情況然後又只要重新再做一次又正常了，不知道到底是時間差、還是資料的問題，或是程式的 bug。
系統又沒有記錄的話，使用者刪除或修改資料是不會承認的，都是朝系統的問題來處理。

只好建立 Trigger 來記錄資料的異常歷史記錄了，看到底是誰搞的鬼。
在 TIPTOP 有提供 log_file 和 aooq040 提供 Trigger log 的查詢，但是 Trigger 要自已在 Oracle 建立。

Trigger 的語法：
CREATE OR REPLACE TRIGGER 觸發名稱
BEFORE INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
AFTER INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
FOR EACH ROW
記錄異動的資料
END;

bmd_file 異動資料的範例，把異動的欄位記錄在 log_file：
create or replace trigger bmd_file_log
--只記錄4 個欄位的異動
after insert or update or delete of bmd01,bmd02,bmd03,bmd04,bmd08 on bmd_file
for each row
declare l_col varchar2(3);
        l_user varchar2(6);
        l_type varchar2(20);
        l_cnt  number;
begin
l_col :='';
--先找有沒有資料，當執行失敗時 trigger 就會中止，所以要先找是否有資料，SID 是唯一值，SESSION有可能重複。
select count(*) into l_cnt from v$session,gbq_file where process=gbq01 and sid = userenv('sid') and process not like '%:%';
if l_cnt > 0 then
--把TIPTOP使用者和程式代號找出來
   select gbq03,gbq04 into l_user,l_type from v$session,gbq_file where process=gbq01 and audsid = userenv('sessionid') and process not like '%:%';
else
--把非TIPTOP的使用者和程式找出來
   l_user:=sys_context('userenv','os_user'); l_type:=substr(sys_context('userenv','module'),1,10);
end if ;
--新增
if inserting then
   l_col :='ins';
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
--修改就記錄修改前和修改後
if updating then
   l_col :='upd';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col||'-old',sysdate,l_user,l_type,'','');
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col||'-new',sysdate,l_user,l_type,'','');
end if;
--刪除
if deleting then
   l_col :='del';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
end;

要刪除 TRIGGER ，就用 drop trigger 名稱，就可以刪除了。

更便利的 DIALOG - 同時單頭單身查詢/輸入

剛開始使用 TIPTOP 很多人都會問為什麼查詢要按二次確定，
需要單頭按確認後再到單身再按確認，才會執行查詢的動作，
用 DIALOG 就可以同時做單頭、單身的查詢。

再來操作上不方便的就是，像工單發料單 asfi510 或多主件工程變異單 abmi710 ，
像這種多單身的樣式，就不能同時將二個單身做 scroll bar 的捲動和資料不能複製到剪貼簿，
一般都會再做一個按鈕查詢另一個單身，然後操作完後要再跳回去原本的單身，實在是很不方便。
就是可以用 DIALOG 來達到比較人性化的介面。

在 Genero 官方文件說明：
The DIALOG block is an interactive instruction that executes multiple kinds of sub-controllers simultaneously to drive different parts of a form.

只要將 CONSTRUCT 和 CONSTRUCT ARRAY 都包在 DIALOG 裡面，就可以做到單頭和單身同時查詢。
將二個不同的 DISPLAY ARRAY 也包在 DIALOG 裡面，也一樣可以同時操作這二個單身的 Action 和 Scroll Bar。

DIALOG ATTRIBUTES(UNBUFFERED)
     DISPLAY ARRAY g_bmz to s_bmz.* ATTRIBUTE(COUNT=g_rec_b)
     END DISPLAY
     DISPLAY ARRAY g_bmy to s_bmy.* ATTRIBUTE(COUNT=g_rec_d)
     END DISPLAY
END DIALOG

DIALOG 提供 INPUT、CONSTRUCT、DISPLAY ARRAY、INPUT ARRAY 的多重控制功能，
利用這個功能就可以做到單頭、單身輸入完資料後，才會產生單號和同時寫入資料到 TABLE，
作廢的單子或許就可以少了一些。

要判斷目前 DIALOG 目前控制在那一個的話，提供一個函式 DIALOG.getCurrentItem() 回傳就是畫面的變數值。
這樣多單身就匯出 Excel 就可以判斷要匯出是那一個單身的資料。

官方網站提供其中一個例子做動態的資料查詢，單頭輸入條件後單身就馬上顯示出資料，
原本是寫在 AFTER FEILD 後加上 DISPLAY ARRAY 也是可以做到，但是缺點就是資料的捲軸沒辦法動。
用 DIALOG 就可以做到像 Windows 應用程式類似的作業，盡可能用 DIALOG 來開發程式吧!!

回復 Oracle 刪除或是已更新的資料

我們都知道還原資料最多就是只能還原到最近一前的備份點，
但是備份不可能是隨時隨地都在備份，
Oracle 有一個 undo 的機制，會將資料進行異動之前就進行 Data Image，
原本的用意為 A 進行 Query 之後 B 又進行 Update 時，A 的 Query 結果會是當時下達指令時點的資料，
就不會被 B 在 Update 時所影響，也不會造成資料不一致。
因此我們就可以利用此功能回復到你所想要的某一時間的資料。
undo 區分為 ACTIVE、UNEXPIRED、EXPIRED 三種，
當資料 commit 或 rollback 後就會改變為非活動中的狀態。

查看目前 undo 的狀態：
select * from DBA_UNDO_EXTENTS

利用 Oracle 的 undo log 來查到底是誰把資料做刪除或變更的，
使用 LogMiner 來開啟 undo log 或 archived log 檔案，就可以查出 SQL_TEXT 和 Session ，
就知道到底是誰幹的好事。

先來看 Oracle 的參數：
sqlplus： show parameter undo;
或
select * from v$parameter where name like '%undo%'

NAME                   VALUE
--------------------------
undo_management  AUTO
undo_tablespace     UNDOTBS1
undo_retention        900

undo_retention 保存時間(秒) 900 秒，
設定 undo_retention 越大就會佔用更多的 undo tablespace 的空間。
要是 undo 空間不足時，才會把 undo_retention 以前的資料蓋掉，
當 undo tablespace 設定 RETENTION GUARANTEE 時，
還是不夠 undo 空間就會自動擴展，增加 undo tablespace 檔案容量，
必須要保証磁碟空間是足夠的，當有大量資料異動時，會造成 undo 檔案過大。
設定為 RETENTION NOGUARANTEE ， undo tablespace 不會擴增，
當容量不足時就會一直把非活動的空間蓋掉，所以 undo_retention 不保証一定會保存完整資料。

將 undo_retention 改為 1 小時，指令如下：

alter system set undo_retention = 3600

修改 undo tablespace 的屬性，開啟 retention 自動擴展：
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE

關閉 retention 自動擴展：
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE

預設 undo_management 都是 auto 管理，
undo_retention 的值會由 Oracle 自動進行最佳的調整，
所以超過 undo_retention 的時間，也有可能狀態還是在 UNEXPIRED 。
因為 Oracle 每 10 分鐘就會依 undo tablespace 的空間和 Oracle 操作狀況來設定最佳的 undo_retention 值。

查看 Oracle 所設定的 undo_retention (秒)
SELECT * FROM v$undostat;

要如何將資料還原，只要加上 timestamp 就可以查到當時的時間點上資料內容。
select * from ds.zx_file as of timestamp to_timestamp('2013-03-15 15:55:29', 'yyyy-mm-dd hh24:mi:ss');

查出來結果應該會很開心，好險資料還在。
再來就是把資料還原就可以了，一般都是用 create table 的方式，再比對資料後做更新。
create table ds.zx_temp as
select * from ds.zx_file as of timestamp to_timestamp('2013-03-15 15:55:29', 'yyyy-mm-dd hh24:mi:ss');

scn (System Change Number) 當資料異動時，就會將 scn + 1 保存在 undo log 。

比較建議還是用 scn 會比較避免同一秒的資料異動時，資料還原的完整性。

再來就是 scn 和 timestamp 在 9i 會有 5 分鐘的資料同步，

所以 9i 用 timestamp 進行 flashback query 時會查不到 5 分鐘內異動的資料。

在 10g 以後的版本就修正此情況， scn 和 timestamp 會在 LogMiner 取得。

改用 scn 的方式來查詢：

select * from ds.zx_file as of scn 2267300000

查詢最近一次資料異動的 scn 和 timestamp ：

select scn_to_timestamp(dbms_flashback.get_system_change_number) ,dbms_flashback.get_system_change_number from dual;

雖然 Oracle 這個機制可以讓我們放心資料異動的問題，但還是有限制：

1. Table Schema 變更就不能回復，因 flashback query 是用目前的資料字典。

2. 不能 flashback 到 5 天前的資料。

3. 不能保証 undo_retention 所保存資料是否完整，undo tablespace 過小會蓋掉舊的。

4. drop、truncate 不需 commit ，所以就不能回復，所以要把 truncate 和 drop 指令鎖住。

一般的帳號也可以使用 flashback 的功能，設定權限：

grant execute on dbms_flashback to USER ;

想要看某筆資料所有異動的紀錄，在 Oracle 有 versions 的指令來查看每個版本的資料和起始、結束時間
欄位 versions_starttime 表示版本開始時間，欄位 versions_endtime  表示版本結束時間
使用方式只要在 table 後面加上 versions between timestamp minvalue and maxvalue 就可以了。
範例：select zx_file.*,versions_starttime,versions_endtime from ds.zx_file versions between timestamp minvalue and maxvalue