This support note covers this topic:

Article-ID: <Note:120061.1>
Circulation: PUBLISHED (EXTERNAL)
Folder: server.Rdbms.DBA.Admin
Topic: Managing Tablespaces
Title: Next Extent Size After Migrating Tablespace from Dictionary
to Locally Managed
Document-Type: BULLETIN
Impact: LOW
Skill-Level: NOVICE
Server-Version: 08
Updated-Date: 18-OCT-2000 12:31:11
References:
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: DICTIONARY; LOCALLY; MANAGED; MIGRATE;
Products: 5/RDBMS (V8.1);
Platforms: GENERIC;

PURPOSE
-------

To Explain why migrated tablespaces are not subject to the
UNIFORM/SYSTEM policy of newly created locally managed tablespaces


SCOPE & APPLICATION
-------------------
An Understanding of the Architecture of Migrated Tablespaces

MIGRATED TABLESPACES:
-----------------------------
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


Example:
==================================================
MIGRATE FROM DICTIONARY MANAGED TO LOCALLY MANAGED
==================================================
SQL> begin
2 dbms_space_admin.tablespace_migrate_to_local
3 (tablespace_name=> 'USERS', rfno=>5);
4 end;
5 /

PL/SQL procedure successfully completed.

===============================================
Verify that tablespace is now locally-managed:
===============================================

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT
2 FROM DBA_TABLESPACES
3 where tablespace_name = 'USERS';

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
USERS LOCAL

=================================================
CREATE TABLE with the storage segments;
=================================================

SQL> create table DW_GSO_COLLN_NEW
2 tablespace USERS
3 storage (initial 500M next 500M pctincrease 0)
4 as select * from cdwadm.dw_gso_colln
5 where rownum < 10000000;
Table created.

SQL> select segment_name,extent_id,bytes
2 from dba_extents
3 where segment_name='DW_GSO_COLLN_NEW';
SEGMENT_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
DW_GSO_COLLN_NEW 0 524288000
DW_GSO_COLLN_NEW 1 524288000
DW_GSO_COLLN_NEW 2 524288000


QUESTION:

Why is the table extending with the next extent that is
specified even though it's locally managed tablespace.

ANSWER:

Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly created locally managed tablespaces. This would be too difficult to implement, since the tablespace is likely to contain the existing objects which already violate new policy. For this reason, migrated tablespace only support the same allocation policy as the dictionary tablespaces, i.e. we do whatever the user tells us to do. If you select from DBA_TABLESPACES, you should see "USER" value in the
ALLOCATION_TYPE column for migrated tablespaces, and UNIFORM or
SYSTEM value for tablespaces which were created as locally managed.

Therefore, the user will not get the policy benefits from migration, but can still get performance benefits - no ST enqueue contention and more efficient extent operations.

SQL> select TABLESPACE_NAME,
EXTENT_MANAGEMENT, ALLOCATION_TYPE,PLUGGED_IN
FROM DBA_TABLESPACES 3 ;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO PLU
------------------------ ---------- --------- ---
SYSTEM DICTIONARY USER NO
RBS DICTIONARY USER NO
TEMP DICTIONARY USER NO
TOOLS DICTIONARY USER NO
USERS LOCAL USER NO <--MIGRATED
IDX DICTIONARY USER NO
MRFACTDATA LOCAL USER NO <--MIGRATED
LOCAL_AUTO LOCAL SYSTEM NO <--NOT MIGRATED
LOCALLY_MANAGED LOCAL UNIFORM NO <--NOT MIGRATED

9 rows selected.