使用ant执行sql脚本重建oracle数据库

勤1160

最近工作中，需要把之前的产品（基于mysql数据库的）转换到oracle环境中，利用oracle自己提供的一个工具sqldeveloper（有时间的话，把这个工具如何把mysql转换到oracle，也整理出来）,里面的migration功能，可以把mysql的表结构，数据都导出成oracle的格式，对于生成的sql脚本，为了便于开发人员，持续构建，我写成了ant文件，供大家使用。

<?xml version="1.0" encoding="UTF-8"?>
<project name="mysql2oracle" basedir="." default="all">
<property file="dev.properties" />
<property name="jdbc.driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="jdbc.url" value="jdbc:oracle:thin:@192.168.0.8:1521:A8" />
<property name="jdbc.system.userid" value="system" />
<property name="jdbc.system.password" value="aljoin" />
<target name="rebuild.schema" description="create all tables in database">
<sql driver="${jdbc.driver}" password="${jdbc.password}" url="${jdbc.url}" userid="${jdbc.userid}" encoding="utf8" print="no">
<transaction src="script/2.12/createTable.sql" />
<transaction src="script/2.12/createSequence.sql" />
<transaction src="script/2.12/createPrimaryKey.sql" />
<transaction src="script/2.12/createForeignKey.sql" />
<transaction src="script/2.12/createIndex.sql" />
<transaction src="script/2.12/InitData.sql" />
<classpath path="lib/classes12.jar" />
</sql>
</target>
<target name="all" depends="rebuild.user,rebuild.schema"/>
<!-- =================================
target: rebuild.user              
================================= -->
<target name="drop.user">
<sql driver="${jdbc.driver}" password="${jdbc.system.password}" url="${jdbc.url}" userid="${jdbc.system.userid}" encoding="utf8" onerror="continue">
<transaction>
DROP USER ${jdbc.userid} CASCADE;
</transaction>
<classpath path="lib/classes12.jar" />
</sql>
</target>
<target name="create.user">
<sql driver="${jdbc.driver}" password="${jdbc.system.password}" url="${jdbc.url}" userid="${jdbc.system.userid}" encoding="utf8">
<transaction>
CREATE USER ${jdbc.userid} IDENTIFIED BY ${jdbc.password};
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO ${jdbc.userid} WITH ADMIN OPTION;
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE,
　　ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER,
　　COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
　　CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE,
　　CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE,
　　DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,
　　DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE,
　　SELECT ANY TABLE, UPDATE ANY TABLE
　　TO ${jdbc.userid} WITH ADMIN OPTION;
</transaction>
<classpath path="lib/classes12.jar" />
</sql>
</target>
<target name="rebuild.user" depends="drop.user, create.user" description="rebuild dev user"/>
</project>