使用python实现oracle同步小工具

斗起来

1、在需要同步的数据库创建需要的dblink
-- Create database link
create public database link IMPDP_JAC_TEST_20
  connect to test identified by "test123"
  using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.20)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAIL_OVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jac)
    )
)';
-- Create database link
create public database link IMPDP_JAC_TEST_31
  connect to test identified by "test123"
  using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.35)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAIL_OVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
)';


以下为python脚本
import paramiko
import time
print('---------------------------------------------')
print('---------------------------------------------')
print('此工具为同步数据库小程序,有问题联系：斗起来')
print('---------------------------------------------')
print('---------------------------------------------')
imptype = int(input('请选择同步方式 1，test整库同步  2，按表同步：',))
expip = input('请输入源端数据库ip：仅输入最后的ip即可  例：192.168.100.20  输入 20 即可:  ',)
impip = input('请输入目标端数据库ip：仅输入最后的ip即可  例：192.168.100.35  输入 35 即可:  ',)
if imptype==1:
cmd='impdp test/test123 SCHEMAS=test directory=DATA_PUMP_DIR network_link=impdp_jac_test_'+expip+' logfile=imp.log TABLE_EXISTS_ACTION=REPLACE'
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect('192.168.100.'+impip, 22, username='oracle', password='oracle', timeout=4)
stdin, stdout, stderr = client.exec_command('source /home/oracle/.bash_profile&&'+cmd)
for std in stdout.read(),stderr.read():
   print(std)
   client.close()
   print('---------------------------------------')
   print('---------------------------------------')
   print('谢谢使用！！！')
   print('---------------------------------------')
   print('---------------------------------------')
   time.sleep(10)
elif imptype==2:
tables=input('请输入需要同步的表名：输入格式为:如果一个表只输入表名即可，如果多个表以逗号分隔 例：tab1,tab2 ',)   
cmd='impdp test/test123 tables='+tables+' directory=DATA_PUMP_DIR network_link=impdp_jac_test_'+expip+' logfile=imp.log TABLE_EXISTS_ACTION=REPLACE'   
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect('192.168.100.'+impip, 22, username='oracle', password='oracle', timeout=4)
stdin, stdout, stderr = client.exec_command('source /home/oracle/.bash_profile&&'+cmd)
#stdin, stdout, stderr = client.exec_command('echo '+cmd+'>/home/oracle/imp.sh')
#time.sleep(5)
#stdin, stdout, stderr = client.exec_command('source /home/oracle/.bash_profile&&sh /home/oracle/imp.sh')
#print(stderr)
for std in stdout.read(),stderr.read():
   print(std)
client.close()
print('---------------------------------------')
print('---------------------------------------')
print('谢谢使用！！！')
print('---------------------------------------')
print('---------------------------------------')
time.sleep(10)
else:
  print('---------------------------------------')
  print('---------------------------------------')
  print('谢谢使用！！！')
  print('---------------------------------------')
  print('---------------------------------------')
  time.sleep(10)