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. 

---

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

---

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?