Get a complete CREATE TABLE definition using DBMS_METADATA package

CREATE TABLE has lot of options to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it.

ops$rperumal@PDB10> CREATE TABLE t
2 (n NUMBER PRIMARY KEY,
3 v VARCHAR2(100),
4 c CLOB,
5 b BLOB
6 );

Table created.

Once the table is created in a simple form, use the standard Oracle supplied package DBMS_METADATA to obtain a verbose definition:

ops$rperumal@PDB10> SELECT DBMS_METADATA.get_ddl ('TABLE', 'T') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "OPS$RPERUMAL"."T"
   (    "N" NUMBER,
        "V" VARCHAR2(100),
        "C" CLOB,
        "B" BLOB,
         PRIMARY KEY ("N")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_RPERUMAL"  ENABLE
   ) PCTFREE 10 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)
  TABLESPACE "TS_RPERUMAL"
 LOB ("C") STORE AS (
  TABLESPACE "TS_RPERUMAL" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 LOB ("B") STORE AS (
  TABLESPACE "TS_RPERUMAL" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

You can customize the definition according to your needs. Most importantly, you don’t have to remember all the parameters and can avoid running through rail-road diagrams.