java（抽取所有数据库的dba_segments信息，进行统计分析）
JDBCTool jdbc数据库连接类
package com.tools;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTool {
private final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private String URL ;
private final String USERNAME ="SYSTEM";
private String PASSWORD;
public String getURL() {
return URL;
}
public void setURL(String uRL) {
URL = uRL;
}
public String getPASSWORD() {
return PASSWORD;
}
public void setPASSWORD(String pASSWORD) {
PASSWORD = pASSWORD;
}
private Connection conn = null;
public JDBCTool() {
try {
Class.forName(DRIVER).newInstance(); // 加载数据库驱动
} catch (Exception ex) {
System.out.println("加载数据库驱动失败!");
}
}
public boolean creatConnection() {// 用来创建数据库连接
boolean isCreated = true;// 默认创建成功
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 创建数据库连接
} catch (SQLException e) {
isCreated = false;// 创建失败
System.out.print(e);
}
return isCreated;// 返回创建情况
}
public boolean executeUpdate(String sql) {// 用来插入、修改和删除记录
boolean isUpdate = true;// 默认执行成功
if (conn == null)
creatConnection();
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (SQLException e) {
isUpdate = false;// 执行失败
}
return isUpdate;// 返回执行情况
}
public ResultSet executeQuery(String sql) { ResultSet rs = null; if (this.conn == null) { this.creatConnection(); } try { Statement stmt = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(1024); rs = stmt.executeQuery(sql); } catch (SQLException var4) { log.info("SQL查询失败!"); log.info(var4.getMessage()); } return rs; } public void closeConnection() { if (this.conn != null) { try { this.conn.close(); } catch (SQLException var5) { var5.printStackTrace(); } finally { this.conn = null; } } } }
c3p0连接池配置
db.properties文件
jdbc.jdbcUrl=jdbc:oracle:thin:@192.xx.xx.xx:21521/xxxx jdbc.driverClass=oracle.jdbc.driver.OracleDriver jdbc.user=xxxx jdbc.password=xxxxxx
bean.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd">
<!-- 配置数据源 -->
<context:property-placeholder location="classpath:db.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="minPoolSize" value="3"></property>
<property name="maxPoolSize" value="8"></property>
<property name="acquireRetryAttempts" value="3"></property>
<property name="testConnectionOnCheckin" value="true"></property>
<property name="checkoutTimeout" value="10000"></property>
<property name="idleConnectionTestPeriod" value="30"></property>
<property name="breakAfterAcquireFailure" value="false"></property>
</bean>
<!-- 配置jdbctemplate实例 -->
<bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
main方法
db_info表保存了数据库的tsn连接串信息
db_user表保存每个数据库对应的system账户信息
package com.itsm;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.dao.DataBaseDao;
import com.tools.JDBCTool;
public class sync_segment {
public static void main(String[] args) throws SQLException {
String xmlpath = "bean.xml";
ApplicationContext context = new ClassPathXmlApplicationContext(xmlpath);
JdbcTemplate j = (JdbcTemplate) context.getBean("jdbctemplate");
String sql="select f_decrypt_number(PASSWORD) password,'jdbc:oracle:thin:@'||replace(a.scanip,' ','') url,a.db_id from db_info a,db_users b where a.db_id=b.db_id and b.username='SYSTEM'"+
" and a.status_code='1' and a.database_type='ORACLE' and A.DB_REGION='生产' and a.status_code='1' order by a.database_name ";
RowMapper<DataBaseDao> row= new BeanPropertyRowMapper<>(DataBaseDao.class);
List<DataBaseDao> database=(List<DataBaseDao>) j.query(sql,row);
List<Object[]> segment = new ArrayList<Object[]>();
for(int i=0;i<database.size();i++){
JDBCTool jdbc=new JDBCTool();
jdbc.setPASSWORD(database.get(i).getPassword());
jdbc.setURL(database.get(i).getUrl());
boolean sign=jdbc.creatConnection();
if (sign==false){
System.out.println(database.get(i).getDb_id()+':'+database.get(i).getUrl()+" FALSE");
continue;
}
System.out.println(database.get(i).getDb_id()+':'+database.get(i).getUrl());
ResultSet rs = jdbc.executeQuery(
"select /*+parallel 4*/ OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,"
+ "TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments"
);
int commit=0;
segment=new ArrayList<Object[]>();
String insert_sql="insert into issue.db_segment_detial (db_id,OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,"
+ "BYTES,BLOCKS,EXTENTS)values(?,?,?,?,?,?,?,?,?)";
while(rs.next()){
segment.add(new Object[]{database.get(i).getDb_id(),rs.getString(1),rs.getString(2),rs.getString(3)
,rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)
});
commit++;
if(commit==100000){
j.batchUpdate(insert_sql,segment);
commit=0;
segment=new ArrayList<Object[]>();
}
}
if(commit!=0){
j.batchUpdate(insert_sql,segment);
}
jdbc.closeConnection();
} } }
