Error tracing

[Issanov]

Hi

I have an application working with Oracle database,
and I don't have access to source code.
Some transactions fail but this app doesn't generate
any logs.
How can I trace errors occuring while executing
SQL statements, session initialization, etc?
All I need is to get ORA error number and SQL code
that caused the error.

Thank you
Dmitri

 

[MikeJones]

Firstly if the Application is written in PL/SQL you may be able to see the source in DBA_SOURCE, so long as the code hasnt been wrapped.

Otherwise you can trace the session and then tkprof this to see the reults. You need to find out the SID anD Process of the user, I use the following SQL, It's a bit long winded and contains lots of extra info you don't need but hey its free from me.

SET TERM /WID=132
SET LINESIZE 132
SET PAGESIZE 40

COLUMN USRNAME FORMAT A13 HEADING 'Oracle User'
COLUMN OSUSR FORMAT A13 HEADING 'VMS User'
COLUMN CMD FORMAT A18 HEADING 'Command'
COLUMN ID FORMAT A8 HEADING 'User Id'

SELECT SUBSTR(S.OSUSER,1,13) OSUSR ,S.SID||','||S.SERIAL# ID,
SUBSTR(S.USERNAME,1,13) USRNAME,
P.SPID OS_PID,
DECODE (s.COMMAND,
0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE Db LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CTL FILE',
58,'DROP PROCEDURE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PKG BODY',
98,'ALTER PKG BODY',
99,'DROP PKG BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUB SYNONYM',
111,'DROP PUB SYNONYM',
112,'CREATE PUB DATABASE LINK',
113,'DROP PUB DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS') CMD,
SUBSTR(S.PROGRAM,1,40)
FROM V$SESSION S, V$PROCESS P
WHERE S.TYPE = 'USER'
AND S.PADDR= P.ADDR
ORDER BY S.OSUSER,S.SID
/

You need to identify the session you are interested in from this so make sure that its the only OSUSer/DBUser combo logged in. Note the user id (2nd Column)

Then log in as a dba and do an alter system trace_session(user_id) including the conmma)

Do your transactions on the application so it errors

In $ORACLE_HOME/rdbms/log will be a trace file fgor that session called ?????????.trc

run tkprof on it..

tkprof username/password trace_file output_file or something like that...

then look at the output file, it should have all the commands in cronological order, with the erroing one at the end.


Mike.

 

[Issanov]

Thanks, it helps!

 

[Issanov]

Will failed transaction show up in
trace file or just errors like SQL parsing
error, etc?

 

[MikeJones]

They should both show up. Well the second may show up as a "Unable to find program unit" error as it will not have been successfully compiled into the database. Have you tried logging in as a dba and doing a:

select * from dba_errors

This will show you any duff code in the database.

 

[raygg]

what's "duff code" ?

 

[MikeJones]

Sorry by "Duff Code" I mean code that has not successfully compiled into the database.