Posted on 2006-04-14
Medium Priority
Last Modified: 2011-08-18

The two databases that I inherented have been generating trace files and log the above error. The OS is Window 2000 SP4. The database version is Since it is a production environment, update to a newer version is next to imposible. The following is excerption from one of the trace file. The DB user issue the sql statement is system. It seems this does not fit into the possible causes from this link:


It seems on the server side. I seems most time it happens right after completing a archive log.  My questions are

1) how could I find out which process or job issue the sql statement?
2) how could I prevent it from happening?

I contacted Oracle support as well, but their suggestion is to upgrade to version  Does this mean there is bug in version Where is the bug doc or fix? If it is a bug, I need to provide to our validation team so that they can approve an upgrade.



---- from alert log -----
Thu Apr 13 02:54:27 2006
ARC0: Evaluating archive   log 8 thread 1 sequence 39636
ARC0: Beginning to archive log 8 thread 1 sequence 39636
Creating archive destination LOG_ARCHIVE_DEST_1: 'K:\ORACLE\ORADATA\D01\VP_ARCH_39636_1.ARC'
ARC0: Completed archiving  log 8 thread 1 sequence 39636
Thu Apr 13 03:44:55 2006
Errors in file f:\oracle\admin\vp\udump\vp_ora_92148.trc
Thu Apr 13 04:19:55 2006
Errors in file f:\oracle\admin\vp\udump\vp_ora_90920.trc
----- from a trace file -----
*** 2006-04-13 04:19:55.000
*** SESSION ID:(36.13376) 2006-04-13 04:19:55.000
*** 2006-04-13 04:19:55.000
ksedmp: internal or fatal error
Current SQL statement for this session:
select /*+ Rule */ sum(f.bytes)/1024, floor(sum(f.bytes)/1024) from sys.dba_free_space f, v$datafile d where f.file_id = d.file# and d.status in ('ONLINE', 'SYSTEM') and tablespace_name =  'SYSTEM' group by tablespace_name
Question by:geotiger
  • 4
  • 3
LVL 48

Expert Comment

ID: 16478532
May be you have read this, but I post it.

cause: The most common cause for the above error is an ungraceful
disconnection of a session from the oracle db while the db is currently running
a dml statement issued by that session.  The error is recorded when oracle
attempts to reply back to the session with the results of the dml and cannot
access the session.  Overall database operations are usually not affected.

An ungraceful disconnection could cause by but is not limited to any of the
- the client machine crashed
- the network connection crashed
- the user exited the session improperly (not issuing the 'exit' command)
- the user application allows the user to exit the application without properly
terminating the session.

The above can cause problems with corrupted rollback segments if occurring on a
regular basis and is not addressed.  This would require db recovery and
possibly a db rebuild (not a light matter)

PMON will usually rollback most transactions in the rollback segments for a
session if it finds that the session has been ungracefully disconnected, but
there is always a chance that it cannot and this will lead to rollback segment


The dml and the user that issued the dml can be determined from the trace file.
 The current dml is in the tracefile header section. The user can be found in
the process state dump of the trace.  The process state shows the machine,
o/suser, and user for the session.

The DBA can use this information to determine what the user was doing at the
time and if there was an ungraceful exit from the session the user was

The DBA should then address the cause of the ungraceful exit to reduce the
possibility of recurrence.

LVL 12

Author Comment

ID: 16479894

Yes, I have read it and it is referenced in the link that I posted. The application connecting to the database is Java application running on WebLogic application server. The Java app creates 10 dedicated connections to Oracle database when it starts, then use the 10 connection as a pool. How would Oracle handle the pool if most of the dedicated connections are idles for long time and then suddenly gets very busy?  
LVL 48

Accepted Solution

schwertner earned 2000 total points
ID: 16484877
Yes, definetely you have to upgrade ASAP to
I missed this by the first read. (also 10.1.....) are so bugy! has terible errors even in the SQL machine, terible errors.
But in the analytic functions also has bugs....
Curiously the 'set of the bugs' is different in Windows and Unix/Linux versions.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Author Comment

ID: 16682025

After checking Oracle's bug database for version, it seems that our case does not fits with anyof the descriptions in the bug database.

Since most of the trace files are generated by dbsnmp.exe (Oracle agent), I stopped unwanted/unused services on Oracle server including Oracle Apache server, Oracle agent, MTSrecoveryService, Distributed Transaction Coordinator services. It seems that Oracle stopped generating the annoying trace files.

We plan to upgrade Oracle to with new Java application release.  


LVL 12

Author Comment

ID: 17183672

This is just an update. I have not seen the error message nor the trace files since I turned off those services.


LVL 48

Expert Comment

ID: 17184846
I insist in that is too bugy
and it is risky to use it.
Even has bugs.

Good luck!
LVL 12

Author Comment

ID: 17187335

I agree.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question