紫糯米搬家了

原先開這個站的用意是要寫些關於資料庫的心得，與另外一個在ITHome純分享程式開發類別的網誌有所區隔。但最後方便管理起見，本網誌將即日起搬到 David' Blog，請舊雨新知移動尊駕到新網誌，小弟會繼續努力耕耘的...

利用IGNORE_DUP_KEY過濾重複的資料

要從SQL SERVER中某table過濾出重複的資料後，塞入另外一個table裡的方法很多。這幾天工作的關係，發現其實也可利用 ignore_dup_key 這個 relation_index_option來取巧。下面我建立個簡單的範例說明：

-------------------------------------------------------------------------------- --建立uniqindex , 並指定ignore_dup_key -------------------------------------------------------------------------------- --1. 建立target table，儲存無重複的資料 if exists (select name from sys.tables t where t.name = 'category')  drop table category go --2. 刪除既有的index create table category(cat_desc varchar(50), cat_code varchar(10)) if exists(select name from sys.indexes i where i.name = 'idx_cat')  drop index idx_cat on cayrgory go --3. 建立unique index，並指定ignore_dup_key create unique index idx_cat on category(cat_desc) with ignore_dup_key go --4. 建立1,000測試資料，其中5,000筆重複 declare @tb1 table(c1 varchar(50), c2 varchar(10)) declare @i int declare @loop_start_date datetime, @loop_end_date datetime declare @uniq_start_date datetime, @uniq_end_date datetime set @i = 1 set @loop_start_date = GETDATE() --設定迴圈起始時間 while @i <= 10000 begin  if @i % 2 = 0   insert @tb1 values('test' + cast((@i-1) as varchar(50)), cast((@i-1) as varchar(10)))  else   insert @tb1 values('test' +cast(@i as varchar(50)), cast(@i as varchar(10)))  set @i = @i + 1 end set @loop_end_date = GETDATE() --設定迴圈結束時間 set @uniq_start_date = GETDATE() --設定insert 至target table的起始時間 insert category(cat_desc, cat_code) select * from @tb1 set @uniq_end_date = GETDATE() --設定insert 至target table的結束時間 select 'inserting loop takes...' select DATEDIFF(SECOND, @loop_start_date, @loop_end_date) select 'inserting into uniq index with ignore_dup_key takes...' select DATEDIFF(SECOND, @uniq_start_date, @uniq_end_date) select * from @tb1 order by c1 select * from category order by cat_desc go

一開始我先建立一個target table，以儲存待會測試資料中已經過濾出來的無重複資料。在target table上我們建立一個unique index，並以WITH  IGNORE_DUP_KEY指定relational_index_option的值；接著宣告一個 table type的暫存資料表，並以迴圈塞入10,000筆資料，其中有5,000筆重複。最後我們直接將測試資料表insert 到target table中。這裡就是重點了。以往要是在建立索引時沒有加上 with ignore_dup_key這個選項，則insert到重複的資料時，就會得到一個錯誤的訊息，整個insert的transaction就會終止並且rollback：

訊息 2601，層級 14，狀態 1，行 8

無法以唯一索引 'idx_cat' 在物件 'dbo.category' 中插入重複的索引鍵資料列。

為了能略過該筆重複的資料所造成的錯誤並使insert動的繼續進行，create unique index時指定 with ignore_dup_key 即可辦到。以後如果懶得寫一堆過濾語法，或許可以偷個懶用用這個方法。

select 是否會使用 undo segment?

根據 Ask Tom的文章，我做了些實驗，得到的結論如文章中所描述：

• 單純的select 並不會使用到undo segment；
• 對於一個有一筆紀錄以上的Table來說，只要select statement 尾巴多加了 for update，就會用到undo segment。

我先建立一個測試表格T(char(1))，並insert了一筆資料，隨後進行以下的測試：

SQL> select used_ublk from v$transaction;                                                               沒有任何資料列被選取 SQL> truncate table t; 表格被截斷. SQL> select used_ublk from v$transaction; 沒有任何資料列被選取 SQL> insert into t values('1'); 已建立 1 個資料列. SQL> commit; 確認完成. SQL> select used_ublk from v$transaction; 沒有任何資料列被選取 SQL> select * from t for update; T1 -- 1 SQL> select used_ublk from v$transaction;  USED_UBLK ----------          1 SQL> commit; 確認完成. SQL> select used_ublk from v$transaction; 沒有任何資料列被選取 SQL>

請注意以上紅色部分，不管你有沒有異動T Table內的資料，只要一旦下了 for update，undo space就會備準備出來儲存現有的資料列版本，而且更恐怖的是Table Lock也同時跟著發生。一旦有其它 transaction 要進行異動，那真的是死在那邊等你解了。所以千萬小心、在意 for update用完要趕快commit以釋放undo space跟table lock，否則大禍臨頭(DBA找出兇手的時候)可就糗了...

