Forum Stats

  • 3,770,583 Users
  • 2,253,137 Discussions
  • 7,875,499 Comments

Discussions

python query oracle db question

55138ad9-f0e9-4be9-b1fd-cad3ed8ad5ae
edited Apr 3, 2016 11:10PM in Python

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?




Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Apr 3, 2016 11:10PM Accepted 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.

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Apr 3, 2016 11:10PM Accepted 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.

This discussion has been closed.