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

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:b827d08e-4d09-4410-8ab9-952c515ee269_at_a29g2000pra.googlegroups.com... On Dec 16, 3:04 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Dec 16, 7:23 am, Robert Klemme <shortcut..._at_googlemail.com> 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 10.2.0.2) 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?

Yes.

> 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.

Thanks!

robert
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