潛水珽的異想世界

前言:

相信使用過Oracle一定會會很熟悉這隻蟾蜍,由於Oracle本身所提供的工具都是java base的GUI工具,因此往往使用這些工具會消耗掉本身PC的資源(memory),且因為Oracle的架構太過於龐大,所以一般縱使是有經驗的DBA也都會使用TOAD來進行管理資料庫的工作,將相關的命令以圖形化的介面加以完成,這篇文章裡介紹的是如何利用TOAD來進行routine的管理工作。

實際演練:

現在我們要排程定期去利用toad來幫我們做一些事情,像現在我們想排程每天晚上幫我們轉object schema的report,可以根據下列的步驟來利用command line的方式將命令排入os的schdule中:

1.先打開TOAD,並將compare schema打開,設定好你要比較的一些參數後,在option的頁籤中選擇存檔,切記,請不要按下compare鈕。

2.將上述的設定檔存檔後使用notepad打開,會看到一堆有的沒有參數,你一定很好奇那些參數是做甚麼用的,別擔心,這個檔案中也會列出如何使用command line來執行compare的功能。

3.使用"c:\toad\toad.exe Connect=system/manager@mydb COMP=c:\thisfile.txt"來執行TOAD 的command line命令,其中"c:\toad\toad.exe"指的是TOAD主程式執行檔的所在位置,"CONNECT=system/1234@db"指的是連線資料庫的帳號與連線字串,"COMP=c:\commandfile.txt"指的是要執行compare schema的命令檔。

幾個簡單的command說明(以compare schema為例):

SaveInteractiveResultsAsText(‘c:\InteractiveResultsFile.txt’) –

Saves contents of ‘Interactive Results’ Tab in a text format. Saving them in an RTF format is not available.

SaveRTFResultsAsRTF(‘c:\RTFResultsFile.rtf’)  -

Saves contents of ‘Results(RTF)’ Tab in RTF Format.

SaveRTFResultsAsText(‘c:\TextResultsFile.txt’)  -

Saves contents of ‘Results(RTF)’ Tab in Text Format.

SaveSummaryAsRTF(‘c:\RTFSummaryFile.rtf’)  -

Saves contents of ‘Results(Summary)’ Tab in RTF Format.

SaveSummaryAsText(‘c:\RTFSummaryFile.txt’)  -

Saves contents of ‘Results(Summary)’ Tab in Text Format.

SaveSyncScript('c:\SyncScript.sql') –

Saves the SyncScript (sql to transform the comparison source schema into the reference source schema) in the specified file. You can change the file path and name.

EmailInteractiveResultsAsText  -

Saves contents of ‘Interactive Results’ Tab in a text format and emails the file to the address specified in View|Toad Options|Email Settings. RTF format is not available.

EmailRTFResultsAsRTF  -

Saves contents of ‘Results(RTF)’ Tab in RTF Format and emails the file to the address specified in View|Toad Options|Email Settings.

EmailRTFResultsAsText  -

Saves contents of ‘Results(RTF)’ Tab in Text Format and emails the file to the address specified in View|Toad Options|Email Settings.

EmailSummaryAsRTF  -

Saves contents of ‘Results(Summary)’ Tab in RTF Format and emails the file to the address specified in View|Toad Options|Email Settings.

EmailSummaryAsText  -

Saves contents of ‘Results(Summary)’ Tab in Text Format and emails the text file to the address specified in View|Toad Options|Email Settings.

EmailSyncScript –

Saves contents of the SyncScript and emails the file to the address specified in View|Toad Options|Email Settings.

CloseToad  -

Closes Toad after the script finishes

CloseComparison  -

Closes the schema comparison window after the comparison has finished.

結語:

TOAD到後期功能做的越來越強大,也越來越複雜,而會用到這個功能,其實還要歸功於台大的同事問我有關"定期排程進行資料庫物件比對"的問題,我相信TOAD中一定還有很多很強大的功能,筆者後續會繼續跟大家分享。