How to Run an Oracle SQL Script Using nohup on Linux

You should run a shell with nohup on Linux to ensure long-running tasks or processes continue to execute even if your user session is terminated or a network connection is lost.

For instance, when managing an Oracle Database, you often need to execute tasks that can take a significant amount of time, such as data loading, database backups, or maintenance operations.

Using nohup in conjunction with these tasks ensures that they will continue to run in the background, even if you log out, the terminal is closed, or there is an unexpected disconnection.

By doing so, you can avoid the risk of incomplete or failed operations due to unintended interruptions, ultimately increasing the reliability and stability of the Oracle Database system.

You can run your script with nohup

nohup sqlplus '/ as sysdba' @my_script.sql > my_script.out 2>&1 &

Or you can also create a bash script, just modify your_sql_file.sql with your .sql file

#!/bin/bash

# Set the Oracle environment variables
export ORACLE_HOME="/path/to/your/oracle/home"
export PATH="$ORACLE_HOME/bin:$PATH"
export ORACLE_SID="your_oracle_sid"

# Set the SQL file path
SQL_FILE="your_sql_file.sql"

# Set the output file name based on the SQL_FILE with a .log extension
OUTPUT_FILE="${SQL_FILE%.*}.log"

# Run the SQL commands using sqlplus inside a nohup command
if [ -f "$SQL_FILE" ]; then
  echo "Executing $SQL_FILE in nohup mode..."
  
  nohup bash -c "sqlplus / as sysdba <<EOF > $OUTPUT_FILE 2>&1
    SET ECHO ON;
    SET SERVEROUTPUT ON;
    SET TIME ON;
    SET TIMING ON;
    @$SQL_FILE;
    EXIT;
EOF" &

  echo "SQL file execution started in nohup mode. Check $OUTPUT_FILE for progress and results."
else
  echo "Error: SQL file not found"
fi

You should give it execution permission.

chmod +x execute_sql_nohup_oracle.sh

Then you run the script with

./execute_sql_nohup_oracle.sh