Oracle 到 SQL Server 和 SQL Server 到 Oracle 遷移指南 - Pt。 3

本系列的第一部分和第二部分討論了 Oracle 數據庫和 Microsoft SQL Server 在事務實現方面的差異，以及由此產生的轉換陷阱，以及一些常用的語法元素。

最後一部分將介紹 Oracle 讀取一致性的概念以及如何將基於此概念的體系結構轉換為 Microsoft SQL Server 版本。 它還將解決同義詞的使用（以及如何不使用它們）以及變更控製過程在管理數據庫環境中的作用。

SQL Server 中的 Oracle 讀取一致性及其等效項

Oracle 讀一致性是對單個 SQL 語句返回的所有數據都來自同一個奇異時間點的保證。

這意味著如果您在 12:01:02.345 發出SELECT語句並在返回結果集之前運行了 5 分鐘，那麼截至 12:01:02.345 已提交到數據庫中的所有數據（並且只有數據）都會成功進入你的回報集。 在數據庫處理您的語句的那 5 分鐘內，您的返回集不會添加任何新數據，也不會有任何更新，也不會看到任何刪除。

Oracle 架構通過在內部對數據的每次更改加時間戳並從兩個源構建結果集來實現讀取一致性：永久數據文件和撤消段（或“回滾段”，直到版本 10g 才知道）。

為了支持它，應該保留撤消信息。 如果它被覆蓋，則會導致臭名昭著的ORA-01555: snapshot too old錯誤。

撇開撤銷段管理——以及如何導航ORA-01555: snapshot too old錯誤——讓我們看看讀取一致性對 Oracle 中任何實際實現的影響。 此外，它應該如何在 SQL Server 中進行鏡像，這與其他 RDBMS 實現的情況一樣，除了 PostgreSQL 的可能和合格的例外 - 不支持它？

關鍵是Oracle讀寫不會互相阻塞。 這也意味著您的長時間運行的查詢返回集可能沒有最新數據。

非阻塞讀寫是 Oracle 的一個優勢，它會影響事務範圍。

但是讀取一致性也意味著你沒有數據的最新狀態。 在某些情況下它非常好（例如為特定時間生成報告），它可能會在其他情況下產生重大問題。

沒有最新的——甚至是“骯髒的”或未提交的——數據可能很關鍵：經典場景是酒店房間預訂系統。

考慮以下用例：您有兩個同時接受房間預訂訂單的客戶服務代理。 如何確保房間不會超額預訂？

在 SQL Server 中，您可以啟動顯式事務並從可用房間的列表（可以是表或視圖）中SELECT一條記錄。 只要此事務未關閉（通過COMMIT或ROLLBACK ），沒有人可以獲得您選擇的相同房間記錄。 這可以防止重複預訂，但也會讓每個其他座席都按順序等待對方一次完成一個預訂請求。

在 Oracle 中，您可以通過針對匹配搜索條件的記錄發出SELECT ... FOR UPDATE語句來獲得相同的結果。

注意：存在更好的解決方案，例如設置一個臨時標誌來標記房間“正在考慮中”，而不是盲目地鎖定對它的訪問。 但這些是架構解決方案，而不是語言選項。

結論：Oracle 讀一致性並不是“全好”或“全壞”，而是平台的一個重要屬性，需要很好地理解，對跨平台代碼遷移至關重要。

Oracle 和 Microsoft SQL Server 中的公共（和私有）同義詞

“公共同義詞是邪惡的。” 這不完全是我個人的發現，但我一直接受它作為福音，直到我的一天、一周和一年被公共同義詞拯救。

在許多數據庫環境中——我會說我有機會使用的所有 Oracle 環境，但沒有一個是我設計的——對每個對象使用CREATE PUBLIC SYNONYM是例行公事，因為“我們一直都是這樣做的”。

在這些環境中，公共同義詞只有一個功能：允許在不指定所有者的情況下引用對象。 這是公開同義詞的一個深思熟慮的原因。

然而，Oracle 公共同義詞可能非常有用，如果正確且有理由地實施和管理，它可以為團隊帶來顯著超過其所有缺點的生產力優勢。 是的，我說的是“團隊生產力”。 但是怎麼做？ 為此，我們需要了解名稱解析在 Oracle 中是如何工作的。

當 Oracle 解析器查找名稱（非保留關鍵字）時，它會嘗試按以下順序將其與現有數據庫對象匹配：

