Below is a script I wrote to copy a set of tables from one schema (or database) to another. <\/p>\n
It simply takes every table in the source schema and builds up an array based copy for it, with the usage of bind variables and bulk inserting. It assumes the target table structures have already been built, but without data. It also doesn’t handle constraint violations, although that shouldn’t be too difficult to implement.<\/p>\n
\r\nimport cx_Oracle, sys\r\n\r\n#-------------------------------------------------------------------------------------------\r\n\"\"\"\r\n#Author:\tSteve Howard\r\n#Date:\t\tJanuary 30, 2011\r\n#Purpose:\tSimple array based row copier. It works best for smaller row sets (less\r\n\t\tthan one million rows per table), as it reads an entire resultset into\r\n\t\tmemory, inserts it into the target table, then commits and releases the\r\n\t\tmemory.\r\n\t\t\r\n\t\tA possible enhancement to this design is to periodically perform a bulk\r\n\t\tinsert into the target once the scan of the source table gets to a\r\n\t\tspecified number of rows or size, and then clear the array and start\r\n\t\treloading again. This shouldn't be too difficult to implement, actually.\r\n\t\t\r\n\t\tAdded what is above, so it should scale better.\r\n\"\"\"\r\n#------------------------------------------------------------------------------------------\r\n\r\nsource = cx_Oracle.connect(\"rep\",\"rep\")\r\nselect = source.cursor()\r\nthistable = source.cursor()\r\n\r\nselect.execute(\"select table_name from user_tables\")\r\ntarget = cx_Oracle.connect(\"rep\",\"rep\")\r\ninsert = target.cursor()\r\n\r\nfound = False\r\nthisrow=[]\r\nfor table in select:\r\n rows = []\r\n s = \"\"\r\n i = -1\r\n try:\r\n thistable.execute(\"select * from \" + table[0])\r\n colcount = len(thistable.description)\r\n for col in range(colcount):\r\n i = i + 1\r\n s = s + \":b\" + str(i) + \",\"\r\n s = s[0:len(s) - 1]\r\n found = False\r\n j = 0\r\n for row in thistable:\r\n j = j + 1\r\n # we have at least one row to copy\r\n found = True\r\n for col in range(colcount):\r\n thisrow.append(row[col])\r\n rows.append(thisrow)\r\n thisrow=[]\r\n if j % 1000 == 0:\r\n insert.prepare(\"insert into \" + table[0] + \" values(\" + s + \")\")\r\n insert.executemany(None, rows)\r\n target.commit()\r\n rows = []\r\n\r\n except:\r\n print str(sys.exc_info()[1])\r\n #only copy if we actually found data\r\n if found == True:\r\n insert.prepare(\"insert into \" + table[0] + \" values(\" + s + \")\")\r\n insert.executemany(None, rows)\r\n target.commit()\r\n\r\nselect.close()\r\nsource.close()\r\ntarget.close()\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Below is a script I wrote to copy a set of tables from one schema (or database) to another. It simply takes every table in the source schema and builds up an array based copy for it, with the usage…<\/p>\n