You can use Linux Nohup command to run any sqlplus command or shell script in background. Usually when a shell script is executed on a remote Linux machine connected over ssh, it takes a long time to finish. If the remote connection is aborted, the process/command will get killed. If you are not sure when the job will finish, its better to leave the job running in background even if the remote connected is aborted. This can be achieved using nohup as shown below:
STEP 1: Open vi editor and create .sql or .sh file:
For example: I have created one sql file using below commands to gather table stats in oracle:
# vi stats_gather.sql
select instance_name from v$instance;
EXEC dbms_stats.gather_table_stats(ownname => 'ORAHOW', tabname => 'CLDR', method_opt=> 'for all indexed columns size skewonly', granularity => 'ALL', degree => 8 ,cascade => true,estimate_percent => 10);
select instance_name from v$instance;
save it using esc [:] wq
STEP 2: Grant read,write and execute permission to the sql file
# chmod 755 stats_gather.sql
STEP 3: Finally run the script using nohup as shown below:
nohup sqlplus USERNAME/password@sid @file_name.sql &
STEP 4: Check the nohup.out file for any error or progress.
To run shell script using nohup use the below command:
#nohup ./filename.sh &
Sample shell script to delete huge records from a table.