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

From: ddf <oratune_at_msn.com>
Date: Tue, 16 Dec 2008 07:55:38 -0800 (PST)
Message-ID: <61ff10a5-a34d-4b52-93b1-ec0cbd659882@t3g2000yqa.googlegroups.com>


On Dec 16, 8:46 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> 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

It's interesting to note that the CHAINED_ROWS table, when present, experiences no 'problems' during an upgrade, and it contains a column of type ROWID.

David Fitzjarrell Received on Tue Dec 16 2008 - 09:55:38 CST

Original text of this message