Re: anyway to commit/rollback for another session?

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 16 Jul 2003 16:33:51 GMT
Message-ID: <3f157ba2.768376867@nyc.news.speakeasy.net>

Not quite sure what problem I am imagining, but here's a display of existance of a TX lock.

SYS user:

SQL> select * from v$lock where sid=255; no rows selected

DBLINK user:

SQL> select 1 from dual_at_remote;

SYS user:
1* select * from v$lock where sid=255
SQL> / ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---- -- ------- ------- ----- ------- -----

01A8B230 01A8B2FC 255 TX 262173 718487 6 0 166 0

DBLINK user:
SQL> rollback;

SYS user:
1* select * from v$lock where sid=255
SQL> / no rows selected

SAVEPOINT scenario:

dblink user:
SQL> savepoint before_dblink;

Savepoint created.
SQL> select 1 from dual_at_remote;

SYS user:
1* select * from v$lock where sid=255
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---- -- ------- ------- ----- ------- -----

01ABAFA8 01ABB074 255 TX 655383 703857 6 0 20 0

DBLINK user:
SQL> rollback to before_dblink;

Rollback complete.

SYS user:
SQL> / ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---- -- ------- ------- ----- ------- -----

01ABAFA8 01ABB074 255 TX 655383 703857 6 0 73 0

DBLINK user:
SQL> rollback
2 /

Rollback complete.

SYS user:
SQL> / no rows selected

On Tue, 15 Jul 2003 16:44:18 -0700, Daniel Morgan <damorgan_at_exxesolutions.com> wrote:

>NetComrade wrote:
>
>> On 14 Jul 2003 19:27:46 -0700, Mark.Powell_at_eds.com (Mark D Powell)
>> wrote:
>>
>> >
>> >Netcomrade, I do not believe there is any way to commit or rollback
>> >for another user's session.
>> >
>> >Are you sure that the distributed applications 1- all got changed, 2-
>> >that the changes got moved into production, and 3- in the case of
>> >desktop software all users received the new version?
>>
>> Mark,
>> I've read and re-read your questions, and I think you misunderstood my
>> problem.
>> All distributed SELECTS require a TX lock which can only be taken off
>> by a commit. All DML is committed already, I just want to commit for
>> idle sessions that could potentially be preventing a rollback segment
>> from recycling.
>> .......
>> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>> remove NSPAM to email
>
>The answer to your question ... is No!
>
>But I should add that unless you have evidence to the contrary I don't
>think the problem you imagine can possibly exist and am inclined to refer
>you to the Concepts and Architecture manuals.
>--
>Daniel Morgan
>http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
>damorgan_at_x.washington.edu
>(replace 'x' with a 'u' to reply)
>
>

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Jul 16 2003 - 11:33:51 CDT