从这样的东西开始，并根据您的要求进行更改：

import csv
import os
import getpass

import oracledb

un = os.environ.get('PYTHON_USERNAME')
cs = os.environ.get('PYTHON_CONNECTSTRING')
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

connection = oracledb.connect(user=un, password=pw, dsn=cs)

sql = 'select * from employees'

with connection.cursor() as cursor:
    cursor.arraysize = 5000
    with open("output.csv", "w", encoding='utf-8') as outputfile:
        writer = csv.writer(outputfile, lineterminator="\n")
        results = cursor.execute(sql)
        writer.writerows(results)

特别是，您需要调整arraysize值。
参见Tuning python-oracledb。