To delete a large table of millions records using PL/SQL

To delete a large table of millions records using PL/sql:

Recently, developers have been stress-tested in the test environment database, resulting in a large amount of dirty data in the database. There are several records of large tables of data in millions or even thousands. The developer now proposes the following requirements:

Only some of the dirty data is cleaned up, and the truncate operation is not allowed on these tables. Then there is the following PL/SQL cleanup script:

1. Write the script that deletes the data table data as follows:

$ cat Data_del.sql Set Serveroutput on
Show Serveroutput
DECLARE
V_lognum number;
V_NEEDARC number;
BEGIN
SELECT count (1) into V_lognum from V$log;
LOOP
LOOP
SELECT count (1) into V_needarc from v$archive;
IF V_needarc < v_lognum-1 Then
EXIT;
ELSE
Dbms_lock. SLEEP (60);
END IF;
END LOOP;

DELETE from T_user. T_table_name WHERE mobile= ' 13800138000 ' and rownum < 100;
IF sql%rowcount = 0 Then
EXIT;
END IF;
COMMIT;
END LOOP;
END;
/2. Write the calling script: $vim clear_dirt_data.shsource ~/.bash_profilesqlplus-s mytest/[email protected] <<EOF@data_del.sql; Exit Eof

This script, through the use of the Dbms_output.sleep () process, in the deletion process when the log file needs to reach the identified limit, so that the deletion process temporarily stopped, waiting for the arch process to archive log files before continuing, so as to avoid the archive log files too late to archive, The problem that caused the database to hang. This method works for each version of Oracle.

To delete a large table of millions records using PL/SQL