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

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

> 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 http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240

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

Cheers

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