Structural Analysis of oracle-table

Table Structure Analysis

Build a Table statement
CREATE TABLE TABLE_NAME (table_name VARCHAR2 (TEN), Table_name1 Int (5),)
Alter statement

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name MODIFY (column_name datatype)

Oracle DDL

CREATE TABLE" cs_gdbzdata". " Acct_work_register "(" Curdate "VARCHAR2(Ten) not NULLENABLE, "Mworkbegintime"VARCHAR2(8), "Mworkendtime"VARCHAR2(8), "Aworkbegintime"VARCHAR2(8), "Aworkendtime"VARCHAR2(8), "Nworkbegintime"VARCHAR2(8), "Nworkendtime"VARCHAR2(8), "WEEK"VARCHAR2(1), "Workflag"VARCHAR2(Ten), "Areatype"VARCHAR2(Ten), "WORKFLAGHK"VARCHAR2(Ten), "WORKFLAGMC"VARCHAR2(Ten)) SEGMENT CREATION IMMEDIATE PCTFREETenPctused +Initrans1Maxtrans255nocompress LOGGING STORAGE (INITIAL65536 NEXT 1048576Minextents1MAXEXTENTS2147483645Pctincrease0Freelists1FREELIST GROUPS1Buffer_poolDEFAULTFlash_cacheDEFAULTCell_flash_cacheDEFAULT) tablespace "Cs_gdbzdatatbs"; ALTER TABLE"Cs_gdbzdata". " Acct_work_register "MODIFY (" Curdate " not NULLENABLE);

These parameters involve stored procedures
Generally, the default
Can be changed according to business requirements

PCTFREE pctused Initrans 1 Maxtrans 255 nocompress LOGGING

Key field explanation

At this time PCTFREE represents the setting of 10, meaning that when the BLOCK is used to reach 90%, it cannot be used,
This BLOCK should be removed from the FREELIST list (un-link).
Why should we keep 10% of the space?
This is to provide the space usage that is likely to increase when the update data is available, and if the space is kept too small, the row chaining is prone to occur.


Assuming that pctused is 40, it means that when we drink the water from the cup to the remaining 40%, the waiter will know it needs to be added.
If the pctused is too large, for example 70 implies that the utilization of the cup increases, but the attendant's frequent service causes the load (I/O Overhead).
Drink water (like DELETE transaction operation), put back FREELIST, add water (like INSERT transaction operation).

Initrans refers to the space on a BLOCK that is initially pre-allocated to parallel trading control (ITLS)
Maxtrans means that if the Initrans space is not enough, the ITL will automatically expand until the maximum value is the Maxtrans value.

If you write logging when you build the library, you want to log logs in the library and subsequent action statements.
Nocompress LOB is not compressed on large object field LOB

Structural Analysis of oracle-table