Oracle
Oracle：比較兩個數據庫中模式的所有對象的簡單方法？
我正在嘗試比較位於兩個不同數據庫中的兩個模式的所有對象。我知道使用 TOAD 和 SQL Developer 可能有辦法做到這一點，但還有其他方法可以通過腳本或 Sql*Plus 做到這一點嗎？
提前致謝。
最簡單的方法是創建一個數據庫連結並使用 all_tables/indexes/objects/tab_colums 等視圖使用數據庫連結進行查詢。
請注意，可能存在很多差異，例如缺少表、索引、列、過程等。因此，您的案例必須告訴您要比較的確切內容。
可以從所有 _objects 中提取全域級別。這可以報告失去的對象和所有權的差異。
對於表，您可以從 all_tables 獲得更高級別的詳細資訊，您可以在其中找到類型和表空間分配，然後是 all_tab_columns，您可以在其中報告缺少的列和列定義的差異。如果分區起作用，您可以嘗試比較所有 _tab_partitions 甚至子分區。
對於儲存的程式碼比較 all_source 可能是一個好方法。
對於索引檢查 all_ind_columns，您可以在其中報告失去的列、順序和排序的差異。在 all_indexes 中是索引類型和表空間分配。
不要忘記贈款。缺少授權可以在使用儲存程式碼的多模式應用程序中發揮重要作用。
一種完全不同的方法可能是為此使用 dbms_metadata.get_ddl 並比較檢索到的 ddl。有很多選項可以控制輸出，與手動使用元數據相比，最終可能需要做更多的工作才能做到這一點。特別是在使用不同版本時。
所以，是的，可以使用 sqlplus 來完成。有很多可用的元數據可供使用，不需要魔法。從簡單開始。
我發現 Oracle 有一個dbms_metadata_diff包。這是一個如何使用它的範例。沒試過不知道有沒有用
這是上面頁面中的範例：
比較對像元數據
- 創建兩個表，TAB1 和 TAB2：
SQL> CREATE TABLE TAB1 2 ( "EMPNO" NUMBER(4,0), 3 "ENAME" VARCHAR2(10), 4 "JOB" VARCHAR2(9), 5 "DEPTNO" NUMBER(2,0) 6 ) ;表已創建。
SQL> CREATE TABLE TAB2 2 ( "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE, 3 "ENAME" VARCHAR2(20), 4 "MGR" NUMBER(4,0), 5 "DEPTNO" NUMBER(2,0) 6 ) ;表已創建。
注意 TAB1 和 TAB2 之間的區別：
- 表名不同
- TAB2 有一個主鍵約束；TAB1 沒有
- 每個表中 ENAME 列的長度不同
- TAB1 有一個 JOB 列；TAB2 沒有
- TAB2 有一個 MGR 列；TAB1 沒有
創建一個函式以 SXML 格式返回表元數據。以下是使用 DBMS_METADATA_DIFF API 時要牢記的有關 SXML 的一些關鍵點：
- SXML 是對像元數據的 XML 表示。
- 返回的 SXML 與 DBMS_METADATA.GET_XML 返回的 XML 不同，後者復雜且不透明，包含二進制值、特定於實例的值等。
- SXML 看起來像是將 SQL 創建 DDL 直接轉換為 XML。標籤名稱和結構與 Oracle 數據庫 SQL 語言參考中的名稱相對應。
- SXML 旨在支持編輯和比較。
為了使這個例子簡單，使用一個變換參數來抑制物理屬性：
SQL> CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS 2 open_handle NUMBER; 3 transform_handle NUMBER; 4 doc CLOB; 5 BEGIN 6 open_handle := DBMS_METADATA.OPEN('TABLE'); 7 DBMS_METADATA.SET_FILTER(open_handle,'NAME',name); 8 -- 9 -- Use the 'SXML' transform to convert XML to SXML 10 -- 11 transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML'); 12 -- 13 -- Use this transform parameter to suppress physical properties 14 -- 15 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES', 16 FALSE); 17 doc := DBMS_METADATA.FETCH_CLOB(open_handle); 18 DBMS_METADATA.CLOSE(open_handle); 19 RETURN doc; 20 END; 21 /創建的函式。
使用 get_table_sxml 函式獲取兩個表的表 SXML：
SQL> SELECT get_table_sxml('TAB1') FROM dual; <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <SCHEMA>SCOTT</SCHEMA> <NAME>TAB1</NAME> <RELATIONAL_TABLE> <COL_LIST> <COL_LIST_ITEM> <NAME>EMPNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>4</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>ENAME</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH>10</LENGTH> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>JOB</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH>9</LENGTH> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>DEPTNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>2</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> </COL_LIST> </RELATIONAL_TABLE> </TABLE>已選擇 1 行。
SQL> SELECT get_table_sxml('TAB2') FROM dual; <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <SCHEMA>SCOTT</SCHEMA> <NAME>TAB2</NAME> <RELATIONAL_TABLE> <COL_LIST> <COL_LIST_ITEM> <NAME>EMPNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>4</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>ENAME</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH>20</LENGTH> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>MGR</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>4</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>DEPTNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>2</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> </COL_LIST> <PRIMARY_KEY_CONSTRAINT_LIST> <PRIMARY_KEY_CONSTRAINT_LIST_ITEM> <COL_LIST> <COL_LIST_ITEM> <NAME>EMPNO</NAME> </COL_LIST_ITEM> </COL_LIST> </PRIMARY_KEY_CONSTRAINT_LIST_ITEM> </PRIMARY_KEY_CONSTRAINT_LIST> </RELATIONAL_TABLE> </TABLE>已選擇 1 行。
使用 DBMS_METADATA 瀏覽 API 比較結果：
SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB1') FROM dual; SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB2') FROM dual;使用 DBMS_METADATA_DIFF API 創建一個函式來比較兩個表的元數據。在該函式中，使用了剛剛在步驟 2 中定義的 get_table_sxml 函式。
SQL> CREATE OR REPLACE FUNCTION compare_table_sxml(name1 IN VARCHAR2, 2 name2 IN VARCHAR2) RETURN CLOB IS 3 doc1 CLOB; 4 doc2 CLOB; 5 diffdoc CLOB; 6 openc_handle NUMBER; 7 BEGIN 8 -- 9 -- Fetch the SXML for the two tables 10 -- 11 doc1 := get_table_sxml(name1); 12 doc2 := get_table_sxml(name2); 13 -- 14 -- Specify the object type in the OPENC call 15 -- 16 openc_handle := DBMS_METADATA_DIFF.OPENC('TABLE'); 17 -- 18 -- Add each document 19 -- 20 DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc1); 21 DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc2); 22 -- 23 -- Fetch the SXML difference document 24 -- 25 diffdoc := DBMS_METADATA_DIFF.FETCH_CLOB(openc_handle); 26 DBMS_METADATA_DIFF.CLOSE(openc_handle); 27 RETURN diffdoc; 28 END; 29 /創建的函式。
使用該函式獲取兩個表的 SXML 差異文件：
SQL> SELECT compare_table_sxml('TAB1','TAB2') FROM dual; <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <SCHEMA>SCOTT</SCHEMA> <NAME value1="TAB1">TAB2</NAME> <RELATIONAL_TABLE> <COL_LIST> <COL_LIST_ITEM> <NAME>EMPNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>4</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>ENAME</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH value1="10">20</LENGTH> </COL_LIST_ITEM> <COL_LIST_ITEM src="1"> <NAME>JOB</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH>9</LENGTH> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>DEPTNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>2</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> <COL_LIST_ITEM src="2"> <NAME>MGR</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>4</PRECISION> <SCALE>0</SCALE> </COL_LIST_ITEM> </COL_LIST> <PRIMARY_KEY_CONSTRAINT_LIST src="2"> <PRIMARY_KEY_CONSTRAINT_LIST_ITEM> <COL_LIST> <COL_LIST_ITEM> <NAME>EMPNO</NAME> </COL_LIST_ITEM> </COL_LIST> </PRIMARY_KEY_CONSTRAINT_LIST_ITEM> </PRIMARY_KEY_CONSTRAINT_LIST> </RELATIONAL_TABLE> </TABLE>已選擇 1 行。
SXML 差異文件顯示兩個 SXML 文件的聯合，XML 屬性 value1 和 src 標識差異。當一個元素只存在於一個文件中時，它會被標記為 src。因此，<COL_LIST_ITEM src=“1”> 意味著該元素在第一個文件 (TAB1) 中，但不在第二個文件中。當兩個文件中都存在一個元素但具有不同的值時，該元素的值是第二個文件中的值，而 value1 給出了第一個文件中的值。例如，20 表示在 TAB1（第一個文件）中長度為 10，在 TAB2 中長度為 20。
使用 DBMS_METADATA_DIFF 瀏覽 API 比較結果：
SQL> SELECT dbms_metadata_diff.compare_sxml('TABLE','TAB1','TAB2') FROM dual;使用 DBMS_METADATA.CONVERT API 創建函式以生成 ALTERXML 文件。這是一個包含 ALTER 語句的 XML 文件，用於使一個對象與另一個對象相似。您還可以使用解析項來獲取有關各個 ALTER 語句的資訊。（此範例使用迄今為止定義的函式。）
SQL> CREATE OR REPLACE FUNCTION get_table_alterxml(name1 IN VARCHAR2, 2 name2 IN VARCHAR2) RETURN CLOB IS 3 diffdoc CLOB; 4 openw_handle NUMBER; 5 transform_handle NUMBER; 6 alterxml CLOB; 7 BEGIN 8 -- 9 -- Use the function just defined to get the difference document 10 -- 11 diffdoc := compare_table_sxml(name1,name2); 12 -- 13 -- Specify the object type in the OPENW call 14 -- 15 openw_handle := DBMS_METADATA.OPENW('TABLE'); 16 -- 17 -- Use the ALTERXML transform to generate the ALTER_XML document 18 -- 19 transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERXML'); 20 -- 21 -- Request parse items 22 -- 23 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'CLAUSE_TYPE'); 24 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'NAME'); 25 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'COLUMN_ATTRIBUTE'); 26 -- 27 -- Create a temporary LOB 28 -- 29 DBMS_LOB.CREATETEMPORARY(alterxml, TRUE ); 30 -- 31 -- Call CONVERT to do the transform 32 -- 33 DBMS_METADATA.CONVERT(openw_handle,diffdoc,alterxml); 34 -- 35 -- Close context and return the result 36 -- 37 DBMS_METADATA.CLOSE(openw_handle); 38 RETURN alterxml; 39 END; 40 /創建的函式。
使用該函式獲取 ALTER_XML 文件：
SQL> SELECT get_table_alterxml('TAB1','TAB2') FROM dual; <ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0"> <OBJECT_TYPE>TABLE</OBJECT_TYPE> <OBJECT1> <SCHEMA>SCOTT</SCHEMA> <NAME>TAB1</NAME> </OBJECT1> <OBJECT2> <SCHEMA>SCOTT</SCHEMA> <NAME>TAB2</NAME> </OBJECT2> <ALTER_LIST> <ALTER_LIST_ITEM> <PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>NAME</ITEM> <VALUE>MGR</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>ADD_COLUMN</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST> <SQL_LIST> <SQL_LIST_ITEM> <TEXT>ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))</TEXT> </SQL_LIST_ITEM> </SQL_LIST> </ALTER_LIST_ITEM> <ALTER_LIST_ITEM> <PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>NAME</ITEM> <VALUE>JOB</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>DROP_COLUMN</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST> <SQL_LIST> <SQL_LIST_ITEM> <TEXT>ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")</TEXT> </SQL_LIST_ITEM> </SQL_LIST> </ALTER_LIST_ITEM> <ALTER_LIST_ITEM> <PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>NAME</ITEM> <VALUE>ENAME</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>MODIFY_COLUMN</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>COLUMN_ATTRIBUTE</ITEM> <VALUE> SIZE_INCREASE</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST> <SQL_LIST> <SQL_LIST_ITEM> <TEXT>ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20)) </TEXT> </SQL_LIST_ITEM> </SQL_LIST> </ALTER_LIST_ITEM> <ALTER_LIST_ITEM> <PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>ADD_CONSTRAINT</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST> <SQL_LIST> <SQL_LIST_ITEM> <TEXT>ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY ("EMPNO") ENABLE </TEXT> </SQL_LIST_ITEM> </SQL_LIST> </ALTER_LIST_ITEM> <ALTER_LIST_ITEM> <PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>NAME</ITEM> <VALUE>TAB1</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>RENAME_TABLE</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST> <SQL_LIST> <SQL_LIST_ITEM> <TEXT>ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"</TEXT> </SQL_LIST_ITEM> </SQL_LIST> </ALTER_LIST_ITEM> </ALTER_LIST> </ALTER_XML>已選擇 1 行。
使用 DBMS_METADATA_DIFF 瀏覽 API 比較結果：
SQL> SELECT dbms_metadata_diff.compare_alter_xml('TABLE','TAB1','TAB2') FROM dual;ALTER_XML 文件包含每個變更的 ALTER_LIST。每個 ALTER_LIST_ITEM 都有一個 PARSE_LIST 包含作為名稱-值對的解析項和一個 SQL_LIST 包含特定變更的 SQL。您可以使用 PARSE_LIST 中的資訊解析此文件並決定執行哪些 SQL 語句。（例如，請注意，在這種情況下，其中一個更改是 DROP_COLUMN，您可能會選擇不執行它。）
創建最後一個函式，該函式使用 DBMS_METADATA.CONVERT API 和 ALTER DDL 轉換將 ALTER_XML 文件轉換為 SQL DDL：
SQL> CREATE OR REPLACE FUNCTION get_table_alterddl(name1 IN VARCHAR2, 2 name2 IN VARCHAR2) RETURN CLOB IS 3 alterxml CLOB; 4 openw_handle NUMBER; 5 transform_handle NUMBER; 6 alterddl CLOB; 7 BEGIN 8 -- 9 -- Use the function just defined to get the ALTER_XML document 10 -- 11 alterxml := get_table_alterxml(name1,name2); 12 -- 13 -- Specify the object type in the OPENW call 14 -- 15 openw_handle := DBMS_METADATA.OPENW('TABLE'); 16 -- 17 -- Use ALTERDDL transform to convert the ALTER_XML document to SQL DDL 18 -- 19 transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERDDL'); 20 -- 21 -- Use the SQLTERMINATOR transform parameter to append a terminator 22 -- to each SQL statement 23 -- 24 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'SQLTERMINATOR',true); 25 -- 26 -- Create a temporary lob 27 -- 28 DBMS_LOB.CREATETEMPORARY(alterddl, TRUE ); 29 -- 30 -- Call CONVERT to do the transform 31 -- 32 DBMS_METADATA.CONVERT(openw_handle,alterxml,alterddl); 33 -- 34 -- Close context and return the result 35 -- 36 DBMS_METADATA.CLOSE(openw_handle); 37 RETURN alterddl; 38 END; 39 /創建的函式。
使用該函式獲取 SQL ALTER 語句：
SQL> SELECT get_table_alterddl('TAB1','TAB2') FROM dual; ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0)) / ALTER TABLE "SCOTT"."TAB1" DROP ("JOB") / ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20)) / ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY ("EMPNO") ENABLE / ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2" /已選擇 1 行。
使用 DBMS_METADATA_DIFF 瀏覽 API 比較結果：
SQL> SELECT dbms_metadata_diff.compare_alter('TABLE','TAB1','TAB2') FROM dual; ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0)) ALTER TABLE "SCOTT"."TAB1" DROP ("JOB") ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20)) ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ENABLE ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"已選擇 1 行。