Python Forum
ORA-03113: end-of-file on communication channel Python Cx_Oracle
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
ORA-03113: end-of-file on communication channel Python Cx_Oracle
#1
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)

Reply
#2
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.
Reply
#3
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.
Unless noted otherwise, code in my posts should be understood as "coding suggestions", and its use may require more neurones than the two necessary for Ctrl-C/Ctrl-V.
Your one-stop place for all your GIMP needs: gimp-forum.net
Reply
#4
This might be unrelated, but why you create new cursors within the try block before closing the cursors you already have open?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  cx_oracle Error - AttributeError: 'function' object has no attribute 'cursor' birajdarmm 1 2,215 Apr-15-2023, 05:17 PM
Last Post: deanhystad
  how to check if someone send a message in a discord channel? Zerolysimin 1 742 Nov-06-2022, 11:10 AM
Last Post: Larz60+
  Output from Paramiko exec_command from channel pemca 0 2,519 Dec-03-2021, 12:29 PM
Last Post: pemca
  Create a 2-channel numpy file from two cvs file containing a 9x9 matrix silvialecc 1 1,616 Oct-26-2021, 07:59 AM
Last Post: Gribouillis
  Not able to add multiple channel IDs to python bot flaykez 3 71,600 Oct-18-2021, 01:26 AM
Last Post: CyKlop
  Good Example for bi-directional Communication between Python and Windows (C#) raybowman 0 1,555 Nov-14-2020, 06:44 PM
Last Post: raybowman
  Installing cx_Oracle 64-bit on AIX Nash 0 1,972 Dec-10-2019, 12:20 AM
Last Post: Nash
  packet radio communication EmpireAndrew 1 2,154 Nov-01-2019, 06:35 PM
Last Post: micseydel
  Auto detect threshold for skin detection w/ HSV channel davlovsky 1 2,170 Oct-13-2019, 01:45 AM
Last Post: Larz60+
  cx_Oracle.DatabaseError: ORA-12541: TNS:no listener ARV 2 10,928 Oct-03-2019, 12:53 PM
Last Post: ARV

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020