fnd_log_messages: get a handle on what your apps dbas are up to

OK. The title is only half-serious. Half. The FND_LOG_MESSAGES table is actually a pretty useful source of info about what’s happening on EBS. Here’s how to query it.

First things first. The structure of FND_LOGS_MESSAGES:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MODULE                                    NOT NULL VARCHAR2(255)
 LOG_LEVEL                                 NOT NULL NUMBER
 MESSAGE_TEXT                              NOT NULL VARCHAR2(4000)
 SESSION_ID                                         NUMBER
 USER_ID                                   NOT NULL NUMBER
 TIMESTAMP                                 NOT NULL DATE
 LOG_SEQUENCE                              NOT NULL NUMBER
 ENCODED                                            VARCHAR2(1)
 NODE                                               VARCHAR2(60)
 NODE_IP_ADDRESS                                    VARCHAR2(30)
 PROCESS_ID                                         VARCHAR2(120)
 JVM_ID                                             VARCHAR2(120)
 THREAD_ID                                          VARCHAR2(120)
 AUDSID                                             NUMBER
 DB_INSTANCE                                        NUMBER
 TRANSACTION_CONTEXT_ID                             NUMBER
 ECID_ID                                            VARCHAR2(4000)
 ECID_SEQ                                           NUMBER
 CALLSTACK                                          CLOB
 ERRORSTACK                                         CLOB

Next, a simple query to dump it’s contents for the current date:

SQL> select timestamp,module,message_text
 from apps.fnd_log_messages
 where timestamp like sysdate;

A little neater formatting could be done by formatting as a .csv:

SQL> select timestamp||','||module||','||message_text
 from apps.fnd_log_messages
 where timestamp like sysdate;

You could also try changing some default page settings:

set newpage 0
set linesize 80
set pagesize 0

and labelling your columns:

column timestamp heading 'TIME'
column module heading 'MODULE'
column message_text 'MESSAGE'

As usual though, my favorite turns out to be HTML formatted output, but be warned this may take a long time to generate — and the resulting file may be too big for your browser to load.