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

[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的表空间无效。