We have a visual basic application on MTS connecting to a Oracle database through DB Link on sun solaris using ADO'S.The Oracle database has global temporary tables.When ever there is an insert or update on the tables a trigger fires to populate the temp tables.Occassionally we are getting an oracle error a saying "trying to access the transactional temp tables which are already in use". If we do the updates through oracle sql we are not getting that error, but when we are going through the VB Application we are getting that error and that too not all the times. Can some one help me find what might be the problem.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Temp Tables
- Thread starter sumanchal
- Start date
SemperFiDownUnda
Instructor
Not sure if this is your error but here goes a little explanation.
Temp tables are normally destroyed by a database when a connection is released. Under MTS/Component Services Connection pooling actually doesn't release connections when the application calling the component in MTS releases the component ie
now without MTS you would do this and when ComObjectA is done with the connection the connection is broken and oracle cleans up/drops the temp tables
Application ---> ComObjectA ----> ADO Connection ----> Oracle
but with MTS what happens is
App ---> MTS ---> ComObjA ---> ADO Conn ---> Oracle
| ^ | ^
| | | |
| --------- |
----------------
Those loops underneath is showing that ComObjA requests a connection from MTS via the connection pool. When the object is down with the connection and sets it to nothing or it goes out of scope it is returned to the connection pool but this does NOT break the connection to Oracle. Oracle still sees the connection as open, becuase it is from MTS and the ComObjA only borrowed it for a bit. If a connection isn't requested for awhile MTS will destroy the connection and Oracle will clean up.
What does this mean? Well your objects are faster because they don't have to wait to establish a connection every time....BUT temp objects don't get destroyed automatically.
So to fix this just make sure that you destroy the temp objects explicitly instead of relying on them going out of scope.
Hope this helps
Temp tables are normally destroyed by a database when a connection is released. Under MTS/Component Services Connection pooling actually doesn't release connections when the application calling the component in MTS releases the component ie
now without MTS you would do this and when ComObjectA is done with the connection the connection is broken and oracle cleans up/drops the temp tables
Application ---> ComObjectA ----> ADO Connection ----> Oracle
but with MTS what happens is
App ---> MTS ---> ComObjA ---> ADO Conn ---> Oracle
| ^ | ^
| | | |
| --------- |
----------------
Those loops underneath is showing that ComObjA requests a connection from MTS via the connection pool. When the object is down with the connection and sets it to nothing or it goes out of scope it is returned to the connection pool but this does NOT break the connection to Oracle. Oracle still sees the connection as open, becuase it is from MTS and the ComObjA only borrowed it for a bit. If a connection isn't requested for awhile MTS will destroy the connection and Oracle will clean up.
What does this mean? Well your objects are faster because they don't have to wait to establish a connection every time....BUT temp objects don't get destroyed automatically.
So to fix this just make sure that you destroy the temp objects explicitly instead of relying on them going out of scope.
Hope this helps
- Status