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.
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.
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:
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';
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.