Python Forum

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)

---

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.

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.

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