No I am not but I do not have the exact date when the tablespace was converted from dictionary to locally managed and wanted to do this on many databases (more than 50) so wanted to find a solution.
Thanks for your inputs and appreciate your help.
go and check the extent sizes then
Guess I'm kinda confused. What method did you use for the migration? If you ran my select from above and it returned zero rows, then that would lead me to believe that the SYSTEM tablespace is LOCALLY managed?
Does the following query show DICTIONARY ?
where tablespace_name = 'SYSTEM';
After migrating a dictionary managed tablespace to
locally managed tablespace
you notice that the tables are still extending with the next extent specified
even though it's now a locally managed tablespace
Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly
created locally managed tablespaces.
I would like to create new tablespaces and then
alter table T move tablespace NEW_TBS;
alter index I rebuild tablespace NEW_TBS;
Issue here is
I wanted a list of tables which should be moved to get the full benefit of
locally managed tablespace.
If you DO NOT understand what I am asking for here is; then do not waste your time posting junk in here. Simply read what others are saying.
ill post it in simple language
plus you do realise that any tablespace which has been converted doesnt have to enforce extent sizes even on newly created objects
i guess you dont know that
Last edited by davey23uk; 05-09-2006 at 03:44 PM.
Read my post carefully and multiple times. I said migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly created locally managed tablespaces. thus the need to find the objects which should be moved to new tablespace to get full benefit of LMT.
for you davey23uk - There's nothing wrong in accepting your mistake and not repeat it.
for gods sake
CHECK YOUR EXTENT SIZES.
think about it ok, just for a moment
davey23uk, I don't know how he can conclude much from extent sizes in and of themselves?
rkhanna, the following query should show which objects where converting to LOCAL from DICTIONARY:
To reap the full benefits of LOCALLY MANAGED tablespaces, your best bet is just to "alter table move/alter index rebuild" all the tables & indexes to a new LOCALLY MANAGED tablespace because utimately, you are going to want to do away with your migrated tablespaces.
select a.owner, a.segment_name, a.segment_type
from dba_segments a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.extent_management = 'LOCAL'
and b.allocation_type = 'USER';
chck the extent sizes, looks for one where initial != next where initial has some way large value or some way low value.
Those are objects which havent been created with any sort of extent sizing thought
so in summary, check your extent sizes.
i'm guessing, but i reckon any object with have an allocation_type of 'USER' even if they were created after a ts was converted to a LMT
Click Here to Expand Forum to Full Width