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
This way we avoid rollback segment contention and at the same time we also avoid frequent commit (instead of commit for every row).
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).
