Oracle Utilities - Using Hidden
Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and
More
One way to obtain the Oracle SPID is to use a tool like QuickSlice from
Microsoft (free download) that will display Windows threads and their
IDs. The QuickSlice main screen (Figure 3.1) displays each executable
that is active in the operating system.
The DBA can use the main QuickSlice screen to determine the
CPU-intensive executables, and drill down into the threads for more
information.
The next QuickSlice window (Figure 3.2) displays the threads for a given
executable (oracle.exe). In the example below, TID 300 is using the CPU.
The DBA can quickly identify the most consumptive thread and decide what
to do. The TID column (Thread ID) in QuickSlice is a Hex value and
matches the decimal value for the spid column from v$session. In this
case, the TID consuming the CPU is 300 (Hex), which equals Session spid
768 (Decimal). Therefore, the command to kill this session would be:
C:\oracle9i\bin>orakill ORCL92 768
In this example, the thread (Oracle session) was killed in the operating
system without ever logging into the database. Before killing the
session, the DBA may decide to view the SQL being executed by the
session. This can be obtained by using the TID above (300) in the
following SQL statement:
select b.username, a.sql_text from
v$sqltext_with_newlines a, v$session b, v$process c
where c.spid = to_number('300', 'xxx')
and c.addr = b.paddr
and b.sql_address = a.address;
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.
