Delete multiple rows from a table

Today we have face one issue, We have to delete the 25930814 rows in a table of size 23 gb. We just followed like below.

Going to further first we have to take the export of that table.

$ expdp directory=data_pump_dir dumpfile=table.dmp tables=<username.tablename> logfile=table.log

Here table export backup is needed because if anything went wrong we will import that table.

Once the table export is completed prepare a shell script that will commit the data after every 1 lac rows deleted from the table.

#!/bin/bash

export ORACLE_SID=raj
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export PATH=/u01/app/oracle/product/11.2.0.3/db_1/bin:$PATH

sqlplus -s "/ as sysdba" <<EOF

set feed off
set pages 0
BEGIN
LOOP
DELETE FROM <username.tablename> where <condition> AND ROWNUM <= 100000;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
END;
/
exit
EOF

Run the script using the nohup command.

$ nohup ksh delete.sh > delete.out &

$jobs -l

[1]+ 11228 Running                 nohup ksh delete.sh > delete.out &

This way we avoid rollback segment contention and at the same time we also avoid frequent commit (instead of commit for every row).