低調

sysdate+1 加一天
sysdate+1/24 加1小時
sysdate+1/(24*60) 加1分鐘
sysdate+1/(24*60*60) 加1秒鐘

雖然是粉久以前的文章，不過非常具有參考價值，也非常精采。
原文網址

By Tommy Wu

我想各位都應該有那種資料被改了或被砍了, 但是沒有人要承認是他做的, 或者是那個程式設
計師不小心寫錯程式造成的, 這時, 老闆就會問你, 這到底是誰做的?

以前, 為了這個問題, 我們可能會在程式中加上一堆稽核的記錄, 記下一堆又臭又長的資料,
但是真的發生問題時, 找起來又不一定找的到罪魁禍首... 我記得每次用到最後, 都會把這部
份的功能關掉, 因為實在是沒什麼作用.

現在, 利用 ORACLE 所提供的 LOGMNR 的功能, 我們可以輕易的找到兇嫌了.

ORACLE 為了要做到 crash 時, 能夠由備份資料回復到之前的狀態, 所以對於每一筆交易, 都
會有記錄. 這些會存放在所謂的 archived log 之中, 在以前, 這個 log 的檔案, 只能用來做
為 recovery 使用. 但是現在, 我們也可以透過 ORACLE 所提供的功能來查詢裡面的內容, 這
個功能在 ORACLE 中叫做 LOGMNR.

要做這件事, 我們需要保留兩種檔案, 第一種當然是 archived log, 因為所有的交易記錄都放
在這類的檔案中. 另一種就是字典檔, 因為在 log 中並不存放物件的名稱, 為了要讓產生的資
料容易讀取, 必須要有字典檔來做對應.

Archived log 會隨著系統的交易而增加, 所以你必須決定要保留多久, 這個視你的需求而定,
以我們這兒為例, 原本保留一個月, 現在改成兩個月, 當然, 只要你的硬碟空間足夠, 要留多
久就可以留多久.

字典檔就必須自己產生, 有兩種方式, 一種是存到 ORACLE 的 control 檔案中, 一種是存放
指定的檔案中. 為了日後的查詢方便, 我們採用的是存到另外的檔案. 每天會產生一個新的字
典檔, 與 archived log 一樣, 保留下來, 以便於日後查詢使用.

產生字典檔的方法如下:


# 今天的日期
TODAY=`date +%Y%m%d`

sqlplus "/ as sysdba" <execute dbms_logmnr_d.build('${ORACLE_SID}_dictionary.ora', '/db/dictionary/${TODAY}',
options => dbms_logmnr_d.store_in_flat_file);
exit
EOF

上面的指令會產生一個字典檔放在 /db/dictionary/20020101 之類的路徑之中.
請注意上面的 execute 指令, 與下面那行是同一行, 請勿換行.

