Re: Changing the extent size of a Locally Managed Tablespace

I ***Would*** not do this.

Recently returned from an Oracle Internals class where the instructor urged that we ***NOT*** use the migrate_to_local procedure. There are some problems with it.

You might not have a problem, then again, you might experience corruption.

• Original Message ----- From: Gilles PARC <gparc_at_online.fr> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, August 28, 2000 3:44 PM Subject: 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
>
> P.S. : the procedure TABLESPACE_MIGRATE_TO_LOCAL
> is available in 8.1.6 but not in 8.1.5
>
>
>
> Gilles Parc
> Email : gparc_at_mail.dotcom.fr
>
> carpe diem !!
> --
> Author: Gilles PARC
> INET: gparc_at_online.fr
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Mon Aug 28 2000 - 15:30:16 CDT