Imagine that you are going home, but before you go, you start some script (bash, Oracle SQL*Plus, or PL/SQL procedure) for which you didn’t know or
didn’t think in advance that job you start to execute can take a long time to finish, and can’t be interrupted.

In this blog I’m assuming you are running Oracle database 11g on IBM AIX operating system (IBM version of System V UNIX).

Procedure is the same if you just have AIX without Oracle Db.

Linux doesn’t have such functionality (I mean proctree). It has something much better, which I’m going to describe in one of the future blogs.

From the first terminal connect to the target database, and start some long running process. This can be some bash script or some long running
process from the SQL*Plus.

In this case we are collecting database statistics which will probably take long enough time to finish.


SYS@test_db> exec dbms_stats.gather_database_stats;

Open a new terminal and find the session info for statistics gathering.

In this case (as it is test system), I was the only user connected as a system

Open a new terminal and find the session info for statistics gathering.

In this case (as it is test system), I was the only user connected as a system


SYS@test_db> select sid, serial# from v$session where username = 'SYSTEM';

In production system, in case of long running Oracle script or procedure you can use the following:


select * from v$sql_monitor where status = 'ACTIVE' and username = 'SYSTEM';

You can add additional criteria (client_id, service_name, program etc.) to narrow the number of rows returned.

Usage of v$sql_monitor require 11g or newer version of Oracle database.

From the other terminal, find PID of the process which is gathering Db statistics

root@tst_db:/> ps -ef | grep sqlplus
oracle 12124222 21627018 0 10:07:37 pts/1 0:00 sqlplus
root 32374870 56623142 0 10:08:40 pts/2 0:00 grep sqlplus

Now when I know the PID, I can use proctree to see the process tree for the process of interest:

root@tst_db:/> proctree 12124222
3997880 /usr/sbin/srcmstr
28966914 /usr/sbin/sshd
38404252 sshd: tstuser [priv] v]
56492096 sshd: tstuser@pts/1 1
50069684 -bash
24576004 -bash
21627018 -bash
12124222 sqlplus

This will confirm for which process I need to send nohup signal (process which is running sqlplus).

This step is necessary as in some cases (especially with the bash scripts) it is necessary to find the correct PID, not a subshell.

In cases like that it can be useful.

echo $$

to get the parent of the subshell.

But as I said, you didn’t think in advance that your job will take a long time to finish and you are in hurry.

The last step is to send nohup to the correct process, for example:

nohup -p 12124222

After that you can close the terminal that executes database statistics gathering.

Be careful!!!

Do not use Ctrl+C to close running terminal, just click on close button.

Otherwise your job will be killed.

From the new terminal (or SQL Developer or any toll you are using), check that job is running, even when you close the terminal.


SYS@test_db> select status from v$session where username = 'SYSTEM' and sid = 554;

STATUS
------------------------
ACTIVE

As you can see, job is still running, and you can go home in peace.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.