###测试alter table storage及dbms_space_admin包(本地管理表空间local及auto segment space management)
SQL> create table test_storage(a int,b int) storage(initial 40k next 40k pctincrease 5 minextents 1 maxextents 1000);--显式指定storage存储属性建表

Table created.

SQL> desc user_segments;
 Name                                                                                                                                                                          Null?    Type
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------

----------------------------------------------------------------------------------------------
 SEGMENT_NAME                                                                                                                                                                           VARCHAR2(81)
 PARTITION_NAME                                                                                                                                                                         VARCHAR2(30)
 SEGMENT_TYPE                                                                                                                                                                           VARCHAR2(18)
 TABLESPACE_NAME                                                                                                                                                                        VARCHAR2(30)
 BYTES                                                                                                                                                                                  NUMBER
 BLOCKS                                                                                                                                                                                 NUMBER
 EXTENTS                                                                                                                                                                                NUMBER
 INITIAL_EXTENT                                                                                                                                                                         NUMBER
 NEXT_EXTENT                                                                                                                                                                            NUMBER
 MIN_EXTENTS                                                                                                                                                                            NUMBER
 MAX_EXTENTS                                                                                                                                                                            NUMBER
 PCT_INCREASE                                                                                                                                                                           NUMBER
 FREELISTS                                                                                                                                                                              NUMBER
 FREELIST_GROUPS                                                                                                                                                                        NUMBER
 BUFFER_POOL                                                                                                                                                                            VARCHAR2(7)

SQL> select segment_name,extents,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_segments where segment_name='TEST_STORAGE';--oracle会自动对相关建表参数进行调整，比如max_extents（从

                                                                                                                                                       1000到2147483645)

SEGMENT_NAME                                                                         EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
--------------------------------------------------------------------------------- ---------- -------------- ----------- ----------- ----------- ------------
TEST_STORAGE                                                                               1          40960                       1  2147483645

SQL> alter table test_storage storage(next 2000k);--不允许对表存储参数进行变更
alter table test_storage storage(next 2000k)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> alter table test_storage storage(pctincrease 20);---同上
alter table test_storage storage(pctincrease 20)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,pct_increase  from user_tablespaces;--查看表所属的表空间的extent及segment管理方式，各为local及auto

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN PCT_INCREASE
------------------------------ ---------- --------- ------ ------------
SYSTEM                         LOCAL      SYSTEM    MANUAL
UNDOTBS1                       LOCAL      SYSTEM    MANUAL
SYSAUX                         LOCAL      SYSTEM    AUTO
TEMP                           LOCAL      UNIFORM   MANUAL            0
USERS                          LOCAL      SYSTEM    AUTO
EYE                            LOCAL      SYSTEM    AUTO

6 rows selected.

#######摘录相关错误如下
SQL> host oerr ora 25150
25150, 00000, "ALTERING of extent parameters not permitted"
// *Cause: An attempt was made to alter the extent parameters for a segment   ###说明对于assm方式且以统一分式或自动分配extent的表空间，不能进行变更属性操作的
//         in a tablespace with autoallocate or uniform. extent allocation
//         policy.
// *Action: Remove the appropriate extent parameters from the command.

SQL> exec dbms_space_admin.tablespace_migrate_from_local('EYE');---用包dbms_space_admin把表空间eye从本地管理方式转化为字典管理方式，失败
BEGIN dbms_space_admin.tablespace_migrate_from_local('EYE'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> host oerr ora 10616 --查阅以上错误,提示不能对auto segment空间管理方式进行以上转化操作
10616, 00000, "Operation not allowed on this tablespace"
// *Cause:  Cannot perform. the operation on tablespace with AUTO
//          SEGMENT SPACE MANAGEMENT
// *Action:  Recheck the tablespace name and re-issue the statement

SQL> exec dbms_space_admin.tablespace_migrate_from_local(upper('undotbs1'));--不允许对undotbs表空间进行转化操作
BEGIN dbms_space_admin.tablespace_migrate_from_local(upper('undotbs1')); END;

*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> create tablespace test_tbs datafile '/oracle/oradata/test_tbs.dbf' size 100m segment space management manual;--无奈，我就新建一个auto segment space management方式的本地local管理表空间

Tablespace created.

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,pct_increase  from user_tablespaces where tablespace_name='TEST_TBS';--查新建的表空间test_storage

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN PCT_INCREASE
------------------------------ ---------- --------- ------ ------------
TEST_TBS                       LOCAL      SYSTEM    MANUAL

SQL> exec dbms_space_admin.tablespace_migrate_from_local('TEST_TBS');--再次转化还是不成
BEGIN dbms_space_admin.tablespace_migrate_from_local('TEST_TBS'); END;

*
ERROR at line 1:
ORA-12914: Cannot migrate tablespace to dictionary managed type
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

SQL> host oerr ora 12914  ##查阅错误，说明只要system表空间是本地管理方式，就不能把表空间转化为字典管理方式，晕
12914, 00000, "Cannot migrate tablespace to dictionary managed type"
// *Cause: Attemp to migrate locally managed tablespace to  dictionary managed
//         type when the database has locally managed system tablespace.
// *Action: Command cannot be issued.

SQL>