2

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;
Improve this question
2
  • It depends on your tablespace settings. In storage_clause it says The specification of storage parameters for objects in locally managed tablespaces is supported for backward compatibility. If you are using locally managed tablespaces, then you can omit these storage parameter when creating objects in those tablespaces.
    – Wernfried Domscheit
    Jun 21, 2016 at 9:29
  • Does it also depend on the size of database? So will the same rule apply for let's say 200 GB database in size?
    – Mukul Pandey
    Jun 21, 2016 at 11:36

1 Answer 1

Reset to default
0

"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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.