注意：引發的錯誤將是ORA-00942: table or view does not exist ，或PLS-00201: identifier 'my_object' must be declared 。

在這個名稱解析順序中，很容易看出，當開發人員在自己的模式中工作時，任何與公共同義詞同名的本地對像都會隱藏這個公共同義詞。 （注意：Oracle 18c 實現了“僅登錄”模式類型，此討論不適用於它。）

擴展團隊的公共同義詞：Oracle 變更控制

現在讓我們看看一個由 100 名開發人員組成的假設團隊，他們在同一個數據庫上工作（這是我所經歷的）。 此外，讓我們假設他們都在他們的個人工作站上本地工作並獨立進行非數據庫構建，所有這些都鏈接到同一個數據庫開發環境。 非數據庫代碼（無論是 C#、Java、C++、Python 還是其他任何東西）中的代碼合併解析將在更改控制簽入時完成，並將在下一次代碼構建時生效。 但是數據庫表、代碼和數據在持續開發過程中需要多次來回更改。 每個開發者獨立執行此操作，並立即生效。

為此，所有數據庫對像都在一個通用應用程序模式中創建。 這是應用程序引用的模式。 每個開發者：

• 使用他們的個人用戶帳戶/模式連接到數據庫
• 總是從一個空的個人架構開始
• 如上所述，僅通過名稱解析到公共同義詞來引用公共模式

當開發人員需要對數據庫進行任何更改（創建或更改表、更改過程代碼，甚至修改一組數據以支持某些測試場景）時，他們會在其個人模式中創建對象的副本。 他們通過使用DESCRIBE命令獲取 DDL 代碼並在本地運行它來做到這一點。

從這一刻起，此開發人員的代碼將看到對象和數據的本地版本，其他任何人都看不到（也不會影響）。 開發完成後，將修改後的數據庫代碼簽入源代碼管理，並解決衝突。 然後，最終代碼（和數據，如果需要）在通用模式中實現。

在此之後，整個開發團隊可以再次看到相同的數據庫。 剛剛交付代碼的開發人員會從他/她的個人模式中刪除所有對象，並準備好接受新任務。

這種為多個開發人員促進獨立並行工作的能力是公共同義詞的主要好處——這一重要性怎麼強調都不為過。 然而，在實踐中，我繼續看到團隊在 Oracle 實施中創建公共同義詞“只是因為我們總是這樣做”。 相比之下，在使用 SQL Server 的團隊中，我不認為創建公共同義詞已成為一種常見做法。 該功能存在但不經常使用。

在 SQL Server 中，用戶的當前默認架構是在用戶配置中定義的，如果您具有“更改用戶”權限，則可以隨時更改。 可以實施與上述 Oracle 完全相同的方法。 但是，如果不使用此方法，則不應複製公共同義詞。

由於默認情況下 Microsoft SQL Server 不會將新用戶帳戶與其自己的架構相關聯（如 Oracle 所做的那樣），因此該關聯應該是標準“創建用戶”腳本的一部分。

下面是一個創建專用用戶模式並將其分配給用戶的腳本示例。

首先，為需要加入名為DevelopmentDatabase的數據庫的新用戶創建模式（每個模式必須在自己的批次中創建）：

 use DevelopmentDatabase; GO CREATE SCHEMA Dev1; GO CREATE SCHEMA Dev2; GO

其次，使用分配的默認模式創建第一個用戶：

 CREATE LOGIN DevLogin123 WITH PASSWORD = 'first_pass123'; CREATE USER Dev1 FOR LOGIN DevLogin123 WITH DEFAULT_SCHEMA = Dev1; GO

此時，用戶Dev1的默認架構將是Dev1 。

接下來，創建另一個沒有默認架構的用戶：

 CREATE LOGIN DevLogin321 WITH PASSWORD = 'second_pass321'; CREATE USER Dev2 FOR LOGIN DevLogin321; GO

用戶Dev2的默認架構是dbo 。

現在更改用戶Dev2以將其默認架構更改為Dev2 ：

 ALTER USER Dev2 WITH DEFAULT_SCHEMA = Dev2; GO

現在用戶Dev2的默認架構是Dev2 。

此腳本演示了為 Microsoft SQL Server 數據庫中的用戶分配和更改默認架構的兩種方法。 由於 SQL Server 支持多種用戶身份驗證方法（最常見的是 Windows 身份驗證），並且用戶登錄可能由系統管理員而不是 DBA 處理，因此分配/更改默認架構的ALTER USER方法將更有用。

