如何利用数据字典视图获取两个数据库版本间的Schema变更？

Absolutely! You can totally leverage the all_tables and all_tab_columns data dictionary views to spot schema differences between Database_V1 and Database_V2. Let’s break down exactly how to pull out the changes you need—new tables, added columns, and data type modifications:

To find tables that exist only in V2 (i.e., they weren’t present in V1), compare the all_tables views from both databases. If you have cross-database access (via a database link, for example), use this query:

-- Tables unique to Database_V2
SELECT owner, table_name
FROM all_tables  -- This runs against V2
MINUS
SELECT owner, table_name
FROM all_tables@db_v1_link;  -- Replace with your link to V1

If you don’t have a database link, export the all_tables data from V1 into a temporary table in V2, then run a similar comparison using LEFT JOIN to find missing matches.

For tables that exist in both versions, we’ll split this into two checks: new columns added in V2, and data type changes for existing columns.

2.1 Find New Columns in V2 Tables #

This query returns columns that exist in V2 but not in V1 for tables present in both databases:

-- Columns added in V2
SELECT 
    tc2.owner,
    tc2.table_name,
    tc2.column_name,
    tc2.data_type AS v2_data_type
FROM all_tab_columns tc2  -- V2's column data
WHERE EXISTS (
    -- Only look at tables that existed in V1
    SELECT 1 
    FROM all_tables@db_v1_link t1
    WHERE t1.owner = tc2.owner AND t1.table_name = tc2.table_name
)
MINUS
SELECT 
    tc1.owner,
    tc1.table_name,
    tc1.column_name,
    tc1.data_type
FROM all_tab_columns tc1@db_v1_link;  -- V1's column data

2.2 Detect Data Type Modifications #

To catch columns that exist in both versions but have changed data types:

-- Data type changes between V1 and V2
SELECT 
    tc1.owner,
    tc1.table_name,
    tc1.column_name,
    tc1.data_type AS v1_data_type,
    tc2.data_type AS v2_data_type
FROM all_tab_columns tc1@db_v1_link  -- V1 columns
JOIN all_tab_columns tc2  -- V2 columns
    ON tc1.owner = tc2.owner
    AND tc1.table_name = tc2.table_name
    AND tc1.column_name = tc2.column_name
WHERE tc1.data_type != tc2.data_type;

• Permissions: Ensure you have SELECT access to all_tables and all_tab_columns (or use dba_tables/dba_tab_columns if you need visibility into all schemas).
• Case Sensitivity: If your database uses case-sensitive identifiers, wrap table/column names in double quotes (e.g., "MyTable") to avoid mismatches.
• Optional Extensions: If you want to track other changes (like nullable status or column length), add those columns to your comparison queries.
• Offline Comparison: If you can’t connect directly to V1, export the relevant dictionary data from V1 to CSV or a temporary table in V2, then run the same logic against that local data.

内容的提问来源于stack exchange，提问作者signup