有了交易記錄檔與字典檔之後, 我們就可以查詢交易的內容了. 不過... 每日的交易檔可能數量是
很可觀的, 以我們這兒來說, 所定的每個交易檔的大小為 1MB, 每天約有 1000 個上下的交易檔產
生, 要找起來實在不是一件小工程. :-(

我們以下面的例子來說明:

假定我們要查的交易是在 arch0000093355.arc 這個交易記錄檔中, 就在 sqlplus 中執行下面的指
令:

execute DBMS_LOGMNR.ADD_LOGFILE('/log/arch0000093335.arc', dbms_logmnr.NEW);

如果還有其他的交易記錄檔, 如 arch0000093336.arc, 可以用下面的指令加上:

execute DBMS_LOGMNR.ADD_LOGFILE('/log/arch0000093336.arc', dbms_logmnr.ADDFILE);

依此類推, 你可以一次加上一堆記錄檔, 我通常習慣是每天的檔案都放在一起查...

把所有的交易記錄檔都加上之後, 就執行下面的指令啟動 LOGMNR 的功能:

execute dbms_logmnr.start_logmnr(DictFileName =>'/log/ORCL_dictionary.ora');

後面那個檔案就是你存下來的字典檔.
這個指令有另外的參數可以使用, 例如我們如果只要查詢有 commit 的交易時, 可以使用:

execute dbms_logmnr.start_logmnr(DictFileName =>'/log/ORCL_dictionary.ora',
options => dbms_logmnr.COMMITTED_DATA_ONLY);

請注意上面的 execute 指令, 與下面那行是同一行, 請勿換行.
其他的參數, 可以參考 ORACLE 的文件說明.

這個動作可能會很久, 等做完之後, 系統就會有一個 v$logmnr_contents 可以查詢.

v$logmnr_contents 這個 view, 比較常用的欄位有:
scn: 系統變更代碼
timestamp: 時間
username: 使用者名稱 (ORACLE 的使用者)
row_id: 指令所異動到的資料的 ROWID.
session#: 執行指令的 session 號碼.
serial#: 執行指令的 serial 號碼.
sql_redo: 執行的指令.
sql_undo: 回復的指令.
session_info: 這個 session 的資料. (可能是空的, 或許要查更早之前的 log, 看看這個
session 什麼時候登入的, 在登入後的那幾筆交易可以由這個看出那一個機
器連上來的, 使用那個 OS 的使用者.

直接使用 select 指令查詢相關的內容, 就可以得知在何時誰做了什麼事情...
例如, 有人下了 delete 指令, 我們可以查詢

select session_info, sql_redo from v$logmnr_contents
where sql_redo like 'delete%';

這個查詢會把這些交易記錄中所有 delete 開頭的指令取出來.

查詢後執行下面的指令, 結束 LOGMNR 的運作:

execute dbms_logmnr_end_logmnr();

查詢的技巧... 自己多試試就知道了, 你也可以 create 另一個 table, 把這個 view 的資料先
存過去, 再來查詢, 當資料多的時候, 可能會比較方便一些.

最後... 要提的就是, 雖然可以知道那一個使用者在那一台機器, 那個時間做了這件事, 但是實
際上... 還是有人要說不是他做的... 說可能有人用他的密碼, 在他的機器上做, 或者他開機後
離開位子, 不曉得誰過來用他的電腦... 這些事情, 已經不是電腦可以處理的了, 應該歸於行政
管理上面的要求.

請所有的使用者, 不要用太簡單的密碼, 也不要讓別人知道你的密碼, 在離開位子的時候, 也請
將電腦的畫面鎖住... 否則, 這事就算在你的頭上.


另外, 在 ORACLE 9.0.1.x 的版本中 (到 9.0.1.3 為止), 這個功能有點問題, 可能會造成你在
select 這個 view 的時候, 造成 ORACLE hang 住, 或產生 core dump. 這個問題據說已經被修
正, 但無法提供單一的 patch, 所以到等到 9.0.1.4 版本才可以使用, 或者是使用 9.2.0.1 這
個版本也可以.

我們這兒的解決方法就是裝一套 ORACLE 9.2.0.1 專門來處理這個問題.

本文轉貼自 http://www.wisc.edu/drmt/oratips/sess004.html

Oracle Tips Session #4
2/11/98
Dave Ruhde

Table of Contents

• Terms

• Review Read Consistency/Concurrency in Oracle

• Row Locks

• Select For Update

• Table Locks

• Lock Failures

• Application Usage of Locking

• Isolation Levels

• Examples

Terms

Concurrency

How well can multiple sessions access the same data simultaneously

Consistency

How consistent is the view of the data between and within multiple sessions, transactions or statements

Statement

A SQL command that reads/changes data in the database. Changes made may or may not be committed (made permanent).
ex. SELECT, SELECT...FOR UPDATE, INSERT, UPDATE, DELETE

Session

A connection (as a single Oracle database user) to the database by an external program. The program that opens the Oracle session may be purely interactive--SQL statements are explicitly submitted by a person, or the program could be a developed application program using any number of languages

A database user may have mulitple concurrent sessions. All sessions, whether by multiple users or the same user, are independent.

Transaction

All changes to the database are made through transactions. A transaction consists of any number of SQL statements.

A transaction ends with a commit or a rollback. If the transaction is committed, ALL CHANGES by SQL statements in the transaction are saved. If the transaction is rolled back, ALL CHANGES by SQL statements in the transaction are undone.

A new transaction begins when a session is opened and after each rollback or commit.

Commit

Makes permanent all changes made in a transaction. Changes are not made part of the database until the transaction is commited. A commit is made when:

• a 'COMMIT' statement is issued
• the application program ends and disconnects its Oracle session normally.

Rollback

Undo any uncommitted changes made by a transaction. The transaction is ended. A rollback occurs when:

• a 'ROLLBACK' statement is issued
• Oracle terminates a session because
  • a application program or database exception is not handled and the application program terminates abnormally
  • a hardware or system failure causes the application program to terminate
  • a network failure causes the network connection between the connecting program and Oracle to be interrupted.
  • Oracle fails

Review Read consistency/concurrency

Consistency

All Oracle SQL statements ALWAYS work with a image of data that is consistent to a single point-in-time. The consistent image consists of all changes and only changes COMMITTED before the point-in-time of the image.

The consistent image nevers shows changes by other sessions that have not been committed to the database. Oracle never performs a "dirty read"

SQL statements do see changes, not yet committed, made by previous statements within the transaction the statement is part of.

By default, consistency is at the statement level not the transaction level. SQL statements return data using a consistent image based on the point-in-time the STATEMENT began, not when the transaction began. Each statement in a transaction works with its own consistent image.
Session 1, Example 1
Session 1, Example 2
Session 1, Example 3

Concurrency

Readers don't block readers.

Readers don't block writers.
Note: SELECT ... FOR UDPATE behaves as a writer

Writers don't block readers.

Row Locks

SELECT..FOR UPDATE, UPDATE, DELETE and INSERT statements always lock the row(s) that the statement is modifying.

Only one transaction can lock a given row at a time.

Other transactions can not update or lock a row that is locked.

Locks are NOT released when a statement completes. Locks are released when the transaction is committed or rolled-back.

Example 1

SELECT...FOR UPDATE

Locks row(s) from all table(s) that are selected by the query.

The row locks are released when the transaction that contains the SELECT...FOR UPDATE is committed or rolled-back.

OF cols: Lock only rows from tables which have a column listed in the OF clause. Rows are not locked in tables that don't have a column appearing in the OF clause. If no OF clause is listed, rows from all tables are locked.

NOWAIT: If NOWAIT is NOT specified and a row to be locked is locked by another transaction, SELECT...FOR UPDATE will wait indefinitely until the lock is released. If NOWAIT is specified and a row to be selected is locked by another transaction, the SELECT...FOR UPDATE will return immediately with a "ORA-00054: resource busy and acquire with NOWAIT specified" error.

Example 2
Example 3 - FOR UPDATE OF
Example 4 - NOWAIT

TABLE LOCKS

Successful completion of this statement means no other transaction can update or lock a row in the locked table(s) or lock the locked table(s) until the lock are released.

The table locks are released when the transaction that contains the LOCK TABLE is committed or rolled-back.

NOWAIT: If NOWAIT is NOT specified, the LOCK TABLE waits indefinitely until all existing row or table locks on the table(s) to be locked are released. If NOWAIT is specified, LOCK TABLE returns immediately with a "ORA-00054: resource busy and acquire with NOWAIT specified" error.

Table locks do not block readers.

Example 5

Lock Failures

"ORA-00060 deadlock detected while waiting for resource"
Transaction 1 locks row A, then Transaction 2 locks row B.

Then Transaction 1 tries to lock Row B and transaction 2 tries to lock row A.

Each transaction is waiting on the other.

Oracle automatically detects deadlocks. When a deadlock is detected the last statement fails with a ORA-00060.

Application Usage of Locking

Simple techniques like the following will not hold records locked and will likely meet true operational needs.

SELECT * INTO editrec FROM emp WHERE empno=:empnum
/* Record is not locked */
 User Edits row....
UPDATE emp .... WHERE empno = :empnum

Isolation Levels

Read Committed (Default)
Statements see changes as of the point-in-time of the start of the statement.

Read Only
Statements see changes committed as of the point-in-time of the start of the transaction. No updates allowed in transaction.

Serializable
Statements see changes committed as of the point-in-time of the start of the transaction. Updates are allowed, but:

A row that has been changed and committed by another transaction after the serialized transaction began will cause the serialized transaction to fail with a ORA-08177: can't serialize access for this transaction" error.

Serializable mode can only be used after consultation with DBA. Tables must be specially initiated to participate in a serializable transaction.

Usage

To set for a single transaction, first statement in transaction is:

SET TRANSACTION READ ONLY
SET TRANSACTION ISOLATION LEVEL READ COMMITTED (default)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

To set for all subsequent transactions within session:

ALTER SESSION ISOLATION LEVEL READ COMMITTED
ALTER SESSION ISOLATION LEVEL SERIALIZABLE

EXAMPLES

Example 1

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
update cust set color='blue';






-----------COMMIT--------------

-------BEGIN TRANSACTION-------
select color from cust
where id=500;
 color
 -----
 red


update cust set color=green
where id=500;

(Waiting...)

1 row updated.


select color from cust
where id=500;
 color
 -----
 green
-----------COMMIT--------------

Example 2

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust where
id=500 for update;
 color
 -----
 red



update cust set color='blue'
where id=500;



----------ROLLBACK--------------

-------BEGIN TRANSACTION-------
select color from cust
where id=500;
 color
 -----
 red


update cust set color=green
where id=500;

(Waiting...)



(Still waiting...)



1 row updated.

-----------COMMIT--------------

Example 3

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select c.color, t.typedesc
from cust c, custtype t where
cust.id=500 and c.type=t.type
for update nowait;

 color  typedesc
 -----  --------
 red    CASH ONLY

ROLLBACK;


select c.color, t.typedesc
from cust c, custtype t where
c.id=500 and c.type=t.type
for update of c.id nowait;

 color  typedesc
 -----  --------
 red    CASH ONLY
----------COMMIT--------------

-------BEGIN TRANSACTION-------

update custtype
set status='VALID';




(Waiting...)


100 rows updated.
-----------COMMIT--------------



update custtype
set status='INVALID';



100 rows updated.

-----------COMMIT--------------

Example 4

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;
 color
 -----
 red

update cust set color='blue'
where id=500;

1 row updated.
-----------COMMIT--------------

-------BEGIN TRANSACTION-------

select color from cust
where id=500 for update nowait;


ORA-00054





select color from cust
where id=500 for update nowait;

update cust set color='blue'
where id=500;

1 row updated.

-----------COMMIT--------------

Example 5

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;
 color
 -----
 red
-----------ROLLBACK--------------



-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;

ORA-00054
-----------COMMIT--------------

-------BEGIN TRANSACTION-------

LOCK TABLE cust in
exclusive mode nowait;

ORA-00054



LOCK TABLE cust in
exclusive mode nowait;

update cust set color='blue'

100 row updated.

-----------COMMIT--------------

本文轉貼自 http://www.dbmotive.com/oracle_error_codes.php?errcode=00060
關於 DeadLock 的發生在這篇有不錯的範例說明：

Transactions deadlocked one another while waiting for resources.

Once Oracle detects a conflict between transactions, it will automatically stop the transaction of one of the sessions so they will not block each other for ever.

You will find a trace file in the user_dump_dest containing the objects involved.

eg:


First setup 2 tables:

SQL> create table t1(n number);

Table created.

SQL> create table t2(n number);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> commit;

Commit complete.

Now you need to have 2 sessions (S1 and S2)

Timestamp   session   action

T1          S1        update t1 set n=3;
T2          S2        update t2 set n=4;
T3          S2        update t1 set n=2; --> This will wait until S1 commits the action done in T1
T4          S1        update t2 set n=4; --> This will wait until S2 commits the action done in T2

Now S2 is waiting for S1 to commit the transaction done in T1
and S1 is waiting for S2 to commit the transaction done in T2

This is a deadlock situation. After a while, S2 will time out with following error:

SQL> update t1 set n=2;
update t1 set n=2
      *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Remark: Only the action done at T3 will timeout, the lock set by S2 in T2 is still there.
S1 will still wait for the commit/rollback of the transaction done in T2