Python cx_Oracle读取Oracle数据时遇ORA-12847错误求助

核心原因 #

ORA-12847是因为查询游标在执行过程中，涉及的数据库对象被其他会话执行了DDL操作（如ALTER TABLE、CREATE INDEX等），导致Oracle需要重新解析游标但失败。由于你用fetchall()一次性获取1000万行数据，过程耗时较长，遇到并发DDL的概率更高。

可行解决方案 #

1. 捕获异常并自动重试 #

针对偶发的并发DDL场景，捕获错误后设置合理重试次数重新执行查询：

import time
import cx_Oracle

max_retries = 3
retry_count = 0
data_list = []
data_columns = []

while retry_count < max_retries:
    try:
        with connection.cursor() as cursor:
            cursor.arraysize = 250000
            cursor.execute(query)
            data_list = cursor.fetchall()
            data_columns = [x[0] for x in cursor.description]
        print(f"I got {len(data_list)} lines ")
        break
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        if error.code == 12847:
            retry_count += 1
            print(f"遇到并发DDL，正在重试第{retry_count}次...")
            time.sleep(1)
        else:
            raise
else:
    raise Exception(f"重试{max_retries}次后仍无法获取数据，请检查数据库并发DDL操作")

2. 分批获取数据，缩短暴露窗口 #

将fetchall()改为fetchmany()循环分批获取，减少单次数据获取的耗时，降低冲突概率：

batch_size = 250000
data_list = []

with connection.cursor() as cursor:
    cursor.arraysize = batch_size
    cursor.execute(query)
    while True:
        batch = cursor.fetchmany(batch_size)
        if not batch:
            break
        data_list.extend(batch)
    data_columns = [x[0] for x in cursor.description]

print(f"I got {len(data_list)} lines ")

3. 锁定查询涉及的对象（谨慎使用） #

如果业务允许，查询前锁定相关表防止期间DDL操作，但会影响其他会话的DDL执行，需评估业务影响：

with connection.cursor() as cursor:
    # 替换为实际涉及的表名
    cursor.execute("LOCK TABLE table1, table2 IN SHARE MODE NOWAIT")
    cursor.arraysize = 250000
    cursor.execute(query)
    data_list = cursor.fetchall()
    data_columns = [x[0] for x in cursor.description]
print(f"I got {len(data_list)} lines ")

4. 协调DDL执行时间 #

排查数据库中执行DDL的任务，调整到业务低峰期执行，避开数据查询时间段，从根源减少并发冲突。

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