Re: Global Temporary Table with column of type ROWID and Oracle Updates
Date: Wed, 17 Dec 2008 07:58:34 +0100
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 ): 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. ;-)
robert Received on Wed Dec 17 2008 - 00:58:34 CST