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

From: Robert Klemme <>
Date: Wed, 17 Dec 2008 07:58:34 +0100
Message-ID: <>

On 16.12.2008 20:11, joel garry wrote:
> On Dec 16, 4: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.)

> Probably, but "Note:
> Segment shrink can be performed only on tables with row movement
> enabled. Applications that explicitly track rowids of objects cannot
> be shrunk, because the application tracks the physical location of
> rows in the objects.
> "
> From

I did not think of this, thanks for the valuable heads up! Currently I do not think that the table affected will benefit from shrinking as there is not much deletion (if at all) going on. But I'll surely check this!

> In other words, you are wiping out forever and ever, some new, useful
> administrative functionality, namely the ability to shrink segments,
> for some coding of unknown usefulness. Maybe your GTT is only
> temporary and your future DBA's won't ever want to shrink while it is
> running, I wouldn't know. But there's this Murphy fellow you might
> want to meet...

Oh, we're bumping into each other on a daily basis - so I'm all too familiar with this guy. ;-)


        robert Received on Wed Dec 17 2008 - 00:58:34 CST

Original text of this message