oracle如何导出数据库表所有数据
发布网友
共2个回答
懂视网
数据文件生成方式有多种，但是大多效率低下，对于大型数据仓库来说，高效导出文件是迫切需求：
这里通过shell+java的方式集成老熊写的数据文件生成工具，效率比普通的导出方法高70%：
shell文件生成脚本：
/**
* @Title: Db2FileSync.java
* @Package etl.etlUtils
* @Description: TODO
* @author Lubin Su
* @date 2015年7月15日 下午12:43:09
* @version V1.0
*/
package etl.etlUtils;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.Callable;
import etl.dao.impl.GenericDaoImpl;
/**
*
* @author Lubin Su
* @date 2015年7月15日 下午12:43:09
* @version V1.0
*/
public class Db2FileSync implements Callable<Object> {
private String procId;
private String srcDb;
private String destDir;
private String srcSql;
private String fileNm;
private String statCycleId;
/**
* <p>Title: 构造函数</p>
* <p>Description: </p>
* @param procId
* @param srcDb
* @param destDir
* @param srcSql
* @param fileNm
* @param statCycleId
*/
public Db2FileSync(String procId, String srcDb, String destDir,
String srcSql, String fileNm, String statCycleId) {
super();
this.procId = procId;
this.srcDb = srcDb;
this.destDir = destDir;
this.srcSql = srcSql;
this.fileNm = fileNm;
this.statCycleId = statCycleId;
}
/**
* 数据表以文件的方式下发
* @author lubinsu
* @date 2015年7月12日
* @param procId
* @throws UnsupportedEncodingException
*/
public void db2FileExtractByProcInfo(String srcDb, String descDir, String srcSql, String fileNm) throws UnsupportedEncodingException {
//获取当前时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date vStartTime = new Date();
System.out.println("Start sending ... " + sdf.format(vStartTime));
String[] cmd = new String[5];
//环境变量
String[] env = {"lubinsu_dir=/int_file/lubinsu","AUTHSTATE=compat","TERM=vt100","SHELL=/usr/bin/bash","NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK","SSH_CLIENT=132.228.78.73 49796 22","SSH_TTY=/dev/pts/4","LOCPATH=/usr/lib/nls/loc","USER=css_int","ODMDIR=/etc/objrepos","ORACLE_BASE=/oracle/app/oracle","TMOUT=0","MAIL=/usr/spool/mail/css_int","PATH=:/usr/java6_/bin:/opt/freeware/bin:/usr/local/bin:/bin:/usr/bin:/usr/sbin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/home/css_app/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/urs/ccs/bin:/bin:/usr/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin:.","css_int_HOME=/int_file","LOGIN=css_int","PWD=/int_file/lubinsu/scripts/etl_dispatch/css_etl","JAVA_HOME=:/usr/java6_","EDITOR=vi","JAVA_COMPILER=NONE","LANG=AMERICAN_AMERICA.ZHS16GBK","etl_dir=/int_file/etl_dir","TZ=Asia/Shanghai","LIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib","SHLVL=1","HOME=/int_file","etl_dispatch=/int_file/lubinsu/scripts/etl_dispatch/css_etl","LC__FASTMSG=true","MAILMSG=[YOU HAVE NEW MAIL]","LOGNAME=css_int","CLASSPATH=::/usr/java6_/lib","SSH_CONNECTION=132.228.78.73 49796 132.228.27.132 22","CLCMD_PASSTHRU=1","SHLIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib","ODS_HOME=/int_file/ODS_ETL/","ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_2","_=/bin/env","OLDPWD=/int_file/lubinsu/etl_data","NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat","LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib"};
Process prc = null;
int exitVal = 0;
cmd[0] = "/int_file/lubinsu/scripts/etl_dispatch/css_etl/java_etl/db2File.sh";
cmd[1] = srcDb;
cmd[2] = descDir;
cmd[3] = srcSql;
cmd[4] = fileNm;
try {
prc = Runtime.getRuntime().exec(cmd, env);
} catch (IOException e) {
e.printStackTrace();
}
InputStream stderr = prc.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
//打印返回信息
try {
while ((line = br.readLine()) != null)
System.out.println(line);
} catch (IOException e) {
e.printStackTrace();
}
try {
exitVal = prc.waitFor();
} catch (InterruptedException e) {
e.printStackTrace();
}
//打印返回号
if (exitVal > 0) {
System.out.println("Process exitValue: " + exitVal);
}
//获取结束时间
Date vEndTime = new Date();
System.out.println("End sending ... " + sdf.format(vEndTime) + ",elapsed time: " + (vEndTime.getTime() - vStartTime.getTime())/1000 + " seconds.");
}
/**
*
* @return
* @throws Exception
* @see java.util.concurrent.Callable#call()
*/
@Override
public Object call() throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date vStartDt = new Date();
GenericDaoImpl.update("INSERT INTO shell_proc_db_2_db_log(proc_id, run_msg, status, stat_cycle_id) VALUES(?, ?, ?, ?)", new String[]{procId, sdf.format(vStartDt) + " 文件开始导出", "2", statCycleId});
db2FileExtractByProcInfo(srcDb, destDir, srcSql, fileNm.toUpperCase() + "_" + statCycleId + ".DEL");
GenericDaoImpl.update("UPDATE shell_proc_db_2_db_log o SET o.status = 0, modify_dt = SYSDATE, run_msg = o.run_msg || CHR(13) || to_char(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') || ' 文件生成结束' WHERE proc_id = ? and stat_cycle_id = ?", new String[] { procId, statCycleId});
GenericDaoImpl.update("UPDATE shell_proc_db_2_db_cfg o SET o.last_succ_cycle = ? WHERE o.proc_id = ?", new String[] { statCycleId, procId});
return null;
}
}
获取配置的流程信息：
public static List<Map<String, Object>> getProcIdForDb2File() {
//获取当前时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
Date vNowDate = new Date();
String vNowDateStr = sdf.format(vNowDate);
ResultSetHandler rsh = new BeanListHandler();
String vRunSql = "SELECT proc_id, src_db, src_sql, dest_dir, file_nm, stat_cycle_id
" +
" FROM (SELECT o.run_type,
" +
" o.proc_id,
" +
" o.src_db,
" +
" o.src_sql,
" +
" o.dest_dir,
" +
" o.file_nm,
" +
" o.last_succ_cycle,
" +
" CASE
" +
" WHEN o.run_type = 2 THEN
" +
" to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')
" +
" WHEN o.run_type = 3 THEN
" +
" to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')
" +
" WHEN o.run_type = 4 THEN
" +
" to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - o.run_hour, 'yyyymmdd')
" +
" WHEN o.run_type = 1 THEN
" +
" to_char(add_months(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), -1), 'yyyymm')
" +
" END stat_cycle_id
" +
" FROM shell_proc_db_2_db_cfg o
" +
" WHERE o.etl_type = 'Db2File'
" +
" AND o.state = 'A'
" +
" AND CASE
" +
" WHEN o.run_type = 2 THEN
" +
" to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')
" +
" WHEN o.run_type = 3 THEN
" +
" to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')
" +
" WHEN o.run_type = 4 THEN
" +
" to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')
" +
" WHEN o.run_type = 1 THEN
" +
" to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymm') || o.run_day || o.run_hour || o.run_minute, 'yyyymmddhh24mi')
" +
" END <= SYSDATE) v
" +
" WHERE v.last_succ_cycle < v.stat_cycle_id
" +
" AND NOT EXISTS (SELECT 1
" +
" FROM shell_proc_db_2_db_log i
" +
" WHERE i.proc_id = v.proc_id
" +
" AND i.stat_cycle_id = v.stat_cycle_id)";
List<Map<String, Object>> arr = null;
try {
arr = (List<Map<String, Object>>) GenericDaoImpl.query(vRunSql, null, rsh);
} catch (SQLException e) {
e.printStackTrace();
}
return arr;
}调用测试：
public static void main(String[] args) throws IOException, InterruptedException {
int taskSize = 13;
// 创建一个线程池
ExecutorService pool = Executors.newFixedThreadPool(taskSize);
// 创建多个有返回值的任务
//List<Future> list = new ArrayList<Future>();
/*List<Map<String, Object>> procs = DataSynchronizer.getProcIdForDb2Db();
for (Map<String, Object> map : procs) {
Callable<Object> db2Db = new Db2DbSync(map.get("PROC_ID").toString(), map.get("PROC_NAME").toString());
pool.submit(db2Db);
}*/
List<Map<String, Object>> db2Files = DataSynchronizer.getProcIdForDb2File();
for (Map<String, Object> map : db2Files) {
Callable<Object> db2File = new Db2FileSync(map.get("PROC_ID").toString(), map.get("SRC_DB").toString(), map.get("DEST_DIR").toString(), map.get("SRC_SQL").toString(), map.get("FILE_NM").toString(), map.get("STAT_CYCLE_ID").toString());
pool.submit(db2File);
}
pool.shutdown();
}版权声明：本文为博主原创文章，未经博主允许不得转载。
Oracle快速导出数据文件
标签：oracle shell etl java
热心网友
你好
你可以使用exp 护着expdp 来进行表的导出导入
如果数据量大的话，你还可以使用RMAN来操作
或者使用XTTS进行迁移，
再或者，直接迁移存储
望采纳
