[20210315]ORA-04000 the sum of PCTUSED and PCTFREE cannot exceed 100.txt

SYS@book> @ 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

SYS@book> create table t  pctfree 99 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
create table t  pctfree 99 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random
                                                                              *
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot exceed 100

$ oerr ora 4000
04000, 00000, "the sum of PCTUSED and PCTFREE cannot exceed 100"
// *Cause: the sum of PCTUSED and PCTFREE for a cluster or table exceeds 100
// *Action: create the table/cluster specifying values whose sum is <= 100

--//因为sys用户缺省表建立在system表空间,system表空间是mssm属性是PCTUSED and PCTFREE都有效。
SYS@book> @ ddl dual
C100
------------------------------------------------------------------------

  CREATE TABLE "SYS"."DUAL"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--//你可以看出缺省pctfree=10,PCTUSED=40.
--//我建立的表pctfree=99,两者相加大于100.

SYS@book> create table t  pctfree 60 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
Table created.

--//这样就可以建立。也可以修改如下：

SYS@book> drop table t purge ;
Table dropped.

SYS@book> create table t  pctfree 99 pctused 1 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
Table created.

--//当然你可以建立在非assm表空间上就不会出现上面的错误。

SYS@book> create table t  tablespace users pctfree 99  as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
Table created.

SYS@book> @ ddl t
C100
----------------------------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T"
   (    "ID" NUMBER,
        "VC" VARCHAR2(3500)
   ) PCTFREE 99 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

--//虽然看到的PCTUSED 40，但是对于assm的表空间无效。