Oracle 12c – Setting Parameters Like PCTUSED When Creating New Tables

oracleoracle-12ctable

When creating new tables in ORACLE 12 C, do we need to explicitly set parameters like PCTUSED, PCTFREE, INITRANS, MAXTRANS, STORAGE etc. (as shown in example below)?
Or will ORACLE automatically set these values efficiently? Developers generally tend to leave out these parameters as they focus only on table/schema/data model creation – "the bare minimum" needed for any application to run!

CREATE TABLE TABLE1
(
COL1 VARCHAR2(28 BYTE) NOT NULL
.....
)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

Best Answer

"Or will ORACLE automatically set these values efficiently"

Well, Oracle will set them to something by default ... probably not very "efficiently" though ;) For that, you're going to want to do a bit of research and groundwork on what YOU need.

As mentioned by Wernfried in comments, LOCALLY managed tablespaces are the way to go, and once you have those defined, specifying those options per table is wasted - Oracle will ignore them.

It's generally seen as better to setup certain types of Tablespaces for different types of tables used.

Small, mostly read-only tables: 1 tablespace: set to small Extent sizes, and small/0 pctincrease.

Larger, highly volatile tables: another tablespace: larger extent sizes, larger pctincrease ..

etc. You need to work out what your needs are, and what kinds of tables you have (and then what tablespaces/storage options you'll want).

Related Question