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

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14075938/viewspace-509512/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14075938/viewspace-509512/