java解析oracle日志,获取指定日期后的更新和插入数据

最新推荐文章于 2021-04-15 08:22:00 发布

zl-zero

最新推荐文章于 2021-04-15 08:22:00 发布

阅读量2.7k

点赞数 1

分类专栏： java解析oracle日志

本文链接：https://blog.csdn.net/skyjohson/article/details/36891835

版权

java解析oracle日志专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.数据库连接很简单就不写了

如何实现解析oracle日志?

在oracle中有logmnr解析过程包,通过该过程包对oracle日志进行解析,以下是示例

/**
*
* 执行过程语句
*/
private void executeCallable(String _sql, CallableStatement _call,
Connection _con) throws SQLException {
_call = _con.prepareCall(_sql);
_call.execute();
}

/**

*该方法是获取oracle日志地址,封装到一个list中

private List<String> doLogAddress() throws SQLException {
List<String> listLog = new ArrayList<String>();

//数据库连接
Connection con = this.createConnection();
Statement stat = con.createStatement();
// 获取日志地址
String logSql = "select group#,member from v$logfile ORDER BY group#";
ResultSet res = null;
try {
res = stat.executeQuery(logSql);
while (res.next()) {
listLog.add(res.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeConnection(con, res);
}
return listLog;
}

/**

*添加日志文件返回一个String

private String doAddfileSQL(List<String> _listLog) {
StringBuffer addLogSql = new StringBuffer();
addLogSql.append("BEGIN ");
addLogSql.append("dbms_logmnr.add_logfile(logfilename=>'"
+ _listLog.get(0).replaceAll("\\\\", "\\\\\\\\")
+ "',options=>dbms_logmnr.NEW);");
for (int i = 1; i < _listLog.size() - 1; i++) {
addLogSql.append("dbms_logmnr.add_logfile(logfilename=>'"
+ _listLog.get(i).replaceAll("\\\\", "\\\\\\\\")
+ "',options=>dbms_logmnr.addfile);");
}
addLogSql.append("END;");
return addLogSql.toString();
}

/**

*获取指定日期后的更新和插入数据的rowid

*_date是指定日期,格式:yyyy-MM-dd HH24:mi:ss

public List<String> doUpdateAndInsertToData(String _date) {
Connection con = this.createConnection();
Statement stat = null;
CallableStatement callableStatement = null;
List<String> listLog = new ArrayList<String>();
try {
listLog = this.doLogAddress();
stat = con.createStatement();
// 添加所有日志
String addLogSQL = this.doAddfileSQL(listLog);
this.executeCallable(addLogSQL, callableStatement, con);
System.out.println("添加日志完成");
// 开始分析日志,联网方式
String startLog = "BEGIN dbms_logmnr.start_logmnr(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;";
this.executeCallable(startLog, callableStatement, con);
System.out.println("日志分析完成");
// 查询日志内容,将更新和插入操作的数据全部查出来
String analysisLog = "SELECT t.ROW_ID FROM v$logmnr_contents t where t.TABLE_NAME='"+(需要查找的表名)+"' "
+ "and (t.OPERATION like 'UPDATE' or t.OPERATION like 'INSERT') "
+ "and to_date(TO_CHAR(SCN_TO_TIMESTAMP(t.scn), 'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd HH24:mi:ss')"
+ ">to_date('" + _date + "','yyyy-MM-dd HH24:mi:ss')";
ResultSet result = stat.executeQuery(analysisLog);
listLog.clear();
while (result.next()) {
System.out.println(result.getString(1));
listLog.add(result.getString(1));
}
// 分析完成后,释放内存
String endLogSQL = "BEGIN dbms_logmnr.end_logmnr;END;";
this.executeCallable(endLogSQL, callableStatement, con);
} catch (SQLException e) {
e.printStackTrace();
}
return listLog;
}