Re: Global Temporary Table with column of type ROWID and Oracle Updates

From: gym dot scuba dot kennedy at gmail <>
Date: Tue, 16 Dec 2008 16:00:46 GMT
Message-ID: <OgQ1l.3$>

"Robert Klemme" <> wrote in message On Dec 16, 3:04 pm, Ed Prochak <> wrote:
> On Dec 16, 7:23 am, Robert Klemme <> wrote:
> > I have proposed a solution which uses a global temporary table to
> > remember an updated record via its rowid. Concerns have been risen
> > that this will cause trouble in case of an update of the Oracle
> > version (currently to another version with a changed format
> > for ROWID type columns. My reply was that during an upgrade of DB
> > software there will be no segments of the global temporary table
> > because of the way Oracle allocates segments (see [1]): only on first
> > insert in a session a segment will be allocated and consequently only
> > then data blocks will be formatted. So there should be no issue at all
> > even if the format of ROWID changes. Is my reasoning correct? (I
> > could not find anything relevant to this in metalink.)

> I'm not clear on the question. Is the GTT holding the ROWID from
> another table?


> I think that would be okay. If it's the other way
> around, i.e, some table holding a ROWID pointing to the GTT, then I
> would agree with the concern.

That would be a moot design anyway: I mean, where is the point in permanently storing a reference to something that is gone after the TX completes or the session dies? :-)

> Seems to me a global temporary table would be empty during any DB
> upgrade.

Certainly. The main concern of my colleague was that the presence of the rowid typed column might cause issues like ORA-06502 after DB upgrade. But since I have seen Oracle touch complete datafiles during an upgrade I would imagine that even rowid typed columns in "ordinary" tables would be adjusted.

> I agree that ROWIDs should be used carefully.


I agree with you. If you want to be very careful you could drop the gtt before the upgrade and recreate it afterwards. Jim Received on Tue Dec 16 2008 - 10:00:46 CST

Original text of this message