注意：我使架構的名稱與用戶的名稱相同。 在 SQL Server 中不必這樣，但這是我的偏好，因為 (1) 它與 Oracle 中的操作方式相匹配，並且 (2) 它簡化了用戶管理（解決了 DBA 對正確操作的最大反對意見首先）——你知道用戶的名字，並且你自動知道用戶的默認模式。

結論：公共同義詞是構建穩定且受到良好保護的多用戶開發環境的重要工具。 不幸的是，在我對行業的觀察中，它更經常被用於錯誤的原因——讓團隊遭受公共同義詞的混亂和其他缺點，卻沒有意識到它們的好處。 改變這種做法以從公共同義詞中獲得真正的好處可以為團隊的開發工作流程帶來真正的好處。

數據庫訪問管理和變更管理流程

正如我們剛剛討論了大型團隊對並行開發的支持，值得討論一個單獨且經常被誤解的主題：變更控制流程。

變更管理通常成為一種由團隊領導和 DBA 控制的繁文縟節，被叛逆的開發人員所鄙視，他們想要交付所有東西，如果不是“昨天”，那麼“現在”。

作為一名 DBA，我總是在進入“我的”數據庫的過程中設置保護屏障。 我有一個很好的理由：數據庫是一種共享資源。

鳴叫

在源代碼控制上下文中，變更管理通常被接受，因為它允許團隊從新的但損壞的代碼恢復到舊的但可以工作的代碼。 但是在數據庫環境中，變更管理看起來像是 DBA 設置的一組不合理的障礙和限制：這純粹是瘋狂的，不必要地減慢了開發速度！

讓我們把這個開發人員的咆哮放在一邊：我是一名 DBA，我不會向自己扔石頭！ 作為一名 DBA，我總是在進入“我的”數據庫的過程中設置保護屏障。 我有一個很好的理由：數據庫是一種共享資源。

每個開發團隊——以及他們的每個開發人員——都有一個非常明確的目標和非常具體的可交付成果。 DBA 每天的唯一目標是數據庫作為共享資源的穩定性。 DBA 在組織中具有獨特的作用，可以監督所有團隊的所有開發工作，並控制所有開發人員訪問的數據庫。 是 DBA 確保所有項目和所有流程都在運行而不會相互干擾，並且每個都有運行所需的資源。

問題是當開發團隊和 DBA 團隊都被鎖在各自的象牙塔中時。

開發人員不知道，沒有訪問權限，甚至不關心數據庫上發生了什麼，只要數據庫運行得很好。 （這不是他們的可交付成果，也不會影響他們的績效評估。）

DBA 團隊將數據庫放在胸前，保護它免受“一無所知”的開發人員的影響，因為他們的團隊目標是數據庫穩定性。 確保穩定性的最佳方式是防止破壞性更改——通常會導致盡可能保護數據庫免受任何更改的態度。

正如我所見，這些對數據庫的相互衝突的態度會導致開發團隊和 DBA 團隊之間的敵意，並導致無法工作的環境。 但 DBA 和開發團隊必須共同努力以實現一個共同目標：交付業務解決方案，這也是他們最初走到一起的原因。

作為開發人員和 DBA 的兩邊，我知道當 DBA 更好地了解開發團隊的共同任務和目標時，問題很容易解決。 就他們而言，開發人員需要將數據庫視為一種共享資源，而不是抽象概念，而在那裡，DBA 應該扮演教育者的角色。

非開發人員 DBA 最常見的錯誤是限制開發人員訪問數據字典和代碼優化工具。 訪問 Oracle DBA_目錄視圖、動態V$視圖和SYS表對許多 DBA 來說似乎是“DBA 特權”，而事實上，這些都是關鍵的開發工具。

SQL Server 也是如此，但有一個複雜之處：不能直接授予對某些系統視圖的訪問權限，但它只是SYSADMIN數據庫角色的一部分，並且永遠不應在 DBA 團隊之外授予此角色。 這可以通過創建在SYSADMIN權限下執行但非 DBA 用戶可以訪問的視圖和存儲過程來解決（並且應該在項目從 Oracle 遷移到 SQL Server 的情況下解決）。 這是配置新的 SQL Server 開發環境時開發 DBA 的工作。

數據保護是 DBA 的主要職責之一。 儘管如此，開發團隊通常可以完全訪問未過濾的生產數據，以便進行與數據相關的故障單故障排除。 這些開發人員對數據結構的訪問權限有限——他們首先創建或為他們創建的結構。

