Re: Find SID for oracle.exe

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 4 Mar 2007 18:53:37 -0800
Message-ID: <1173063217.000204.204310@p10g2000cwp.googlegroups.com>

On Mar 4, 10:07 am, "astalavista" <nob..._at_nowhere.com> wrote:
> Hi,
>
> On windows I have several instance,
> if look at the task manager I see several oracle.exe.
> Is there a simple way to find the SID of each oracle.exe
>
> Thanks in advance

An unconventional way to find the Oracle exe associated with an instance is to place a load on the instance, and see which oracle.exe increases CPU usage. One certain way to do that is to create a Cartesian join between two or more tables with a significant number of rows.

Here is another way that you can place a temporary load on a database instance (watch for word-wrap in the SQL statement):

CREATE TABLE T1 (G VARCHAR2(50)); INSERT INTO
  T1
SELECT
  P1||'-'||P2 G
FROM
  (SELECT TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('10000000','XXXXXXXX') +1,TO_NUMBER('FFFFFFFF','XXXXXXXX')+1)),'XXXXXXXX')) P1,     TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('1000','XXXX') +1,TO_NUMBER('FFFF','XXXX')+1)),'XXXX')) P2   FROM
    DUAL
  CONNECT BY
    LEVEL<=1000000);

ROLLBACK; DROP TABLE T1;

This might also work:
Edit the columns displayed in Task Manager to include the Threads column.
Sort the list in Task Manager by Image Name (the exe) Use SQLPlus to connect to the database instance that is of interest. The thread count should increase in Task Manager.

Another possible solution:
On the server, open a command prompt and type:   NETSTAT >C:\NSTAT1.TXT
>From another computer, make a new SQLPlus connection to the database
instance that is of interest.
On the database server again, type the following at a command prompt:   NETSTAT >C:\NSTAT2.TXT
  NETSTAT -B >C:\NSTAT3.TXT

  NOTEPAD C:\NSTAT1.TXT
  NOTEPAD C:\NSTAT2.TXT
  NOTEPAD C:\NSTAT3.TXT

Compare NSTAT1.TXT with NSTAT2.TXT to determine the port that was just connected to the remote computer:
Proto Local Address Foreign Address State TCP DBSERVER_1:1589 AIRFORCE_2:40000 ESTABLISHED Now, find the port in the NSTAT3.TXT file: Proto Local Address Foreign Address State PID TCP DBSERVER_1:1589 AIRFORCE_2:40000 ESTABLISHED 504 [oracle.exe]
You now have the PID of a particular database instance (504 in my example).

There are probably other ways to find the PID using Oracle supplied packages, oradebug, and possibly the Oracle performance counters.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Mar 04 2007 - 20:53:37 CST