[20151215]ORA-60014: invalid MAXSIZE storage option value.txt

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t ( id number);
Table created.

SCOTT@book> insert into t values (999999999999999) ;
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@BOOK> alter table t storage ( maxsize 64k );
Error at line 3
ORA-60014: invalid MAXSIZE storage option value

$ oerr ora 60014
60014, 00000, "invalid MAXSIZE storage option value"
// *Document: YES
// *Cause: Minimum of 1M should have been specified against the MAXSIZE storage clause.
// *Action: Correct the value and retry command.
//

--根据提示很明显，最小仅仅设置1M。

SCOTT@book> alter table t storage ( maxsize 1M );
Table altered.

SCOTT@book> insert into t select level from dual connect by level<=1e7;
insert into t select level from dual connect by level<=1e7
            *
ERROR at line 1:
ORA-60004: adding (256) blocks to table SCOTT.T with MAXSIZE (128)

$ oerr ora 60004
60004, 00000, "adding (%s) blocks to table %s.%s with MAXSIZE (%s) "
// *Document: YES
// *Cause:  Extending a table violated MAXSIZE limit.
// *Action: Increase the MAXSIZE limit and retry command.
//

--为什么是1M，我估计跟asm的au有关，au缺省就是1M，在asm下你不用1M空间也是浪费。
--users表空间定义如下：

CREATE TABLESPACE USERS DATAFILE
  '/mnt/ramdisk/book/users01.dbf' SIZE 8960K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--在9i下，实际上这个测试版本是9i，打上9.2.0.8的补丁就变成了10.1，不知大为什么？
SQL> select * from v$version where rownum<=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

SQL> create table t  (id number) tablespace users;
Table created.

SQL> alter table t storage ( maxsize 64k );
alter table t storage ( maxsize 64k )
                        *
ERROR at line 1:
ORA-02143: invalid STORAGE option

$ oerr ora 02143
02143, 00000, "invalid STORAGE option"
// *Cause: An option other than INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, or
//        PCTINCREASE was specified in the STORAGE clause.
// *Action: Specify only valid options.

--根据提示9i不支持maxsize参数。

SQL> ALTER TABLE T STORAGE ( MAXEXTENTS 1 );
ALTER TABLE T STORAGE ( MAXEXTENTS 1 )
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> ALTER TABLE T STORAGE ( MAXEXTENTS 100 );
ALTER TABLE SYS.T STORAGE ( MAXEXTENTS 100 )
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

$ oerr ora 25150
25150, 00000, "ALTERING of extent parameters not permitted"
// *Cause: An attempt was made to alter the extent parameters for a segment
//         in a tablespace with autoallocate or uniform extent allocation
//         policy.
// *Action: Remove the appropriate extent parameters from the command.

--很奇怪oracle在9i下不支持这种修改方式,对于本地管理的表空间oracle视乎不支持这些参数。自己以前不注意。不知道错误在那里。

SQL> alter table t storage ( next 1024768 );
alter table t storage ( next 1024768 )
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

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

--难道仅仅在建立是有效吗？不知道问题在那里。

SQL> create table tx  (id number) tablespace users
     STORAGE    (
            INITIAL          64K
            NEXT             64K
            MINEXTENTS       1
            MAXEXTENTS       2);
Table created.

--找到1个连接：
http://www.itpub.net/thread-1841111-1-1.html
--看来这个是1个bug。