[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。
