使用java程序实现对oracle的导入和导出并创建表空间、用户、制定用户的默认表空间、给用户授权
package job;
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.binfeng.saas.util.UserUtil;
import com.binfeng.saas.util.UserUtilException;
public class JavaOracle{
public static void main(String[] args) {
expFile();
// createTablespace("back2");
// createUser("back2");
// impFile("back2");
// grant("leftf3");
}
/**得到一个oralce的连接*/
public static Connection getConnection() {

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
try {
conn = Instance().getUrl(),
Instance().getUsername(), UserUtil
.getInstance().getPassword());
} catch (UserUtilException e) {
System.out.println("jdbc.property 文件解析失败!");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// 从oracle导出
public static void expFile() {
String[] cmds = new String[3];
String commandBuf = "exp cwbak/cwbak@cwerp
  file=c://cwbak.dmp log=c://cwbak.log";
// String commandBuf = "exp cwuser/cwuser@cwerp
// file=e://accbookBak//cwuser.dmp log=e://accbookBak//cwuser.log";
cmds[0] = "cmd";
cmds[1] = "/C";
cmds[2] = String();
Process process = null;
try {
process = Runtime().exec(cmds);
} catch (IOException e) {
e.printStackTrace();
}
boolean shouldClose = false;
try {
InputStreamReader isr = new InputStreamReader(process
.
getErrorStream());
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
System.out.println(line);
if (line.indexOf("????") != -1) {
shouldClose = true;
break;
}
}
} catch (IOException ioe) {
shouldClose = true;
}
if (shouldClose)
process.destroy();
int exitVal;
try {
exitVal = process.waitFor();
System.out.print(exitVal);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
// 导入oracle
public static void impFile(String tablespaceName) {
String[] cmds = new String[3];
String commandBuf = "imp system/system@cwerp fromuser=cwbak touser="
+ tablespaceName + " file=c://cwbak.dmp ignore=y";
cmds[0] = "cmd";
cmds[1] = "/C";
cmds[2] = String();
Process process = null;
try {
process = Runtime().exec(cmds);
} catch (IOException e) {
e.printStackTrace();
}
boolean shouldClose = false;
try {
InputStreamReader isr = new InputStreamReader(process
.getErrorStream());
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
System.out.println(line);
if (line.indexOf("????") != -1) {
shouldClose = true;
break;
}
}
} catch (IOException ioe) {
shouldClose = true;
}
if (shouldClose)
process.destroy();
int exitVal;
try {
exitVal = process.waitFor();
System.out.print(exitVal);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
//创建表空间
public static void createTablespace(String tablespaceName) {
Connection conn = null;
PreparedStatement ps = null;
conn = getConnection();
String sql = "create tablespace " + tablespaceName
+ " logging datafile" + "'" + tablespaceName + ".dbf'"
+ "size 5M  extent management local AUTOALLOCATE";
System.out.println(sql);
try {
ps = conn.prepareStatement(sql);
ps.executeUpdate();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

// 创建用户并且制定已经创建好的表空间
public static void createUser(String userName) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
String sql1 = "create user " + userName
+ " identified by abc default tablespace " + userName;
/
/ +" temporary tablespace " +"t"+userName; 临时表暂时没有用，不使用临时表
String sql2 = "grant connect,resource to " + userName;
String sql3 = "revoke  unlimited tablespace from " + userName;
String sql4 = "alter user " + userName + " quota 0 on " + userName;
String sql5 = "alter user " + userName + " quota unlimited on "
+ userName;
// String sql6 = "grant dba to "+ userName;
String str[] = new String[5];
str[0] = sql1;
str[1] = sql2;
str[2] = sql3;
str[3] = sql4;
str[4] = sql5;
// str[5] = sql6;
for (int i = 0; i < str.length; i++) {
ps = conn.prepareStatement(str);
ps.executeUpdate();
}
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 给具体的某个用户赋增、删、改、查以及dba的权限
*
* @param dbUser
*/
public static void grant(String dbUser) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
String sql1 = "grant select,insert,delete,update on " + dbUser
+ "表 to 用户";

String sql2 = "grant dba to " + dbUser;//赋dba的权限
String str[] = new String[20];
str[0] = sql1;
str[1] = sql2;
for (int i = 0; i < str.length; i++) {
ps = conn.prepareStatement(str);
ps.executeUpdate();
}
jdbc连接oracle
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。