Locally managed tablespaces and migrated rows

Or chaining, as we used to call it. Probably already answered, but I can't find anything in google.

Locally managed tablespaces with uniform extent allocation sound to me as a recipe for disaster when tables get many migrated rows.

Picture this:

1- Space is allocated in "chunks" (extents) of say, 64K.

2- These chunks are mapped back to a bit in a bitmap. Bit is either on (allocated) or off (free), no other info possible. Or else it's not a bit!

3- Punter creates tables with insufficient pctfree. Quite common in the case of third party apps like Peoplesoft, where DBAs are encouraged to behave like trained monkeys instead of rational humans.

4- The app changes the length of many rows in many blocks of the table.

5- Obediently, ORACLE will allocate from somewhere an extra block for each of the migrated rows when they get "bumped" off the end of the block. Where does this extra block come from? From one single bitmap bit for *EVERY* single row that gets migrated in each block? Ie, get one row migrated, loose 64K of free space?

6- If so, pretty soon punter starts complaining ORACLE is a disk space hog, there is no free space anywhere in his carefully crafted tablespace, initially sized with mathematical precision!

Now, I've been through all the info on locally managed tablespaces I could find, trying to dispel this fear of mine. Nothing has done it. Please someone more knowledgeable allay my fears?

Note that this is not a case of reuse of free space in the last allocated extent! We are talking potentially all blocks in table, not just the ones near the HWM where presumably the extent is not yet full and there are free blocks to use.

Does ORACLE do the intelligent thing and allocate the migrated blocks into free blocks near the HWM? And only allocate a new extent when needed, therefore adjusting again the HWM? I sincerely hope so...

And while I'm here: where the heck does ORACLE get the info for dba_free_space and dba_extents for a LMTS? It ain't from FET$ and UET$ for sure...

Many thanx for any feedback/experiences on this.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Mon May 07 2001 - 04:58:49 CDT