SQL Writer 自發性的啟動?

SQL Writer 預設和SQL Server一起安裝，主要目的在於和VSS互動，讓備份軟體可在SQL Server運行時，執行備份或復原鎖定的data files。

這個服務預設是Disabled的，但我試著把SQL 2005 sp2升級到sp4，卻發現升級精靈檢查鎖定檔案時，SQL Writer服務卻出現了！奇怪，難道我又誤入了什麼嗎? 查了一下文章，才發現SQL SQL 2005 上了 sp2後就會自動的啟動這項服務。原文如下：

The SQL Writer Service provides added functionality for backup and restore of SQL Server 2005 through the Volume Shadow Copy Service framework.

This SQL Writer Service is automatically installed but not enabled by default. It must be explicitly enabled to run on the server machine and must be running at the time that the Volume Shadow Copy Service (VSS) application requests a backup or restore. Use the Microsoft Windows Services applet to configure the service. The SQL Writer Service installs on all operating systems but is only needed on server systems. For Microsoft Windows XP, use the MSDE Writer.

SQL Server 2005 Service Pack 2 configures SQL Writer to start automatically.

真是差點以為自己見鬼了...(筆記)

關於log file sync兩三事

今天Top Activity突然發現有趣的wait class：commit。看起來像下面橘色的那一小塊(請點圖放大)：

由於平常沒什麼特別注意，今日特別興起繼續往下追蹤，發現原來是 「log file sync」的wait event：

查了一下文章，原來是app可能大量用了batch transaction的寫法，但是log buffer或者redo log file所在的硬碟效率有待加強的時候，就容易發生。嗯，這個要開始注意了..

取得Oracle 物件的DDL

因工作關係，寫了取得Table、Procedure、Function、Trigger等物件的DDL的函式，並搭配C#使用；我把文章寫在這裡，分享囉。

替新建立的Control File建立Backup Repository

要Restore一個一模一樣的測試環境，備份檔來源紀錄會有兩個地方，一個來自於Source DB的Control file，另外一個則來自於RMAN Repository。假設，你想還原的是一個已經超過Retention Policy的備份檔，而且你想要用新建立的Control File來建立備份記錄，那該怎麼辦呢? 方法比想像中的簡單許多。只要利用RMAN的Catalog Start With指令，將備份檔整理到Control file就搞定了。舉例：

C:\> sqplus / as sysdba SQL> startup mount; SQL> exit; C:\> rman target / RMAN> catalog start with 'd:\oracle\orabak\';

這時候Control file就會有放在d:\oracle\orabak\的備份檔記錄了。

有趣的行話：Bit bucket

Bit bucket 在 Wiki 裡頭解釋的挺有趣的，它所舉的生活化例子，相當於我們用中文說＂資料莫名奇妙的消失在「黑洞」或「百慕達三角洲」＂的味道。

這名詞記下來給大家笑一下。

ORA-01019 on Windows 7

同事今天安裝Windows 7給User使用，其中有個應用程式在安裝完成後，執行時卻遇到了ORA-01019錯誤，由錯誤訊息看起來無法判斷是哪方面的問題造成的，不過經驗上判斷這應該跟權限問題比較有關。我試著先用tnsping是沒問題的，但如果用sqlplus 登入卻會出現以下錯誤：

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly


很有趣但又沒有用的的訊息。既然如此，換個方式來google一下SP2-1503呢？撲！答案找到了，原來是「控制台\系統管理工具\本機安全性設定\本機原則\使用者權限指派\建立通用物件(Create Global Object)」加入有需要權限的使用者即可。譬如說你希望所有網域使用者都能正常的登入Oracle，就將Domain Users群組加入就搞定。


註：XP稱Create Global Object為「建立全域物件」，個人覺得真是自找麻煩的變更

回復誤蓋的control file

實作restore的時候，不小心把source database的control file覆蓋到別的測試區的control file，等回神定眼一看，大錯已經造成。不幸中的大幸是測試區有做 3 個 multiplex control file，這次蓋到的只有兩個。立刻，心神慌亂的shutdown測試區instance(windows 平台直接關掉service)，接著馬上砍掉錯誤的兩個control file，把測試區那碩果僅存的control以另存新檔的方式做出兩個control file，並命名成正確的control file name(ex, copy control03.ctl control01.ctl)。最後，restart database即成功恢復運作。

好在搞掛的是測試區，換作正式區，我可無法解釋這5分鐘的downtime怎麼來的啊...@@