ITPub博客

首页 > 数据库 > Oracle > [20151215]ORA-60014.txt

[20151215]ORA-60014.txt

原创 Oracle 作者:lfree 时间:2015-12-15 09:03:21 0 删除 编辑

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

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

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2894
  • 访问量
    6671485