ORA-03113: end-of-file on communication channel Python Cx_Oracle

[akansha]

Error: ORA-03113: end-of-file on communication channel
 
I am trying to migrate data between two oracle databases using python but i end up getting this error for table with records above 100000.
I am establishing connection between database and python code using cx_Oracle.SessionPool. 

---

1 2 3 4 5 6 7 8 9

dsn_tns = cx_Oracle.makedsn(localhost, 7845, orcl) pool4 = cx_Oracle.SessionPool(user= 'pythonhol' ,password= 'welcome',dsn= dsn_tns ,min=1,max=2,increment=1)  con4 = pool4.acquire() cur4 = con4.cursor()    dsn_tns = cx_Oracle.makedsn(localhost, 4878, orcl) pool5 = cx_Oracle.SessionPool(user= 'pythonhol',password= 'thankyou',dsn= dsn_tns ,min=1,max=2,increment=1)  con5 = pool5.acquire() cur5 = con5.cursor()

---

 
My code for migrating data keep giving this error.

Error:
“Error: ORA-03113: end-of-file on communication channel”

If the table records are more than 100000

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

if (total_count > 100000):         for i in range(1,1001):                 cur4,con4,cur5,con5,pool4,pool5 = conn('pythonhol')                 qwe = "SELECT EMP_NAME,DEPT_ID,ID,DOJ  FROM (select ntile(1000) over (order by rowid)  as tile_partition from EMPLOYEE) where tile_partition =:i"                 cur4.execute(qwe, {'i' : i})                 res = cur4.fetchall()                 count_res = cur4.rowcount                 print count_res                 elapsed1 = (time.time() - start)                 print "Time taken to fetch"                 print elapsed1, "seconds"                                   statement ='insert into EMPLOYEE( EMP_NAME, DEPT_ID, ID, DOJ ) values (:1,:2,:3,:4)'                 try:                                           cur4,con4,cur5,con5,pool4,pool5 = conn('pythonhol')                         cur5.executemany(statement, res)                         con5.commit()                         cur5.close()                         cur4.close()                         pool4.release(con4)                         pool5.release(con5)                           elapsed = (time.time() - start)                         print elapsed, "seconds"                 except Exception as err:                         print("Error: {0}".format(err))                         cur4,con4,cur5,con5,pool4,pool5 = conn('pythonhol')                         error = "insert into migr_tbl_error(table_name,owner, error, batch_number) values('EMPLOYEE', 'pythonhol', '"+str(err)+"', "+str(i)+")"                         cur5.execute(error)                         con5.commit()                         cur5.close()                         cur4.close()                         pool4.release(con4)                         pool5.release(con5)

---

[micseydel]

This seems like more of an Oracle question than a Python question. I wish we could help, but you should probably look for Oracle resources.

[Ofnuts]

I don't know the particulars but here is a way in Oracle to get the results of big requests in "chunks" (Google that). this may be a good start.

It is also useful to insert your data in appropriately sized chunks. Too small and you waste a lot of time with the request overhead, too large and the DB needs to manage huge buffers to keep everything around until you commit.

[nilamo]

This might be unrelated, but why you create new cursors within the try block before closing the cursors you already have open?