chanhe LMT uniform extent

[clinton]

I have an LMT that has a uniform extent of 64000K.
I would lkie to change it to 32000K. Can any body give me the steps
involved in doing this?

This is the command that I used to create the LMT:

create tablespace LOCAL_TBS_DATA2
datafile '/8i/ora816/oradata/V816/local2_01.dbf' size 1280000k
extent management local
uniform size 64000k

[marist89]

Create a new tbs with the correct sizing and MOVE the tables to the new tbs.

[gpsingh]

That is one of the feature of LMT , to remove the usage of Next extent.
If u need to change the next extent, change at the level of table.
Oracle shall allocate in the multiple of uniform extent of tablespace.

[marist89]

except, in this case, the NEXT extent is SMALLER than the UNIFORM extent size.

[kgb]

Solution Description
--------------------

1. Migrate the locally-managed tablespace to a dictionary management.
2. Then migrate it back to local management.

Example:
==================================================
MIGRATE FROM LOCALLY MANAGED TO DICTIONARY MANAGED
==================================================

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('LOCAL_TBS_DATA');
PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('LOCAL_TBS_DATA2');
PL/SQL procedure successfully completed.

================================================
Verify that tablespace is now dictionary-managed
================================================

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT
2 FROM DBA_TABLESPACES
3 where tablespace_name in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
LOCAL_TBS_DATA2 DICTIONARY
LOCAL_TBS_DATA DICTIONARY

=======================================================
MIGRATE FROM DICTIONARY MANAGED BACK TO LOCALLY MANAGED
=======================================================
Identify the file_id corresponding to the tablespace:

SQL> select FILE_ID, TABLESPACE_NAME from dba_data_files
2 where TABLESPACE_NAME in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

FILE_ID TABLESPACE_NAME
-------------- ----------------------------------------
7 LOCAL_TBS_DATA
8 LOCAL_TBS_DATA2

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
tablespace_name =>'LOCAL_TBS_DATA', rfno => 7);
PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
tablespace_name =>'LOCAL_TBS_DATA2', rfno => 8);
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 in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
LOCAL_TBS_DATA2 LOCAL
LOCAL_TBS_DATA LOCAL


=============================================
Alter the TABLE with a new NEXT storage value
=============================================

SQL> ALTER TABLE t_local STORAGE (NEXT 200K);
Table altered.

SQL> ALTER TABLE t_local2 STORAGE (NEXT 200K);
Table altered.

SQL> select table_name, initial_extent, next_extent
2 from dba_tables where table_name in ('T_LOCAL','T_LOCAL2');

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
T_LOCAL2 73728 204800
T_LOCAL 65536 204800


Explanation
-----------

The 1st migration allows you to manage the extent sizing, and the
2nd migration ignores extent size handling.

Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly
created locally managed tablespaces.


-----------------------

[marist89]

Originally posted by kgb
Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly
created locally managed tablespaces.

Herein lies the key.

[kgb]

http://p2p.wrox.com/archive/oracle/2001-04/0.asp

[marist89]

OK, so you posted the link from which you stole the text. Smashing.

The point I was trying to make was that although this would "work", it wouldn't give the result the poster was looking for. The poster wants to know how to change his 64M UNIFORM extents to 32M UNIFORM extents. By converting to a DMT back to a LMT would not give the poster UNIFORM extents.

[pando]

if you want *REAL* uniform extent size you must not use dbms_space package

[kgb]

I see you are very smart guy! Congratulations !!!!
Let me know from which books did you steal your knowledge? I may tell you mine:
Internet and Oracle Documentation.
Or may be you ask someone first and then post an answer ? :-)

Originally posted by marist89
OK, so you posted the link from which you stole the text. Smashing.
UNIFORM extents.