當開發團隊和 DBA 團隊之間建立了適當的工作關係時，創建良好的變更控制流程就變得很直觀。 數據庫端變更管理的特點和挑戰同時是數據庫的剛性和流動性——結構是剛性的，數據是流動的。

經常發生的情況是，結構修改（即數據定義語言或 DDL）的變更管理已經很好地建立起來，而數據變更幾乎沒有變更管理的方式。 理由很簡單——數據一直在變化。

但如果我們更仔細地觀察這一點，我們會發現在任何系統中，所有數據都屬於以下兩類之一：應用程序數據和用戶數據。

應用程序數據是一個數據字典，它定義了應用程序的行為，並且對於它的進程和任何應用程序代碼一樣重要。 與任何其他應用程序更改一樣，對這些數據的更改應遵循嚴格的更改控制流程。 為了在應用程序數據更改的更改控製過程中創建透明度，應用程序數據和用戶數據應該明確分開。

在 Oracle 中，應該通過將應用程序和用戶數據分別放在自己的模式中來完成。 在 Microsoft SQL Server 中，應該通過將每個模式放入單獨的模式或（更好）放入單獨的數據庫來完成。 做出這些選擇應該是遷移計劃的一部分：Oracle 具有兩級名稱解析（架構/所有者 - 對象名稱），而 SQL Server 具有三級名稱解析（數據庫 - 架構/所有者 - 對象名稱）。

應用程序和用戶數據的正確分離使 DBA 團隊能夠解決其第二個最重要的問題：用戶數據安全。 由於用戶數據是分開存放的，因此根據需要實施用戶數據訪問的打破玻璃程序將非常簡單。

結論：變更控製過程在任何項目中都至關重要。 在軟件工程中，數據庫方面的變更管理經常被忽視，因為數據被認為“過於流動”。 但正是因為數據同時具有“流動性”和“持久性”，所以設計良好的變更控制流程應該是適當數據庫環境架構的基石。

關於代碼遷移工具的使用

標準的第一方工具 Oracle Migration Workbench 和 SQL Server Migration Assistant 在代碼遷移中很有幫助。 但需要考慮的是 80/20 規則：當代碼將被正確遷移 80% 時，解決剩餘的 20% 將花費 80% 的遷移工作。

迄今為止，使用遷移工具的最大風險是“銀彈”觀念。 人們可能會想，“它會完成這項工作，我只需要進行一些清理和整理。” 我觀察到一個項目由於轉換團隊及其技術領導的這種態度而失敗。

另一方面，使用 Notepad++ 的批量替換功能作為主要編輯工具，我花了四個工作日完成中型 Microsoft SQL Server 2008 系統（大約 200 個對象）的基本轉換。

到目前為止，我所討論的所有關鍵遷移元素都無法通過遷移工具來解決。

當然，請使用遷移輔助工具，但請記住，這些工具僅提供編輯幫助。 生成的輸出文本需要經過審查、修改，並且在某些情況下需要重寫以成為具有生產價值的代碼。

人工智能工具的開發可能會在未來解決這些遷移工具的缺陷，但我預計在此之前數據庫之間的差異會變得模糊，任何遷移過程本身都將變得不必要。 所以，只要需要這些類型的項目，我們就需要用老式的方式來做，使用老式的人類智能。

結論：使用遷移輔助工具是有幫助的，但它不是“靈丹妙藥”，任何轉換項目仍然需要對以上幾點進行詳細審查。

Oracle/SQL Server 遷移：始終密切關注

Oracle 和 Microsoft SQL Server 是企業環境中使用最多的兩個 RDBMS 平台。 兩者都基本符合 ANSI SQL 標準，並且只需很少的修改，甚至可以按原樣移動小段代碼。

這種相似性造成了一種欺騙性的印象，即跨兩個平台的遷移是一項簡單、直接的任務，並且可以輕鬆地從使用一個 RDBMS 後端到另一個應用程序採用相同的應用程序。

在實踐中，此類平台遷移絕非易事，必須考慮每個平台內部運作的精細元素，最重要的是，它們實現對數據管理最關鍵元素（交易）的支持的方式。

雖然我介紹了兩個 RDBMS 平台，它們是我專業的核心，但同樣的警告——“看起來相似並不意味著它工作相似”——應該應用於在任何其他符合 SQL 的數據庫管理系統之間移動代碼。 在所有情況下，首先要關注的是源平台和目標平台之間事務管理的實現有何不同。