FYI: Changing the extent size of a Locally Managed Tablespace

Hi Listers,

Some weeks ago, there was a thread
about changing the extent size of a Locally Managed Tablespace.

Reading through the dbms_space_admin
package specification, I got an idea for a possible workaround .
Here is a small testcase to evaluate the solution :

Initial Situation :

create tablespace TBS_TEST datafile '/base/TEST/TEST.dbf' size 15m extent management local uniform size 65536 online;

TABLESPACE_NAME   : TBS_TEST
INITIAL_EXTENT    : 65536
NEXT_EXTENT       : 65536
MIN_EXTENTS       : 1
MAX_EXTENTS       : 2147483645
PCT_INCREASE      : 0
MIN_EXTLEN        : 65536
STATUS            : ONLINE
CONTENTS          : PERMANENT
LOGGING           : LOGGING
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE   : USER
PLUGGED_IN        : NO

create table test1 (x char) tablespace tbs_test; select * from user_segments where segment_name='TEST1';

SEGMENT_NAME    : TEST1
PARTITION_NAME  :
SEGMENT_TYPE    : TABLE
TABLESPACE_NAME : TBS_TEST
BYTES           : 65536
BLOCKS          : 16
EXTENTS         : 1
INITIAL_EXTENT  : 65536
NEXT_EXTENT     : 65536
MIN_EXTENTS     : 1
MAX_EXTENTS     : 2147483645
PCT_INCREASE    : 0
FREELISTS       : 1
FREELIST_GROUPS : 1
BUFFER_POOL     : DEFAULT

Now i want to change the uniform size from 64K to 128K

Step 1 : migrate to dictionary managed tablespace exec dbms_space_admin.tablespace_migrate_from_local('TBS_TEST');

Result :

TABLESPACE_NAME   : TBS_TEST
INITIAL_EXTENT    : 65536
NEXT_EXTENT       : 65536
MIN_EXTENTS       : 1
MAX_EXTENTS       : 2147483645
PCT_INCREASE      : 0
MIN_EXTLEN        : 65536
STATUS            : ONLINE
CONTENTS          : PERMANENT
LOGGING           : LOGGING
EXTENT_MANAGEMENT : DICTIONARY
ALLOCATION_TYPE   : USER
PLUGGED_IN        : NO


Step 2 : adjust the tablespace storage parameters
              alter tablespace tbs_test
              default storage (initial 128K next 128K pctincrease 0);
              alter tablespace tbs_test minimum extent 128K;

Result :

TABLESPACE_NAME   : TBS_TEST
INITIAL_EXTENT    : 131072
NEXT_EXTENT       : 131072
MIN_EXTENTS       : 1
MAX_EXTENTS       : 2147483645
PCT_INCREASE      : 0
MIN_EXTLEN        : 131072
STATUS            : ONLINE
CONTENTS          : PERMANENT
LOGGING           : LOGGING
EXTENT_MANAGEMENT : DICTIONARY
ALLOCATION_TYPE   : USER
PLUGGED_IN        : NO

Step 3 : migrate back to locally managed tablespace exec dbms_space_admin.tablespace_migrate_to_locaL('TBS_TEST');

Result :

TABLESPACE_NAME   : TBS_TEST
INITIAL_EXTENT    : 131072
NEXT_EXTENT       : 131072
MIN_EXTENTS       : 1
MAX_EXTENTS       : 2147483645
PCT_INCREASE      : 0
MIN_EXTLEN        : 131072
STATUS            : ONLINE
CONTENTS          : PERMANENT
LOGGING           : LOGGING
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE   : USER
PLUGGED_IN        : NO


Step 4 : creation d'une nouvelle table de test
             create table test2  tablespace tbs_test
             as select * from from test1;

select * from user_segments where segment_name like 'TEST%';

Result :

SEGMENT_NAME    : TEST1
PARTITION_NAME  :
SEGMENT_TYPE    : TABLE
TABLESPACE_NAME : TBS_TEST
BYTES           : 65536
BLOCKS          : 16
EXTENTS         : 1
INITIAL_EXTENT  : 65536
NEXT_EXTENT     : 65536
MIN_EXTENTS     : 1
MAX_EXTENTS     : 2147483645
PCT_INCREASE    : 0
FREELISTS       : 1
FREELIST_GROUPS : 1
BUFFER_POOL     : DEFAULT

SEGMENT_NAME    : TEST2
PARTITION_NAME  :
SEGMENT_TYPE    : TABLE
TABLESPACE_NAME : TBS_TEST
BYTES           : 131072
BLOCKS          : 32
EXTENTS         : 1
INITIAL_EXTENT  : 131072
NEXT_EXTENT     : 131072
MIN_EXTENTS     : 1
MAX_EXTENTS     : 2147483645
PCT_INCREASE    : 0
FREELISTS       : 1
FREELIST_GROUPS : 1
BUFFER_POOL     : DEFAULT

test1 is still 64K but test2 is now 128K So you lose some "uniformity" but at least you don't have to recreate the tablespace from a backup.

It's just a small test so i possibly
missed some pitfalls

Hope this helps Received on Mon Aug 28 2000 - 11:14:18 CDT