Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

python query oracle db question

55138ad9-f0e9-4be9-b1fd-cad3ed8ad5aeMar 20 2016 — edited Apr 3 2016

my DB is oracle 11.

develop language is python 2.7.

i use following statement to get record from DB. and the row number is nearly 0.5 billion

statements:

sql = "select count(*) from dwrsdm.bl_inv_stock_erp "

cursor.execute(sql)

result = cursor.fetchall()

print result;

sql = "select * from dwrsdm.bl_inv_stock_erp"

cursor.execute(sql)

result = cursor.fetchall() -------error happen



error happen when it run the second " cursor.fetchall()", it prompt

"cx_Oracle.OperationalError: ORA-03113: "

I am sure the error happen for the return records is too huge. How can I solve the problem?
This post has been answered by Gaz in Oz on Apr 3 2016
Jump to Answer

Comments

Gaz in Oz
Answer

Hi 55138ad9-f0e9-4be9-b1fd-cad3ed8ad5ae,

11gR2 SQL> oerr ORA-03113

03113, 00000, "end-of-file on communication channel"

// *Cause: The connection between Client and Server process was broken.

// *Action: There was a communication error that requires further investigation.

//          First, check for network problems and review the SQL*Net setup.

//          Also, look in the alert.log file for any errors. Finally, test to

//          see whether the server process is dead and whether a trace file

//          was generated at failure time.

...so check db alert log and trace files etc as suggested.

Reason for -3113 is many and varied, might be as simple as exceeding some resource limit, like memory, for instance, breaking the python.

One point to consider, why are you trying to manipulate 0.5billion records in python? I am sure there is a better solution to what ever your end goal might be.

Why not share what you are trying to do, and show your code.

If you simply have to use python, then use array fetch logic and deal with X records at a time. Note, X will be a number you have tested and are happy with, with respect to performance, load, etc. Start with, say 500 as your initial array size and see how you go, processing, say 1million rows. increase your recordset to see if your new solution is scalable.

Cheers,

Gaz.

Marked as Answer by 55138ad9-f0e9-4be9-b1fd-cad3ed8ad5ae · Sep 27 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 1 2016
Added on Mar 20 2016
#python
1 comment
1,748 views