oracle巨大表的数据删除的方法，20分钟搞定
oracle巨大表的数据删除的方法，20分钟搞定
一个客户的日志表，已经有3000多千万的记录了，容量大约30G，打算维护一下，看了一下字段，发现日志是按日期记录的，打算只保留3个月的日志就好了。
第一个思路：
按条件查出来，直接DELETE
试了一下
delete from NTLS_LOGS where to_char(START_TIMES,'yyyy-mm-dd hh:mi:ss') <'2008-08-01 00:00:00';
时间会非常长，不知道多少天能跑完，反正一个晚上没跑完，UNDO会增长到一个可怕的数字
这个思路是不行的
研究了一下，发现大多数人的思路是做个循环，每5000条左右提交一次，做了个测试语句试了一下，每小时能删除80－90万条，晕倒需要删除的数据是2700万条，猴年马月才能完成。
询问客户，发现这个业务日志表不是什么时候都要写的，每天晚上10点到第二天早上6点是没有人会使用的，很好，我立刻考虑了一种方法，思路是将需要的数据提取出来到一个新的表，然后将原表删除，将这个提取出来的表重命名为旧的日志表名。
呵呵，试一下这种方法的威力，生成新的数据整个过程只用了172S，也就是3分钟左右，表是生成了，但是没有索引，那就照抄原表的索引吧，呵呵最后花了18分21秒搞定了，其实原理就是只处理必须保留的数据这些数据远远小于要删掉的数据，跳过不需要的数据，采用CREAT SELECT AS可以充分使用索引，而且不会生存UNDO,因此速度大大加快，该方法的弊端是，必须要有一点时间，没有人使用这个表才行。
于是我写了一个SQL，将这个放到每月的自动执行计划里定期执行，定期维护这个表，避免表异常膨大，说了原理，就该上SQL了。
drop table new_log_temp;
create table new_log_temp as select * from NTLS_log where timestamp > to_date(to_char(SYSDATE-90,'yyyy-mm-dd'),'yyyy-mm-dd');
drop NTLS_LOG;
alter table new_log_temp rename to NTLS_LOG;
create index IDX_NTLS_LOG on NTLS_LOG (RECORD)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_NTLS_LOG_OPERATE_ID on NTLS_LOG (OPERATE)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
相关文档：
在数据库中经常要合并字符串，而合并字符串的方法有很多，现在总结如下：
--创建会话级临时表
create global temporary table TMPA
(
ID INTEGER,
NAME VARCHAR2(10)
)
on commit preserve rows;
--插入记录
insert into tmpa select 1,'aa' from dual;
insert into tmpa select 1,'bb ......
在pl/sql时经常会碰到将某些特定的符号转换成行,这功能相当于split.以前在网上收集的split函数如下:
CREATE OR REPLACE TYPE DJ_STR_SPLIT IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE PACKAGE Dj_Function_API IS
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'DjFuncti ......
察看listener.log 确认没有问题
# find / -name listener.log
/home/oracle/app/OraHome_1/network/log/listener.log
位置在$ORACLE_HOME/network/log/listener.log
TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 29-DEC-2009 15:12:15
Copyright (c) 1991, 2007, Oracle. All ......
High Water Mask（最高水位线，呵呵）以下简称HWM
delete命令不影响表自身的HWM，即表中的区块会随着数据量的增大而增大，但删除数据时，
区块并不会减少，当查询时，会按照HWM的标准去扫描表，即使使用delete将表数据清空，查询时，
依旧会扫描HWM的区块，查询速度也就和没使用delete命令之前是一样的，而truncate可以� ......
oracle表中有Date类型的字段，用PD自动生成测试数据，格式为‘yyyy-mm-dd’，而oracle中插入Date类型的字段格式为'dd-mm月-yyyy'。如果数据量少，可以用to_date()，
to_date('2008-01-25 12:39:49','YYYY-MM-DD HH24:MI:SS')
但是测试用数据有10万条，如果受动去写显然是不实际的� ......
