Some scripts take a long time to run. If they are interrupted, you will lose all your work. To get around that, create a bash file to run the script for you, then run the script file with the NOHUP option so it won’t hang up.

Scripts and Running with NOHUP

  1. Make sure you have access to an account that can do what you want to do on the database. Do you need permissions on another schema? Do you need permissions to create any table? Do you need SELECT, INSERT, UPDATE or DELETE permissions on a particular table? 
    sql> grant insert, update, delete, select to schema.table_name to username;
  2. Make sure you can log into sqlplus using that username and its password. You may not have the right password. The password may need quotes around it because of special characters. 
    #> sqlplus username/"password"

    should bring you to…

    sql>
  3. Create the SQL script that you wish to run on the database.
    1. Begin with your main purpose. In your editor for your SQL file, create your code. Maybe you’re creating a table of distinct values from another table. 
      create table dup_vals as 
select desc, count(*) cnt from my_table group by desc;
    2. Do you want to see the output of your script? Then use spool as you normally would. Add it to the script. The file created will be create_dup_vals.log. 
      spool create_dup_vals.log create table dup_vals as select desc, count(*) cnt from my_table group by desc; spool off
    3. Add wrappers for handling errors. On the top, add the WHENEVERs to gracefully exit when you get any kind of os error (missing file, etc) or SQL error (can’t extend a tablespace, table not found, etc). Add exit to the bottom to provide the clean exit when an error is encountered or the script runs successfully. 
      WHENEVER OSERROR EXIT FAILURE; WHENEVER SQLERROR EXIT FAILURE; spool create_dup_vals.log create table dup_vals as select desc, count(*) cnt from my_table group by desc; spool off exit
    4. Anything else? Now’s the time to add it. Save your script and name it something useful. Something that I find useful is turning timing on. It reports after each execution how long that particular line took to execute. Put it before the spool line. 
      set timing on
  4. Create the Linux bash script to run the SQL Script. In your editor, you want to evoke sqlplus, pass it your username and password and tell it which sql script file to run. Very easy, just need two lines. The first one lets Linux know we’re using bash. The second is running the file. Save the file and exit the editor. Let’s assume you named it filename.bash. 
    #!/bin/sh
sqlplus username/"password" @sql_filename.sql
  5. Give filename.bash the proper permissions. You need it to be executable. 
    # chmod u+x filename.bash
  6. Run the file using NOHUP. That way, a separate, unattended session is created and will stay active even if you get disconnected. NOHUP has two keywords that make it run. This is how you run it, with NOHUP at the front and ampersand at the end. 
    # nohup ./filename.bash &

nohup.out log

One of the benefits of running nohup are not just the unattended execution. It also provides its own log, nohup.out. If there are any problems with the execution, check the nohup.out file first.

You can watch the nohup.out log as the script runs by tailing it.

-f means follow. Update what I’m seeing as the file is updated.

-n100 means show me the last 100 lines.

#> tail -f -n100 nohup.out

nohup Status Messages

Successfully Started and logging

[1] 99999
#> nohup: appending output to 'nohup.out'

Finished, for Good or Ill

If you get this message immediately after the first message showing it started, you may have a problem. Check the nohup.out log.

[1]+ Exit 1                nohup ./filename.bash &
Tagged: