I created a locally managed tablespace with a uniform extent size of 1M. I moved several tables into this tablespace that had many different extent sizes. I thought that moving them into a tablespace with uniform extent sizes would alleviate the need to modify the storage parameters to 1M extents during the move. However, when I query dba tables I see that the initial and next extent sizes are not all 1M. Can anyone explain?
how did you move tables to locally managed tablespaces ???
You have to convert dictionary managed tabspace to locally managed tablespace using DBMS_SPACE_ADMIN package/import, removing table level storage clauses.
If you create a new table in a tablespace locally and uniform you can specify initial anyway; but, if you say 5M initial it won't create 1 extent of 5M instead it will create 5 extents 1M each one.
That's the way it works.
I hope I'm clear.
BTW. Moving a table carries the initial.
(See in dba_segments)
I guess only if the PCTINCREASE not set to Zero. I meant for Dictionary managed tablespaces as I assumed you were talking about 'em. GOOD point pando, as others might confuse with this posting under this subject.
Regarding coleascing space, it is right that SMON wakes up every 5 minutes, to coalesce the free space provided the tablespace's pctfree setting is not zero.This is done regardless of whether the new continogous space is needed or not.
But in a situation where Oracle tries to allocate an extent and is not able to find an extent of such size, it will still automatically coalesce the space, regardless of the pctfree setting for the tablespace.
This was mentioned in one of white papers on 'Stop Defragmenting and Start Living' by Oracle Corp.I found this white paper quite interesting and is availabe on Oracle site.