用python查询oracle的dmp文件的编码、全自动导入多个dmp文件。

import cx_Oracle
import os
os.environ["PATH"] += 'D:\\instantclient_19_6'#Oracle客户端目录
fname = '数据文件.dmp'
f = open(fname,'rb')
f.seek(1)
t1 = f.read(1)
t2 = f.read(1)
f.close()
conn = cx_Oracle.Connection("abc/123@//localhost:1521/orcl",encoding = "UTF-8", nencoding = "UTF-8")
curser = conn.cursor()
data = curser.execute("select nls_charset_name(to_number('{}{}','xxxx')) from dual".format(hex(ord(t1)).replace('0x',''),hex(ord(t2)).replace('0x','')))
row = data.fetchall()
print(row)
curser.execute("CREATE OR REPLACE DIRECTORY DMPDIR AS 'D:\dump'")#这个是为使用数据泵导入dmp准备的
curser.close()

全自动导入dmp文件到本地oracle数据库（localhost）

#自动导dmp.py
import subprocess
import os
import re

本地数据库实例名 = input('本地数据库实例名：')
用户名 = input('用户名：')
密码 = input('密码：')
input("请将要导入到该数据库的dmp文件放在d盘根目录的dump文件夹中，然后按回车开始自动导入")

flist = os.listdir('D:\\dump')
faill=[]
for fname in flist:
    if fname[-4:]=='.dmp':
        print('==============={}：开始导入=================='.format(fname))
        #先尝试普通导入
        s = subprocess.Popen('imp {}/{}@localhost/{} file=D:\\dump\\{} full=y ignore=y'.format(用户名,密码,本地数据库实例名,fname),stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,shell=True)
        vout,err = s.communicate()
        print(err.decode('gbk'))
        #再尝试数据泵
        s1 = ''
        s2 = ''
        n=0
        while '导入了' not in err.decode('gbk'):
            if n>2:
                faill.append(fname)
                print('==============={}：导入失败=================='.format(fname))
                break
            if l := re.findall("表空间 '[A-Za-z0-9]+' 不存在",err.decode('gbk')):
                s1 += 'REMAP_TABLESPACE = '
                for i in set(l):
                    s1 += '{}:{} '.format(i[5:-5],'USERS')
            if l := re.findall("用户 '[A-Za-z0-9]+' 不存在",err.decode('gbk')):
                s2 += 'REMAP_SCHEMA = '
                for i in set(l):
                    s2 += '{}:{} '.format(i[4:-5],用户名)
            s = subprocess.Popen('impdp {}/{}@localhost/{} {}table_exists_action = replace DIRECTORY=DMPDIR DUMPFILE={} full=y'.format(用户名,密码,本地数据库实例名,s1+s2,fname),stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,shell=True)
            vout,err = s.communicate()
            n+=1
            print(err.decode('gbk'))


input('================完成===============\n以下文件未成功导入{}'.format(faill))

最后编辑于：2020.08.20 18:54:33