You Asked

I am trying to track down the source (ie. machine and program) of a SQL that is erroring out with an ORA-00600. When the session produces a trace file, it identifies the session using the SID, serial# combination : *** SESSION ID:(1346.55751) 2017-10-19 05:01:34.416. In the dba_audit_trail, I am auditing all connections from the user that is submitting the query. The logons and logoffs show the audsid and identify the machine and program. Is there a way to find the SID, serial# that corresponds to a specific audsid, or should I be doing this a different way?

and Connor said...

You trace file has the OS process in the name, eg

SQL> select p.TRACEFILE
  2  from v$session s, v$process p
  3  where s.PADDR = p.ADDR
  4  and s.SID = sys_context('USERENV','SID');

TRACEFILE
-------------------------------------------------------------
C:\ORACLE\diag\rdbms\db122\db122\trace\db122_ora_10128.trc

You can use that (plus any other details of relevance) to check dba_audit_trail, eg

SQL> select * from dba_audit_trail
  2  where os_process like '%10128%'
  3  and timestamp > sysdate - 3
  4  @pr
==============================
OS_USERNAME                   : XPS13\hamcdc
USERNAME                      : MCDONAC
USERHOST                      : XPS13
TERMINAL                      : XPS13
TIMESTAMP                     : 20-OCT-17
OWNER                         :
OBJ_NAME                      :
ACTION                        : 100
ACTION_NAME                   : LOGON
NEW_OWNER                     :
NEW_NAME                      :
OBJ_PRIVILEGE                 :
SYS_PRIVILEGE                 :
ADMIN_OPTION                  :
GRANTEE                       :
AUDIT_OPTION                  :
SES_ACTIONS                   :
LOGOFF_TIME                   :
LOGOFF_LREAD                  :
LOGOFF_PREAD                  :
LOGOFF_LWRITE                 :
LOGOFF_DLOCK                  :
COMMENT_TEXT                  : Authenticated by: DATABASE
SESSIONID                     : 821375
ENTRYID                       : 1
STATEMENTID                   : 1
RETURNCODE                    : 0
PRIV_USED                     : CREATE SESSION
CLIENT_ID                     :
ECONTEXT_ID                   :
SESSION_CPU                   :
EXTENDED_TIMESTAMP            : 20-OCT-17 09.43.53.391000 AM +08:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 10100:10128
TRANSACTIONID                 : 0000000000000000
SCN                           :
SQL_BIND                      :
SQL_TEXT                      :
OBJ_EDITION_NAME              :
DBID                          : 872342268
RLS_INFO                      :
CURRENT_USER                  : MCDONAC

PL/SQL procedure successfully completed.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OS PROCESS is the common ID

Brian Blades, October 20, 2017 - 12:57 pm UTC

Thanks Connor, I didn't think of that, just figured that the session IDs would have been the tie in. Using your process, I found the location of the autosys instance that was running job and the admins have fixed the query by moving it to the database where it should be running, rather than running it over a db link which doesn't allow them to select a LOB column (this was causing the error).

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Breadcrumb

Question and Answer