yup, good thread, tasty too. I'm not an Oracle DBA, but I did stay at a Holiday Inn Express last night :)
We use Toad here and I have been given access to a few things, one of which is the tables allowing me to use DBS > Kill/Trace Session in Toad. This really rawks, I can see the SQL in action and all. This is how I disoverd all this stuff a few months back. We had some issues with load and leaving connection pooling on for DSNs. I even setup a test page with a meta refresh in it and ran it in like 10 browser sessions and watched the connections come and go. uber schweet. <quote> "So here is a completely random question. How can a user kill thier own session in Oracle without having priveleges to kill everyones session? (i.e. if they logout of the cf app, can we kill that user's session through a procedure rather than waiting for the session to timeout)" </quote> hmm, if you have connection pooling off in CFs DSN settings, this happens immediately. Otherwise I suppose you could run an SP upon logging out that kills it, eh? Or just set the timeout sweeper thingy to run more often. How often do users actually click logout though, eh? I've never used Oracle for so much security though, 5k users! sounds like much to manage. DK On 6/8/05, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > > Interesting stuff. Both of you guys have been a wealth of good > information. There isn't much published on the inner working between > CF and Oracle. DataDirect has given me very minimal details when I > ask. > > 1) Totally didn't realize he was using two different servers there. > Although I'd like to run a test querying v$lock and actually see the > locks, rather than just assume due to the time it takes to process. > > 2) This is really urking me, because my DBAs are saying the opposite, > even in light of this last post. The way I see it, we are basically > saying that all clients/drivers that access Oracle just all agreed > that upon completion of stored procedure a commit is then executed. > Everything I've read today is saying just that, except for SQL*Plus. > Which only performs an implicit commit on quit/exit and of course DDL. > > Anyways, tons of good info on this tread. > > So here is a completely random question. How can a user kill thier own > session in Oracle without having priveleges to kill everyones session? > (i.e. if they logout of the cf app, can we kill that user's session > through a procedure rather than waiting for the session to timeout) > > -Adam > > On 6/8/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: > > 1) huh? each connection to Oracle from CF creates a new session. If you > > happen to have connection pooling on in the ColdFusion DSN settings, > then > > yes you would only see a fixed set of sessions as they are reused, > having > > this set to one would get what you are seeing perhaps. If you have > > connection pooling off in CF for the DSN, then you could see only one > > session in the kill/trace view in Oracle IFF there is only one > connection to > > Oracle from CF whilst you are looking. In fact, I have connection > pooling > > off for several DSNs on a server. Say the DSN is named FOO. I can look > in > > Oracle and see multiple sessions for FOO that die as soon as the > ColdFusion > > request containing the CFQUERY/CFSTOREPROC call is done. > > > > Now, what I have not figured out yet is, if you have connection pooling > off > > for a DSN, call it GOO, and you have two CFQUERYs in a single CF page, > does > > one connection/sessioon get used for both or are there two > > connections/sessions? If its two seperate requests to the same CF page, > then > > yes, there are two seperate and distinct connections/sessions. I just > don't > > know at the page level. > > > > 2) a comment from my DBA here 'Yup, that comment is dead wrong. Oracle > never > > inherently auto-commits inside or after a SP, unless it contains a DDL > > operation (create index, analyze table, grant privs, etc).' I'm agreeing > > with tanguy below, we have seen this behaviour. CF automagically appends > > 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' to the end of each > > statement passed unless you use CFTRANSACTION. This includes calling > SPs. > > Now, if you have a commit withing the body of your SP, this kind of > bypasses > > what CF can be concerned about, eh? > > > > > > DK > > > > On 6/8/05, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > > > > > > 1) No. Not true. I have a public website that has over 5k oracle > > > users. 1 user account is used for the public (ie. people who do not > > > log in). They all share the same session. When you look at active > > > sessions in oracle there is only 1 entry for the public account. > > > Unless that account is connecting from a different client toad/cf. CF > > > passes the same username/password to oracle for the public account, > > > oracle has no way to determine that different requests are different > > > sessions, its all based on the client used to connect (dataDirect > > > JDBC) and the username/password. > > > > > > 2) Well thats kinda what I'm wondering. I think the commit occurs > > > directly following the </cfstoredproc> tag, because Oracle > > > automatically commits when _any_ stored procedure finishes without > > > exception. Regardless if CF made the procedure call or an Oracle Forms > > > app did. I don't think the <cftransaction> tag can modify the > > > procedure call to tell oracle not to commit. Even if it could, would > > > it be able to chain that to all procedures that were internal to the > > > initial procedure call? > > > > > > -Adam > > > > > > On 6/8/05, RADEMAKERS Tanguy <[EMAIL PROTECTED]> wrote: > > > > Adam, > > > > > > > > 1) Even with the same username/password, oracle treats it as two > > > > sessions. If you have TOAD you can verify this with the session > browser. > > > > > > > > 2) stored procs don't change the transaction handling of oracle > (unless, > > > > or course, you manually commit within the body of the proc) - ie cf > > > > handles cfstoredproc just like cfquery: by default it will commit > after > > > > each </cfstoredprocedure> unless you use <cftransaction>. > > > > > > > > /t > > > > > > > > >-----Original Message----- > > > > Subject: FW: Get New Record ID in ORACLE > > > > >From: Adrocknaphobia <[EMAIL PROTECTED]> > > > > >Date: Wed, 8 Jun 2005 10:52:48 -0400 > > > > >Thread: > > > > >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages > > > > >&threadid=40594&forumid=4#208966 > > > > > > > > > >James, > > > > > > > > > >Interesting test. This strongly contradicts what our DBAs have been > > > > >telling me. I'm going to bring this up with them. (I love when they > > > > >are proved wrong!). The only thing is that you are using only a > single > > > > >session. ie. same user to perfrom both actions since I'm assuming > u/p > > > > >is hardcoded into your DSN. Although you get all sorts of errors > with > > > > >cftransaction if they aren't hardcoded. > > > > > > > > > >What about with stored procedures? I think the second a procedure > is > > > > >completed and returned the commit is automatically applied, > regardless > > > > >of the cftransaction. So is it safe to say that cftransaction > should > > > > >only be used with cfquery and not cfstoredproc? > > > > > > > > > >-Adam > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209060 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
