如何在Oracle中批量删除数据

 
    在一个ORACLE数据库运行过程中，有时候会遇到要批量删除数据的情况，如一个保存历史数据的表中有大量的数据已经不需要保留，要将这部分数据删除。
以下就这种情况可以采取的方法进行讨论。

1、使用TRUNCATE命令进行删除。
如果是整个表的数据都要删除的话，使用TRUNCATE TABLE命令是理想的选择。它删除了表中的所有数据，并且因为不写REDO LOG FILE，所以速度很快。删除的同时，表的索引和约束条件仍然存在。这种方法适用于ORACLE的各个版本。但是当要删除的数据只是表中的一部分时，这种方法便行不通了。

2、直接进行DELETE操作。
直接使用DELETE命令进行删除，如果删除的数据量较大时，可能导致回滚段出错。这是因为在删除数据的过程中，不断扩展回滚段，直到回滚段的最大范围数
或回滚段所在表空间空闲空间用完而出错。解决这个问题可以通过给删除数据的事务指定一个足够大的回滚段或者将回滚段所在表空间的AUTOEXTEND选项打开，同时将回滚段的MAXEXTENTS改大或设为UNLIMITED。不过这样仍存在一个隐患，如果删除的数据量大，同时数据库工作于归档模式下时，有可能导致日志切换频繁，所有日志文件都处于需要归档的状况，而归档进程来不及归档日志文件的情况出现，这时数据库将被挂起，直到有可用的日志文件后才恢复正常。
所以这种方法也不理想。

3、通过PL/SQL循环分段删除。
第三种方法是专门针对上面第二种方法进行优化改进的。这种方法通过一段PL/SQL程序循环分段删除数据，逐步提交事务，达到缩小事务规模，安全删除数据的目的。
例如有一个数据表t_table，我们将对其中字段c_date满足小于2001年1月1日的记录进行删除，可以采用以下的PL/SQL程序。

1 DECLARE
2 V_TEMP NUMBER;
3 BEGIN
4 LOOP
5 BEGIN
6 SELECT 1 INTO V_TEMP FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum = 1;
7 DELETE FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum < 100;
8 COMMIT;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 EXIT;
12 END;
13 END LOOP;
14 END;

程序的第1和第2行声明了一个临时变量。第4到第13行定义了一个循环，在这个循环中第6行不断检查表中是否还有满足条件的记录，如果有，第7行程序便执行删除操作，每次删除100记录，同时提交事务。当表中已无满足条件的记录时，便引起NO_DATA_FOUND的异常，从而退出循环。通过分批删除，逐步提交，缩小了事务的规模，从而达到避免出现回滚段错误的目的。然而这种方法依然存在因日志切换频繁，而归档进程来不及归档日志文件而导致数据库挂起的可能性。下面的程序通过ORACLE所提供的dbms_lock包中的过程sleep，解决了这个问题，从而达到安全快速大量删除数据的目的。

1 DECLARE
2 V_LOGNUM NUMBER; -- 数据库中拥有的日志文件数
3 V_NEEDARC NUMBER; -- 需要归档的日志文件数
4 BEGIN
5 SELECT count(1) INTO V_LOGNUM FROM V$LOG;
6 LOOP
7 LOOP
8 SELECT count(1) INTO V_NEEDARC FROM V$ARCHIVE;
9 IF V_NEEDARC < V_LOGNUM - 1 THEN
10 EXIT;
11 ELSE
12 DBMS_LOCK.SLEEP(60);
13 END IF;
14 END LOOP;
15
16 DELETE FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum < 100;
17 IF SQL%ROWCOUNT = 0 THEN
18 EXIT;
19 END IF;
20 COMMIT;
21 END LOOP;
22 END;
程序中的第2和第3行声明了两个变量v_lognum和v_needarc来保存数据库中日志文件的数量和当前需要归档的日志文件数量。
第5行获取了数据库中日志文件的数量。
第6行到第21行开始了删除数据的循环，第7行到第14行是一个子循环，不断检测当前需要归档的日志文件的数量v_needarc是否小于数据库的日志文件总数v_lognum减去1，如果满足条件，则退出子循环，开始删除数据。否则的话便调用dbms_lock.sleep()过程，使程序休眠60秒，然后继续子循环，检测需归档的日志文件数量。
第17到19行，检查删除数据的结果，如果已无数据，则退出，程序结束。
这个程序，通过利用dbms_output.sleep()过程，在删除过程中当需要归档的日志文件达到认定的限制时，使删除过程暂时停止，等待ARCH进程将日志文件归档后再继续进行，从而达到避免归档日志文件来不及归档，导致数据库挂起的问题。
此方法适用于oracle的各个版本。

4、使用NOLOGGING选项重新建表。
在ORACLE 8以后的版本中，CREATE TABLE命令提供了NOLOGGING的选项，在建表时不用写日志文件。
这样当我们在删除大量的数据时可以将要保留的数据通过CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法将要保留的数据备份到另一个表中，将原来的表删除，然后再 ALTER TABLE RENAME TO 命令将备份的表改为原来表的名字。
这个方法由于不写日志文件，所以速度很快，但是原来的表所拥有的索引和约束都将不存在，需重新建立。另外这个方法只适用于ORACLE 8以后的版本，ORACLE7.3中也可采用这个方法，但NOLOGGIN关键字要由UNRECOVERABLE代替。

前面比较了在ORACLE中如何批量删除数据的几种方法，以上这几种方法均在oracle 8i for windows，oracle 7.3 for windows，oracle 7.1 for netware中测试通过，在应用中可以根据实际情况选择一种合适的方法进行处理。

关于Oracle中大批量删除数据的方法，也可以参考Oracle专家eygle的文章：http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html