1、使用背景
该软件设计原理,结合Oracle存储过程(自定义事务控制,将大数据量分解成小批量数据,小批量提交事务),能够自动释放表空间.
该软件适用于Oracle数据库环境下,需要根据条件大批量删除数据
该软件仅在Oracle 10G下测试过.
2、部分示例代码:
存储过程脚本
create or replace procedure delBigTab
(
p_TableName in varchar2,
p_Condition in varchar2,
p_Count in varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
主入口函数
package com.eryansky;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.eryansky.util.Constants;
/**
* 主入口函数.
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-27 下午11:03:03
* @version : v1.0
* @since
*/
public class MainJob {
private static Logger logger = LoggerFactory.getLogger(MainJob.class);
public static void main(String[] args) {
logger.info("server run ... ");
try {
//轮询时间
long pp = Long.valueOf(Constants.getPeriod());
//启动线程
JobExecutor job = new JobExecutor(pp);
job.start();
} catch (Exception e) {
e.printStackTrace();
}finally{
logger.info("server stop ... ");
}
}
}
线程调度
package com.eryansky;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang.Validate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.support.TaskUtils;
import com.eryansky.service.JobManager;
import com.eryansky.util.ThreadUtils;
/**
* 被ScheduledThreadPoolExecutor定时执行的任务类.
*/
public class JobExecutor
implements Runnable {
private static Logger logger = LoggerFactory.getLogger(JobExecutor.class);
/**
* 延迟时间(秒)
*/
private int initialDelay = 0;
/**
* 循环调度时间(秒)
*/
private long period = 0;
private int shutdownTimeout = Integer.MAX_VALUE;
private ScheduledExecutorService scheduledExecutorService;
private JobManager jobManager;
public JobExecutor(long period) {
super();
this.period = period;
}
public void start() throws Exception {
Validate.isTrue(period > 0);
jobManager = new JobManager();
// 任何异常不会中断schedule执行
Runnable task = TaskUtils
.decorateTaskWithErrorHandler(this, null, true);
scheduledExecutorService = Executors
.newSingleThreadScheduledExecutor(new ThreadUtils.CustomizableThreadFactory(
"JdkExecutorJob"));
// scheduleAtFixedRatefixRate() 固定任务两次启动之间的时间间隔.
// scheduleAtFixedDelay() 固定任务结束后到下一次启动间的时间间隔.
scheduledExecutorService.scheduleAtFixedRate(task, initialDelay,
period, TimeUnit.SECONDS);
}
public void stop() {
ThreadUtils.normalShutdown(scheduledExecutorService, shutdownTimeout,
TimeUnit.SECONDS);
}
/**
* 定时打印当前用户数到日志.
*/
public void run() {
logger.info("Job run ...");
try {
jobManager.call();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置任务初始启动延时时间.
*/
public void setInitialDelay(int initialDelay) {
this.initialDelay = initialDelay;
}
/**
* 设置任务间隔时间,单位秒.
*/
public void setPeriod(long period) {
this.period = period;
}
/**
* 设置gracefulShutdown的等待时间,单位秒.
*/
public void setShutdownTimeout(int shutdownTimeout) {
this.shutdownTimeout = shutdownTimeout;
}
}
业务处理类
package com.eryansky.service;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.eryansky.db.JobDao;
/**
* 业务处理类.
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-27 上午9:35:22
* @version : v1.0
* @since
*/
public class JobManager {
private static Logger logger = LoggerFactory.getLogger(JobManager.class);
private JobDao jobDao;
/**
* 条件
*/
private static String CONDITION = "";
/**
* 提交数据条数.
*/
private static final int COUNT = 10000;
public JobManager() {
jobDao = JobDao.getInstance();
}
/**
* 执行sql.
*
* @param sql
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-28 下午4:32:46
* @version : v1.0
*/
public void execute(String sql) {
try {
jobDao.execute(sql);
} catch (Exception e) {
System.out.println("操作数据库失败," + e.getMessage());
e.printStackTrace();
}
}
/**
* 执行存储过程.
*
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-27 上午9:47:18
* @version : v1.0
*/
public void call() {
InputStream inStream = null;
InputStreamReader isr = null;
BufferedReader bfr = null;
try {
inStream = getClass().getResourceAsStream("/config/tables.txt");
isr = new InputStreamReader(inStream);
bfr = new BufferedReader(isr);
String line;
while ((line = bfr.readLine()) != null) {
if (StringUtils.isNotBlank(line) && !line.startsWith("#")) {
logger.debug(line);
String[] ss = line.split("\\|");
if (ss.length >= 2 && StringUtils.isNotBlank(ss[1])) {
CONDITION = ss[1];
}
//执行存储过程
jobDao.execute("call delBigTab('" + ss[0] + "','" + CONDITION
+ "','" + COUNT + "')");
//释放表空间
// jobDao.execute("alter table "+ss[0]+" deallocate UNUSED KEEP 0");
//Oracle10G使用以下语句收缩碎片
jobDao.execute("alter table "+ss[0]+" enable row movement");//启用 语句会造成引用表的对象(如存储过程、包、视图等)变为无效
jobDao.execute("alter table "+ss[0]+" shrink space compact");//数据重组
jobDao.execute("alter table "+ss[0]+" shrink space");//HWM调整
jobDao.execute("alter table "+ss[0]+" disable row movement");//禁用
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
bfr.close();
isr.close();
inStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
DAO
package com.eryansky.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang.Validate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.eryansky.JobExecutor;
import com.eryansky.util.Constants;
/**
* 操作数据库 使用commons-dbutils组件操作数据库(单例模式).
*/
public class JobDao {
private static Logger logger = LoggerFactory.getLogger(JobExecutor.class);
private volatile static JobDao instance;// 单例对象
private Connection conn = null;
private static String url = "jdbc:oracle:thin:@192.168.2.185:1521:orcl";
// driverName
private static String driverName = "oracle.jdbc.driver.OracleDriver";
private static String username = "datachange";// 用户名
private static String password = "password";// 密码
static{
url = Constants.appconfig.getProperty("jdbc.url", url);
driverName = Constants.appconfig.getProperty("jdbc.driverClassName", driverName);
username = Constants.appconfig.getProperty("jdbc.username", username);
password = Constants.appconfig.getProperty("jdbc.password", password);
logger.info("url:{}", url);
logger.info("driverName:{}", driverName);
logger.info("username:{}", username);
logger.info("password:{}", password);
DbUtils.loadDriver(driverName);
}
/**
* 单例构造方法.
*
* @return
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-25 下午11:10:44
* @version : v1.0
*/
public static JobDao getInstance() {
if (instance == null) {
synchronized (JobDao.class) {
if (instance == null) {
instance = new JobDao();
}
}
}
return instance;
}
/**
* 初始化Connection 打开连接.
*
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-25 下午9:28:41
* @version : v1.0
*/
public void init() {
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
logger.error("打开数据库连接失败.");
e.printStackTrace();
}
}
/**
* 定时任务.
*
* @throws Exception
* @author : 尔演&Eryan eryanwcp@gmail.com
* @date : 2013-4-25 下午11:10:30
* @version : v1.0
*/
public void execute(String sql) throws Exception {
Validate.notEmpty(sql);
try {
init();
QueryRunner qr = new QueryRunner();
Date d1 = new Date();
int count = qr.update(conn, sql);
Date d2 = new Date();
Long times = d2.getTime()-d1.getTime();
logger.info("Count:{},Time:{}ms",count,times);
logger.info("SQL:{}",sql);
} catch (SQLException e) {
logger.error(e.getMessage());
} finally {
DbUtils.closeQuietly(conn);// 关闭连接
}
}
}
完整代码请参考附件....