An example of a complete Oracle table Creation

Creating a table in a complete Oracle table creation example is generally quite simple, but the Oracle table creation statements have many optional parameters, and some of them may not be used at ordinary times, I don't know how to use it when I use it. Here is an example of a complete TABLE creation: [SQL] CREATE TABLE banping (id NUMBER (5) CONSTRAINT banping_id_pk PRIMARY KEY, last_name VARCHAR2 (10) CONSTRAINT does 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 NUMBE R (7) CONSTRAINT inclureferences 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 [SQL] CREATE TABLE [schema.] table (c Olumn datatype [, column datatype]…) [TABLESPACE tablespace] [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [STORAGE storage-clause] [LOGGING | NOLOGGING] [CACHE | NOCACHE]; schema: Table owner Table: Table name Column: field name Datatype: Data Type of the field Tablespace: Table Tablespace Pctfree: the amount of space reserved in each block for the purpose of row length growth (expressed in the form of percentage of space occupied by the whole space minus the remaining space after the block header). When the remaining space is insufficient for pctfree, 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: Limit the maximum number of transaction items that can be allocated to each block. The default value is 255 STORAGE: identify determines how to differentiate the storage sub-statement LOGGING assigned to the table: specify that the creation of the table will be recorded in 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 partition NEXT: size of the NEXT partition PCTINCREASE: Percentage of space growth in each partition in the future MINEXTENTS: number of INITIAL partitions in a segment MAXEXTENTS: if minimum extent is defined for a tablespace, the partition size of the table is rounded up to the next higher multiple of the minimum extent value. The id column of the dept table associated with the foreign key must be unique or its own primary key. If not, you can use the following statement to add it: [SQL] alter table dept add constraint dept_id_pk primary key (id); Block space parameters can be used to control the use of data segments and index segment spaces: Control concurrency parameters: INITRANS and MAXTRANS specify the initial and maximum number of transaction locations, which are created in the index block or data block. The transaction location is used to store the information of the transaction on which the block is being modified at an event point. A transaction occupies only one transaction location, even if it is changing multiple rows or multiple index entries. The default value of INITRANS for data segments is 1, and the default value of index segments is 2 to ensure the lowest degree of concurrency. For example, if INITRANS is set to 3, at least three transactions can be modified simultaneously. If necessary, you can also allocate other transaction locations from the free space of the block to allow more transactions to modify the rows in the block concurrently. The default value of MAXTRANS is 255, which sets the limit on the number of concurrent transactions that can be changed for data blocks or index blocks. After setting, this value limits the space used by the transaction location to ensure that there is sufficient space in the block for the row or index data. Parameters used to control the data space: PCTFREE of the Data Segment specifies the percentage of reserved space in each data block, which is used to increase as a result of updating rows in the block. The default value of PCTFREE is 10%. PCTUSED of the data segment represents the minimum percentage of space in use that the Oracle server tries to maintain for each data block in the table. If the used space of a block is lower than PCTUSED, the block is put back to the idle list. The idle column of the segment indicates the list of selectable blocks that can be inserted in the future. By default, each segment has an idle list when it is created. The default value of PCTUSED is 40%. Both PCTFREE and PCTUSED are calculated based on the percentage of available data space. The available data space is the size of the entire block minus the remaining block space after the block size. Block space parameters can only be specified for segments, but cannot be set at the tablespace level. The following steps describe how to manage the block space in the Data Segment PCTFREE = 20 and PCTUSED = 40: 1. Insert rows into the block until the free space in the block is smaller than or equal to 20%. When the data space in the block occupied by a row reaches 80% (-pctfree) or more, the row cannot be inserted into the block. 2. The remaining 20% can be used when the capacity increases. For example, update the column whose initial value is NULL and assign a value. In this way, the block usage may exceed 80% due to updates. 3. If the row or row size in the block is deleted due to updates, the block usage may fall below 80%. However, you still cannot insert data into the block until the block usage falls below PCTUSED. In this example, PCTUSED is 40%. 4. When the block usage falls below PCTUSED, this block can be used for insertion. As a row is inserted into a block, the block usage increases and repeats the cycle starting from step 1.