Re: oracle JDBC - how to detect uncommitted work on an open Connection ?

exception handled:

'or by a user that has privileges granted by SYS'

yes, there are risks, which i should have mentioned, but it is sometimes necessary to do things in SYS -- just like it's sometimes necessary to actually edit the windows registry

not all users have privileges on the V_$ views, which are owned by SYS -- so, how do you grant selective privileges on V$ stuff? grant SELECT_CATALOG_ROLE and let the user have access to all V$'s and DBA views?

so, for a more carefully advised approach (assuming there isn't some other mechanism to accomplish the task, i.e., that i'm not overlooking some normally available stat, v$, or function)

[_] create a user name something like SYS_EXTENSIONS with basic privileges (create session, create view, create public synonym)

[_] connect as SYS
[_] grant select on v_$session to sys_extensions;
[_] grant select on v_$transaction to sys_extensions;
[_] connect as SYS_EXTENSIONS
[_] create the view based on the sys V_$ views
[_] create a public synonym for the view
[_] grant select on the view to public

• mcs

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067613108.215966_at_yasure...
> mcstock wrote:
>
> >i can't think of any default way to find out about open transactions, but
> >you could create a view (either owned by SYS or by a user that has
> >privileges granted by SYS like this:
> >
> >CREATE OR REPLACE VIEW MY_TRANSACTION ( SID,
> >START_SCNB, START_SCNW ) AS select
> >s.sid
> >, t.start_scnb
> >, t.start_scnw
> >from
> > v$session s
> >, v$transaction t
> >where
> >s.saddr = t.ses_addr
> >and
> >s.AUDSID = USERENV('SESSIONID')
> >
> >then create a public synonym and grant select to public -- selecting from
> >the view will then show if there is an open transaction.
> >
> >-- mcs
> >
> >"Edoardo" <e.comar.no.spam_at_no.spam.btinternet.com> wrote in message
> >news:bns3dh$ckk$2_at_hercules.btinternet.com...
> >
> >
> >>Hi
> >>
> >>is there a way, using Oracle's JDBC drivers to find out if a given
> >>java.sql.Connection has uncommitted work pending on it ???
> >>
> >>the reason is, close() will commit work if there is any - while if the
> >>close happens as a cleanup action after an error, it would be desirable
> >>to rollback (but I don't want to rollback always before close).
> >>
> >>TIA.
> >>Edoardo Comar
> >>www.choreology.com
> >>
> >>
> >>
> I take exception to any advice telling someone to create an object as SYS.
>
> If all someone wants is open transactions there are a boatload of V_$
> magic views that
> provide all of that information.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Fri Oct 31 2003 - 14:27:03 CST