oracle 9i数据库，表所在的表空间是本地管理方式，maxextent unlimited

SQL> alter table abss_policy_stat
2 storage
(
        next 1M
3    4    5 );
alter table abss_policy_stat
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

想到改变表空间的管理方式
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(TB_SPACE);
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('BSMP_TEMP'); END;

*
ERROR at line 1:
ORA-12914: Cannot migrate tablespace to dictionary managed type
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
网上找了半天，都说9i里没有办法了，表空间是本地管理其中的表不能修改NEXT MAXEXTENTS等参数

贴一篇解决此问题的文章

*** Example1 of a table residing on an AUTOALLOCATE extent
    locally-managed tablespace

SQL> create tablespace LOCAL_TBS_DATA
2 datafile '/8i/ora816/oradata/V816/local01.dbf' size 600K
3 extent management local autoallocate;

Tablespace created.

SQL> create table T_LOCAL (c number)
2 tablespace LOCAL_TBS_DATA;

Table created.

SQL> select table_name, initial_extent, next_extent
2 from dba_tables where table_name='T_LOCAL';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
T_LOCAL                                 65536

SQL> alter table T_LOCAL storage (NEXT 200K);
alter table T_LOCAL storage (NEXT 200K)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

*** Example2 of a table residing on a UNIFORM. EXTENT SIZE
    locally-managed tablespace

SQL> create tablespace LOCAL_TBS_DATA2
2 datafile '/8i/ora816/oradata/V816/local2_01.dbf' size 600K
3 extent management local uniform. size 65k;

Tablespace created.

SQL> create table T_LOCAL2 (c number)
2 tablespace LOCAL_TBS_DATA2;

Table created.

SQL> select table_name, initial_extent, next_extent
2 from dba_tables where table_name='T_LOCAL2';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
T_LOCAL2                                73728       73728

SQL> alter table T_LOCAL2 storage (NEXT 200k);
alter table T_LOCAL2 storage (NEXT 200k)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

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.

关于ORA-25150: ALTERING of extent parameters not permitted