Database Reference
In-Depth Information
The figure after the pound sign (
#
) is the cursor number of the failed statement. The reason
for the failing
INSERT
statement in the preceding example was “ORA-00372: file 4 cannot be
modified at this time”. The tablespace containing the table
POEM
had status read only. The
parameters of the
ERROR
entry are in Table 24-17.
Table 24-17.
ERROR Parameters
Parameter
Meaning
err
Error number
tim
Timestamp in centiseconds (appears to be 0 at all times in Oracle9
i
, but has a
meaningful value in Oracle10
g
)
Sometimes applications do not report ORACLE error numbers or error messages. Under
such circumstances, it is very worthwhile to do a level 1 SQL trace, to discover which error the
DBMS throws. It is even possible to find out when an error occurred by looking at timestamps
and
tim
values in the trace file. Near the header of each trace file is a timestamp such as this:
*** SESSION ID:(150.524) 2007-06-22 15:42:41.018
After periods of inactivity, the DBMS automatically writes additional timestamps with the
following format into the trace file:
*** 2007-06-22 15:42:51.924
Intermittent timestamps can be forced by calling
DBMS_SYSTEM.KSDDT
. Running the following
script will generate two errors which are 10 seconds apart, due to a call to
DBMS_LOCK.SLEEP
:
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER TABLESPACE users READ WRITE /* is read write, will fail */;
SQL> EXEC dbms_lock.sleep(10) /* sleep for 10 seconds */
SQL> EXEC dbms_system.ksddt /* write timestamp to trace file */
SQL> ALTER TABLESPACE users READ WRITE /* is read write, will fail */;
The resulting Oracle10
g
trace file is shown here (excerpted):
1 *** SESSION ID:(150.524) 2007-06-22 15:42:41.018
2 PARSING IN CURSOR #1 len=55 dep=1 uid=0 oct=3 lid=0 tim=176338392169 hv=1950821498
ad='6784bfac'
…
3 EXEC #1:c=20028,e=208172,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=176339220462
4 ERROR #1:err=1646 tim=17633550
…
5 PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=47 lid=0 tim=176339264800
hv=2252395675
ad='67505814'
6 BEGIN dbms_lock.sleep(10); END;
7 END OF STMT
8 PARSE #1:c=0,e=140,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=176339264790
