Re: SQL challenge - an undocumented feature.

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Wed, 09 Jun 1999 00:11:29 -0400
Message-ID: <375DE971.ADAD6276@bigfoot.com>

Yes - I'll try - I'm actually at an Oracle class this week so I'll do it from memory
until I get back in the interests of keeping the thread going.

I have a 7.3.3. database on AIX 4.2 called MERGE. I fire up a SQL*Plus session, and then execute like the following

create table doug (a number);
insert into doug values (userenv('commitscn')); select * from doug;

A

---

7783823

Now, there's a possibility that no one else is on the system, so I fire up another SQL*Plus sesssion, and I say create table x as select * from version. (about 1000 rows).
I type commit;

I go back to the session where I used commitscn, and type
select * from doug;
and get
A

---

7783823

Now, even if I type commit right now in this session - commit;
It's still
A

---

7783823
Until I do the same thing in THIS session, like create table xy as select * from version;
commit;
Then it goes up - like
A

---

7784234

That's about it from memory - I do find this interesting though and will try to follow up with a literal blow by blow..

• Dc.

Jonathan Lewis wrote:

> Could you post, or email, a step by step
> description of what you do.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <37580126.7E4F2884_at_bigfoot.com>...
> >This only works for me if I'm in the same session. If I go into another
> >session
> >and do some transactions, then return to the first one, even if I do a
> commit;
> >
> >the number doesn't change..
Received on Tue Jun 08 1999 - 23:11:29 CDT