Oracle Scripts

For Oracle support & training call (800) 766-1884

Oracle Utilities - Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More

Utilities for General Administration

The Oracle supplied utilities have been loosely categorized throughout the course of this book. Some utilities are difficult to categorize, especially the more obscure and generic ones.

This chapter examines the utilities that a DBA can use to manage the Oracle environment. Utilities that pertain to killing threads, obtaining error messages, and altering session stack size will all be discussed in this chapter.

Terminating Threads with orakill

The orakill utility is provided only with Oracle databases on Windows platforms. The executable (orakill.exe) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database.

In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process.

Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the “Processes” tab of Windows Task Manager. Each session creates its own thread within oracle.exe and therefore, is not exposed to the Windows user. Killing the oracle.exe process in Windows would crash the entire database.

The orakill utility serves the same purpose as kill –9 in UNIX . The command requires the instance and the SPID of the thread to kill. The utility will display exactly how to obtain the SPID in the event the command was entered without parameters:

C:\oracle9i\bin>orakill

Usage: orakill sid thread

where sid = the Oracle instance to target
thread = the thread id of the thread to kill

The thread id should be retrieved from the spid column of a query such as:

select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr

If the statement suggested by Oracle (above) to retrieve the Thread ID is executed, the results below are displayed:

select a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.username is not null;

USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
AMOORE Alex 4760
DMOORE Dave 768

With the SPID for each user listed above, the session for any user can be killed.

C:\oracle9i\bin>orakill ORCL92 4760

Kill of thread id 4760 in instance ORCL92 successfully signalled.

SQL> select a.username, a.osuser, b.spid
2 from v$session a, v$process b
3 where a.paddr = b.addr
4 and a.username is not null;

USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
DMOORE Dave 768

2 rows selected.

Notice that SPID 4760, user AMOORE is gone.

Why does Oracle provide a utility to kill sessions from the DOS prompt, when a DBA could kill a user session from within Oracle? The following command will also kill the user session:

alter system kill session(sid, serial#);

The sid (session ID) and serial# above can be obtained from the v$session view. There are a couple of reasons a DBA might use orakill instead of the alter system kill session command.

1. The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.

2. A DBA may be unable to gain access to a SQL prompt due to a runaway query consuming all database resources. In this case, the session can be killed without ever logging in to the database.

These are good reasons to kill threads directly from the DOS prompt, but they do not address how to get the required kill information from the database if access is unavailable. How can a DBA obtain the SPID?

The above is an excerpt from Oracle Utilities - Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More by Rampant TechPress.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot.

Fast-Track Oracle Support
PO Box 511
Kittrell, NC 27544

Email BC: