An example of a complete Oracle table Creation

Table creation is generally quite simple, but the Oracle table creation statements have many optional parameters. Some of them may not be used at ordinary times and do not know how to use them.

Table creation is generally quite simple, but the Oracle table creation statements have many optional parameters. Some of them may not be used at ordinary times and do not know how to use them.

Table creation is generally quite simple, but there are many optional parameters in the Oracle table creation statements. Some of them may not be used at ordinary times, and we do not know how to use them, here is an example of creating a complete table:

Create table banping
(Id NUMBER (5)
CONSTRAINT banping_id_pk primary key,
Last_name VARCHAR2 (10)
CONSTRAINT banping_last_name_nn not null,
First_name VARCHAR2 (10) not null unique,
Userid VARCHAR2 (8)
CONSTRAINT banping_userid_uk UNIQUE,
Start_date date default sysdate,
Title VARCHAR2 (10 ),
Dept_id NUMBER (7)
CONSTRAINT banping_dept_id_fk REFERENCES dept (id ),
Salary NUMBER (11,2 ),
User_type VARCHAR2 (4)
CONSTRAINT banping_user_type_ck CHECK
(User_type IN ('in', 'out ')),
CONSTRAINT banping_uk_title UNIQUE (title, salary)
)
INITRANS 1 MAXTRANS 255
PCTFREE 20 PCTUSED 50
STORAGE (INITIAL 1024 k next 1024 k pctincrease 0 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE data

Explanation:
Syntax

Create table [schema.] table
(Column datatype [, column datatype]… )
[TABLESPACE tablespace]
[PCTFREE integer]
[PCTUSED integer]
[INITRANS integer]
[MAXTRANS integer]
[STORAGE storage-clause]
[LOGGING | NOLOGGING]
[CACHE | NOCACHE];

Schema: The table owner.
Table: Table Name
Column: field name
Datatype: Data Type of the field
Tablespace: The Tablespace in which the table is located
Pctfree: the amount of space reserved in each block to increase the length of the row (expressed as a percentage of the space occupied by the whole space minus the remaining space after the block header). When pctfree is insufficient, no new rows are added to the block.
Pctused: After the remaining space of the block is insufficient, the percentage of space used by the block must be smaller than pctused before a new row can be added to the block.
INITRANS: Number of transaction items pre-allocated in the block. The default value is 1.
MAXTRANS: specifies the maximum number of transaction items that can be allocated to each block. The default value is 255.
STORAGE: identifies the STORAGE clauses that determine how to distribute data to tables.
LOGGING: Create a specified table and record it to the redo log file. It also specifies that all subsequent operations on the table will be recorded. This is the default setting.
NOLOGGING: the creation of a specified table is not recorded in the redo log file.
CACHE: specify that even when a full table scan is performed, the blocks retrieved for the table will be placed at the last end of the cache lru list.
NOCACHE: specifies that when a full table scan is performed, the block retrieved for this table will be placed at the end of the LRU list that is not used recently in the buffer cache.
STORAGE clause:
INITIAL: size of the INITIAL Zone
NEXT: the size of the NEXT partition.
PCTINCREASE: Percentage of space growth in each area in the future
MINEXTENTS: number of initial segments
MAXEXTENTS: Maximum number of extended zones