| 你需要查询Oracle数据库中指定用户下的所有表及字段信息，同时获取PostgreSQL（PG）数据库中对应schema下的表和字段信息，并找出两者之间的表结构差异（包括表的存在性、字段名、字段类型、字段属性等）。 ### 步骤1：分别提取Oracle和PG的表字段信息（基础SQL） 先通过SQL分别导出两个数据库的表结构信息，为后续对比做准备。 #### 1.1 Oracle查询指定用户的表和字段 替换 `YOUR_ORACLE_USER` 为目标用户名，执行以下SQL： ```sql SELECT t.table_name, -- 表名 c.column_name, -- 字段名 c.data_type, -- 字段类型 c.data_length AS length, -- 字段长度 c.data_precision AS precision, -- 数值精度（仅数值类型） c.data_scale AS scale, -- 小数位数（仅数值类型） c.nullable, -- 是否可为空（Y/N） cc.comments AS column_comment -- 字段注释 FROM all_tables t JOIN all_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner LEFT JOIN all_col_comments cc ON c.owner = cc.owner AND c.table_name = cc.table_name AND c.column_name = cc.column_name WHERE t.owner = UPPER('YOUR_ORACLE_USER') -- 指定Oracle用户名（大写） ORDER BY t.table_name, c.column_id; ``` #### 1.2 PG查询指定schema的表和字段 替换 `YOUR_PG_SCHEMA` 为目标schema名（PG默认是public），执行以下SQL： ```sql SELECT c.table_name, -- 表名 c.column_name, -- 字段名 c.data_type, -- 字段类型 c.character_maximum_length AS length, -- 字符长度（仅字符类型） c.numeric_precision AS precision, -- 数值精度（仅数值类型） c.numeric_scale AS scale, -- 小数位数（仅数值类型） c.is_nullable, -- 是否可为空（YES/NO） col_description((c.table_schema || '.' || c.table_name)::regclass::oid, c.ordinal_position) AS column_comment -- 字段注释 FROM information_schema.columns c WHERE c.table_schema = LOWER('YOUR_PG_SCHEMA') -- 指定PG schema名（小写） ORDER BY c.table_name, c.ordinal_position; ``` ### 步骤2：自动化对比差异（Python脚本） 手动对比CSV文件效率低，推荐用Python脚本直接连接两个数据库，提取信息并输出差异。 #### 2.1 环境准备 先安装依赖库： ```bash pip install cx_Oracle psycopg2-binary pandas ``` #### 2.2 完整对比脚本 ```python import cx_Oracle import psycopg2 import pandas as pd # ===================== 配置数据库连接信息 ===================== # Oracle配置 ORACLE_CONFIG = { "user": "YOUR_ORACLE_USER", "password": "YOUR_ORACLE_PWD", "dsn": "ORACLE_HOST:ORACLE_PORT/ORACLE_SID", # 例如：127.0.0.1:1521/ORCL "encoding": "UTF-8" } # PG配置 PG_CONFIG = { "user": "YOUR_PG_USER", "password": "YOUR_PG_PWD", "host": "PG_HOST", "port": "PG_PORT", "database": "YOUR_PG_DB", "options": f"-c search_path=YOUR_PG_SCHEMA" # 指定PG schema } # ===================== 提取数据函数 ===================== def get_oracle_table_columns(oracle_config): """提取Oracle指定用户的表和字段信息""" conn = None try: conn = cx_Oracle.connect(**oracle_config) sql = """ SELECT t.table_name, c.column_name, c.data_type, c.data_length AS length, c.data_precision AS precision, c.data_scale AS scale, c.nullable, NVL(cc.comments, '') AS column_comment FROM all_tables t JOIN all_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner LEFT JOIN all_col_comments cc ON c.owner = cc.owner AND c.table_name = cc.table_name AND c.column_name = cc.column_name WHERE t.owner = UPPER(:user) ORDER BY t.table_name, c.column_id """ # 读取为DataFrame df = pd.read_sql(sql, conn, params={"user": oracle_config["user"]}) # 统一字段名和格式（方便对比） df["table_name"] = df["table_name"].str.upper() # Oracle表名默认大写 df["column_name"] = df["column_name"].str.upper() df["nullable"] = df["nullable"].map({"Y": "YES", "N": "NO"}) # 对齐PG的格式 return df except Exception as e: print(f"Oracle数据提取失败：{e}") raise finally: if conn: conn.close() def get_pg_table_columns(pg_config): """提取PG指定schema的表和字段信息""" conn = None try: conn = psycopg2.connect(**pg_config) sql = """ SELECT c.table_name, c.column_name, c.data_type, c.character_maximum_length AS length, c.numeric_precision AS precision, c.numeric_scale AS scale, c.is_nullable, COALESCE(col_description((c.table_schema || '.' || c.table_name)::regclass::oid, c.ordinal_position), '') AS column_comment FROM information_schema.columns c WHERE c.table_schema = current_schema() -- 使用配置的search_path ORDER BY c.table_name, c.ordinal_position """ # 读取为DataFrame df = pd.read_sql(sql, conn) # 统一字段名和格式（对齐Oracle） df["table_name"] = df["table_name"].str.upper() df["column_name"] = df["column_name"].str.upper() return df except Exception as e: print(f"PG数据提取失败：{e}") raise finally: if conn: conn.close() def compare_table_structure(oracle_df, pg_df): """对比Oracle和PG的表结构差异""" # 1. 获取表名集合 oracle_tables = set(oracle_df["table_name"].unique()) pg_tables = set(pg_df["table_name"].unique()) # 2. 输出表级差异 print("="*80) print("【表级差异】") only_oracle_tables = oracle_tables - pg_tables only_pg_tables = pg_tables - oracle_tables common_tables = oracle_tables & pg_tables print(f"仅Oracle存在的表：{sorted(only_oracle_tables)}") print(f"仅PG存在的表：{sorted(only_pg_tables)}") print(f"双方共有的表：{sorted(common_tables)}") # 3. 输出字段级差异（仅对比共有表） print("\n" + "="*80) print("【字段级差异（仅共有表）】") for table in sorted(common_tables): # 筛选当前表的字段 oracle_cols = oracle_df[oracle_df["table_name"] == table].copy() pg_cols = pg_df[pg_df["table_name"] == table].copy() # 设置字段名为索引，方便对比 oracle_cols = oracle_cols.set_index("column_name") pg_cols = pg_cols.set_index("column_name") # 字段名差异 oracle_col_names = set(oracle_cols.index) pg_col_names = set(pg_cols.index) only_oracle_cols = oracle_col_names - pg_col_names only_pg_cols = pg_col_names - oracle_col_names common_cols = oracle_col_names & pg_col_names if only_oracle_cols or only_pg_cols or common_cols: print(f"\n表 {table}：") if only_oracle_cols: print(f" - 仅Oracle存在的字段：{sorted(only_oracle_cols)}") if only_pg_cols: print(f" - 仅PG存在的字段：{sorted(only_pg_cols)}") # 对比共有字段的属性差异 for col in sorted(common_cols): ora_col = oracle_cols.loc[col] pg_col = pg_cols.loc[col] # 对比关键属性（类型、长度、是否为空、注释） diffs = [] if str(ora_col["data_type"]).upper() != str(pg_col["data_type"]).upper(): diffs.append(f"类型：Oracle={ora_col['data_type']}, PG={pg_col['data_type']}") if ora_col["length"] != pg_col["length"]: diffs.append(f"长度：Oracle={ora_col['length']}, PG={pg_col['length']}") if ora_col["nullable"] != pg_col["is_nullable"]: diffs.append(f"是否为空：Oracle={ora_col['nullable']}, PG={pg_col['is_nullable']}") if str(ora_col["column_comment"]).strip() != str(pg_col["column_comment"]).strip(): diffs.append(f"注释：Oracle='{ora_col['column_comment']}', PG='{pg_col['column_comment']}'") if diffs: print(f" - 字段 {col} 属性差异：{'; '.join(diffs)}") # ===================== 执行对比 ===================== if __name__ == "__main__": # 提取数据 oracle_df = get_oracle_table_columns(ORACLE_CONFIG) pg_df = get_pg_table_columns(PG_CONFIG) # 对比差异 compare_table_structure(oracle_df, pg_df) # 可选：将原始数据保存为CSV（方便后续核对） oracle_df.to_csv("oracle_table_structure.csv", index=False, encoding="utf-8-sig") pg_df.to_csv("pg_table_structure.csv", index=False, encoding="utf-8-sig") print("\n原始表结构已保存为：oracle_table_structure.csv 和 pg_table_structure.csv") ``` #### 2.3 脚本关键说明 1. **连接配置**：需要替换脚本中`ORACLE_CONFIG`和`PG_CONFIG`里的数据库账号、地址、端口、库名等信息。 2. **格式对齐**：将Oracle和PG的表名、字段名统一为大写，将“是否为空”的标识统一为YES/NO，避免因大小写或格式差异误判。 3. **差异输出**： - 表级：输出仅Oracle/PG存在的表、共有表。 - 字段级：对共有表，输出仅某侧存在的字段，以及共有字段的类型、长度、是否为空、注释差异。 4. **数据保存**：脚本会将两边的表结构保存为CSV文件，方便手动核对。 ### 总结 1. **核心步骤**：先通过SQL提取Oracle/PG的表字段信息，再通过Python脚本自动化对比表级和字段级差异（推荐），或手动对比CSV文件。 2. **关键注意点**：Oracle表名/字段名默认大写，PG默认小写，对比前需统一格式；两者数据类型体系不同（如Oracle VARCHAR2 vs PG VARCHAR），脚本仅对比原始类型，需结合业务判断类型映射是否合理。 3. **扩展建议**：若需要更细致的对比（如主键、索引、默认值），可在SQL中补充`all_constraints`（Oracle）、`information_schema.table_constraints`（PG）相关字段。 |
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
GMT+8, 2026-1-24 14:59 , Processed in 0.032059 second(s), 17 queries .
Powered by Mxzdjyxk! X3.5
© 2001-2025 Discuz! Team.
