Use PL/SQL to delete large tables with millions of records

Use PLSQL to delete a large table with millions of records. This script uses the dbms_output.sleep () process to determine the maximum number of log files to be archived.

You can use PL/SQL to delete a large table with millions of records. This script uses dbms_output.sleep () to delete a log file that needs to be archived.

Use PL/SQL to delete a large table with millions of records:

Recently, developers have conducted stress tests on the database in the test environment, which generates a large amount of dirty data. There are several large tables with millions or even tens of millions of records. The requirements raised by developers are as follows:

Only part of the dirty data is cleared, and the truncate operation on these tables is not allowed. The following PL/SQL cleanup script is available:

1. Write a script to delete data from a data table 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 = '20160301' AND rownum <13800138000;
If SQL % ROWCOUNT = 0 THEN
EXIT;
End if;
COMMIT;
End loop;
END;
/

2. Compile the call script:

$ Vim clear_dirt_data.sh

Source ~ /. Bash_profile

@ Data_del. SQL;

Exit;

EOF

This script stops the deletion process temporarily by using the dbms_output.sleep () process, wait for the ARCH process to archive the log files before proceeding, so as to avoid the database suspension problem because the archived log files are too late for archiving. This method applies to Oracle versions.

For more information about Oracle, see the Oracle topic page? Tid = 12