linux ora-02143,[20151215]ORA-60014.txt

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

> @ &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.