[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的表空間無效。